locked
2 cubes, same fact and dimensions structures....can PowerPivot relate them? RRS feed

  • Question

  • SQL Server R2 RC0 x64 on Windows Server 2008, PowerPivot RC0 x64 on Windows 7

    Reources tried:  Too many to list!

    Hi all,

    I've have a really good search but can't see anyone raising this yet....

    So I have 2 cubes (for Business1 and Business2) with identical measure definitions and a nearly identical dimension structures....I know, I know we should have conformed dimensions and a channelised cube...well trust me, its not for the want of trying to find the time to completely refactor all our ETL process, DBs and Cubes....so, I thought I'd use this as a real case basis for putting PowerPivot through its paces!  Can I cut the time to delivery?

    So, at the most basic level..I have:

    PowerPivot sheet1.  From cube1......OrderDate, InvoiceCountry, OrderCount....and a very basic calced column [BusName] that populates a column with "Business1"

    PowerPivot sheet2.  From cube2......OrderDate, InvoiceCountry, OrderCount....and a very basic calced column [BusName] that populates a column with "Business2"

    PowerPivot sheet3.  DimDate...relationships created from OrderDate from both 1 and 2 above to this lookup (as forced to)

    So my question is, how do I then create a pivot table that will allow me to sum up the orders for both business to get a total by orderDate or InvoiceCountry?

    I just seem to be going around in circles...and probably missing some very basic concept...

    Any help or insight would be so much appreciated!

    thanks so much,

    Nikki I

    Tuesday, March 30, 2010 2:04 PM

Answers

  • Hi, Nikki,

    Based on your description, I have populated my PowerPivot workbook with four tables:

    Table2:

    OrderDateInvoiceCountryOrderCountCalculatedColumn1
    1/1/2010 USA 100 Business1
    1/2/2010 USA 200 Business1
    1/3/2010 UK 300 Business1

    Table 3:

    OrderDateInvoiceCountryOrderCountCalculatedColumn1
    1/1/2010 UK 150 Business2
    1/2/2010 USA 250 Business2
    1/3/2010 UK 350 Business2

    Table 5:

    Date
    1/1/2010
    1/2/2010
    1/3/2010

    Table 6:

    Country
    UK
    USA

    Then created relationship so table 2 and table 3 are related to Date table and Country table with Date and Country as the look up table.

    With this PowerPivot workbook, when create pivot table, you can create a calculated measure simply as

    =SUM(Table2[OrderCount])+SUM(Table3[OrderCount])

    You can then add Table5.Date and Table6.Country in Row Labels, column labels, slicer etc depending on how you want to view the data, and the new measure will return you the sum of the OrderCount from the two business.

    Please let me know if you have any question.

    Thanks,

    Lisa

    Monday, April 5, 2010 11:09 PM
  • Hi was working on something similar, and come across the following blog that explains how to Union together Excel files into a single powerpivot table

    http://blog.contextures.com/archives/2010/09/06/powerpivot-from-identical-excel-files/

    Not sure if this will work for your Cube scenario, but thought I would share.

    Richard Mintz

    Wednesday, March 23, 2011 9:08 PM

All replies

  • Nikki,

    I think what you'll want to look at is things like the Data Refresh of multiple data sources & things like the RELATED() and RELATEDTABLE() expressions in DAX.  Here are some good articles that will get you started:

    Information on Related() and RelatedTable() from the MSDN blog above:

    • RELATED() follows an existing relationship between two tables and returns a value from the related table.
    • RELATEDTABLE() IS a new function which will follow an existing relationship and return a table which contains all the related rows from the specified table.

    Hope this helps.

    ~Meghann [MSFT]

    Tuesday, March 30, 2010 3:54 PM
  • Hi Meghann,

    Thanks for getting back to me so quickly!

    I'll review all of the above again, although I must admit I've read through most of them already and have been working with RELATED for a while now.

    My sticking point is that that I've got 2 different Fact tables sheets with identical structure but coming from different locations.  There is no 1-to-many link between them though - there is no way to "REALTE" them in first place.

    Both PowerPivot facttable sheets are related to the same DimDate lookup sheet but surely I can't use RELATED to pull back the facts into DimDate and then add together and aggregate?  There would then be no ability to work with that measure by other attributes (i.e country) as in order to do this DimDate would actually have to be a massivley exploded DimeDate x Country x Attribute1....x

    Its as if I need to union the 2 factsheets together...any ideas....I heard "Append" rumblings...I'll go back and have a play and will definitely reread your suggested resources.

    Meantime...any thoughts gratefull received!!  I could well be missing a very basic conceptual point here!!

    Thanks so much,

    N

     

     

     

    Tuesday, March 30, 2010 5:11 PM
  • Nikki,

    there are a few issues when you work on data coming from different cubes. To say it shortly, it would be much better getting data directly from SQL data mart, especially if you have the same surrogate keys used to join facts nd dimensions.

    Anyway, this is a short list:

    • Cube measures are not interpreted as measures but imported as text. You have to remember to change them to numeric
    • MDX generated by the designer doesn't contain attribute keys, and this could be a big issue to create a relationship between facts and dimensions
    • You need a table in powerpivot for each dimension you want to use to browse data coming from different cubes, so...
    • you should import each dimension as a single table (using an MDX query to $Dimension, if you write MDX by hand it is better) and in this way you will join fact table with dimension tables in the PowerPivot model. Provided you have good keys (see previous point)
    • If key attribute in dimension is hidden, you need to create a calculated column with composite key
    • If you have calculated measures on the cube, you probably don't want to import them in PowerPivot but you will need to recreate them in DAX (for example, any average has to be calculated in DAX and cannot be imported if you want to aggregate it...)

    Please vote this if you feel that support for SSAS data is not good in PowerPivot.

    http://connect.microsoft.com/SQLServer/feedback/details/538102/powerpivot-rc-doesnt-recognize-a-measure-from-ssas-as-numeric

    Marco

     


    Marco Russo
    Wednesday, March 31, 2010 2:33 PM
  • Nikki,

    While Marco makes a good point of the confusion of using Analysis Services cube data inside PowerPivot, you have to keep in mind that the PowerPivot product was designed for using things that are also not multi-dimensional like SharePoint Lists and Excel Worksheets as data sources, thus keeping the out of box characteristics simple.  However, using DAX as previously mentioned, you can mimic certain Multidimensional behaviors.

    Here is a snippet of useful information from some documentation that is in rough draft at the moment but I wanted to pass on to you:

    "Dimensional data modeling involves defining related properties/attributes of an entity (ex: customer, store, product, etc) from one or more tables, defining relationships between properties, and creating hierarchies that allow users to browse the attributes using known relationships between the various properties.  Once the dimensions are defined, then facts (ex: Qty Sold, Qty On Order, Sales Amount, etc) must be defined from one or more tables related to the dimensions.  Dimensional modeling can be a very time consuming process when developing a business intelligence solution and mistakes in the dimensional modeling can lead to incorrect results or a solution that doesn’t meet the users’ needs.

     

    To simplify the modeling process for typical business analysts and power users, PowerPivot and the VertiPaq engine do not require users to deal with the traditional dimensional modeling concepts but use the more familiar concepts of rows, columns, tables, and relationships between tables.  Each individual table in the PowerPivot model is treated as a dimension and each column is treated as a stand alone hierarchy with a built-in ‘All’ member at the top level and then all values that appear in the column as children of the All member.  This means that parent-child, and many to many dimensions are not possible in PowerPivot models.  Some additional limitations in PowerPivot dimensions include no custom rollup formulas, no calculated dimension members, no support for semi-additive measures, and no role-playing dimensions. 

     

    It is possible to define a relationship between tables in a PowerPivot model which allows special DAX functions to be used when attempting to reference rows from another table related to the current row in the current table. "

     

    There is a ton of more detail that will come out in future white papers, one in particular is alluded to here:

    http://blogs.msdn.com/powerpivot/

     

    Let me do some checking for you on some of our more seniored "DAX" experts and let you know what I can dig up.

     

    ~Meghann

    Thursday, April 1, 2010 5:15 PM
  • Hi, Nikki,

    Based on your description, I have populated my PowerPivot workbook with four tables:

    Table2:

    OrderDateInvoiceCountryOrderCountCalculatedColumn1
    1/1/2010 USA 100 Business1
    1/2/2010 USA 200 Business1
    1/3/2010 UK 300 Business1

    Table 3:

    OrderDateInvoiceCountryOrderCountCalculatedColumn1
    1/1/2010 UK 150 Business2
    1/2/2010 USA 250 Business2
    1/3/2010 UK 350 Business2

    Table 5:

    Date
    1/1/2010
    1/2/2010
    1/3/2010

    Table 6:

    Country
    UK
    USA

    Then created relationship so table 2 and table 3 are related to Date table and Country table with Date and Country as the look up table.

    With this PowerPivot workbook, when create pivot table, you can create a calculated measure simply as

    =SUM(Table2[OrderCount])+SUM(Table3[OrderCount])

    You can then add Table5.Date and Table6.Country in Row Labels, column labels, slicer etc depending on how you want to view the data, and the new measure will return you the sum of the OrderCount from the two business.

    Please let me know if you have any question.

    Thanks,

    Lisa

    Monday, April 5, 2010 11:09 PM
  • Hi was working on something similar, and come across the following blog that explains how to Union together Excel files into a single powerpivot table

    http://blog.contextures.com/archives/2010/09/06/powerpivot-from-identical-excel-files/

    Not sure if this will work for your Cube scenario, but thought I would share.

    Richard Mintz

    Wednesday, March 23, 2011 9:08 PM