This entry was posted on August 23, 2009 at 15:58	and is filed under Excel 2010, PowerPivot and DAX. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Hi Thomas, nice article. I have a few comments:"Gemini let you build large tables, from databases, web page information, Excel and text files, without having to use a ETL tool like Integration Services"You don't have to use an ETL tool with Analysis Services either, and unlike Gemini, the UDM doesn't impose restrictions on the types of table relationships that you can use. I don't see how you can import text files in Gemini, unless you do it through Excel."Gemini is a new Sharepoint application that relates to Analysis Services but it is a new and separate track."It's has both client components and server components. You can happily install the add-in in Excel and never publish the file to SharePoint. You can keep a copy of the file in a shared folder in Windows Server, although this won't be the most efficient way to share the file."Previous versions of Excel have only the notion of rows, columns and cells that make it hard to use this data source as a relational data store even if it is supplied with plenty of functions that tries to mimic the notion of relational data"This architecture has not changed in Excel 2010 and probably will not change in any future version either."First i tried to build a concatenated string of the team and the player in Gemini with the DAX statement: = [Team] & [PlayerName], but the problem is that these statements will not show up when you try to relate the tabs in Gemini."Doesn't show up where? If I create a calculated column named FullName with concatenated string like =[FirstName]&" "&[LastName], FullName shows up in the Source Column (Foreign Key) list in the Create Relationship dialog box.
I do not know who commented here but thank you for being clear about Gemini. It is true that Gemini both has the client part and the server part. I should have mentioned that I was using the client part. What I tried to explain was also that Gemni makes it possible to build relational structures "within" Excel 2010, but within the Gemini add-in. For an end user it will look like an Excel application anyway. The concatenated string, in the Gemini table, did not always show up(should have mentioned that) but when it did I only got the all level values from the second table. When I did the concatenation in Excel first it all worked fine. I will try to reproduce this behavior and report it, later today.
Hi Thomas, my name is Colin Banfield. It was I who commented previously. When I signed in to Windows Live I saw my name show up on the top right of the screen, and so expected to see the name as the comment author. I don't see anywhere to input a name otherwise.I'm interested to know what you see as the biggest limitations in Gemini. You may have seen some of my not so positive posts in Microsoft Connect, but I'll reserve final judgement until after I get to play with CTP3. One frustration I have is that there is no obvious way to perform any kind of grouping in Gemini PivotTables. The three grouping types available in Excel PivotTables are: group by date, group by number (discretize) and group by field items (you highlight the items you want to group). In BIDS, for instance, you have the Dimension Wizard to help you create time dimensions. Within the cube builder it's easy to set discretization for an attribute. Gemini has no similar concepts. You can of course create calculated fields for date grouping and discretization, but these actions won't be obvious to the most Excel users that bulid cubes in Gemini. Better would be for Gemini to provide wizards that automatically create the calculated columns based on the user input.I also think that the Table Wizard could have been better done to create the queries graphically. Heck, even the stone-age MS Query in Excel does a better job in most cases.
Hi Colin. I do not agree on your view about the table wizard. It seems straight forward for me. The biggest limitation is if you cannot import a large text file and split it up into dimension like tables and fact data. I am not sure about what will happen in the next CTP release but text files are a frequent data source. Wiithout that functionality you can only load lareg amounts of data from relational sources.Grouping do work in my example. Each team player appeared under the correct team. I can hide the player level under the team level.Anyway you idea about adding wizards is important becuase without it the tool can be to complex for end users.
"Hi Colin. I do not agree on your view about the table wizard. It seems straight forward for me."Yeah, it's straightforward until you want to *visually*:1) Extract distinct records2) Create aggregations prior to importing into Gemini3) Create a derived table by selecting the tables, fields and relationships you want (in case you don't want all the individual tables).All of the above can be handled by the visual tools in the decade old MS Query application, and indeed can be done in the most basic query tools that I've ever used. In Gemini, you have to write a SQL script to execute these operations. Not by any means difficult, but may be beyond the capability of Gemini's target users (Excel users)."Grouping do work in my example. Each team player appeared under the correct team. I can hide the player level under the team level."This assumes that you do the grouping up front i.e during database query. However, it's often the case that the end user wants the flexibility to create special groups in the PivotTable. This often occurs when there's no obvious way to perform the categorization in a database field. In an Excel PivotTable, you can create any abitrary group by selecting the field items you want to group, then right-clicking and selecting Group. I see this kind of ad-hoc grouping quite often.