Loading dimensions from SSAS without measures.

Question

I have extracted some data from an SSAS cube (Adventureworks). but now i want to join this data with some other data from a linked table by productcategorie and month. You cannot create a relationship on 2 fact tables. So i have to use a dimenstion table where at least one column has a unique value. To achieve this I want to get the product categorie dimension from the SSAS cube. I want to do this using the cube because when the data refreshes i want to have the latest values.

To get the dimension into PowerPivot I openend SSAS import again and selected the dimension members i wanted. I clicked show include empty cells because I want only the dimension values, resulting in the following MDX:
 SELECT { } ON COLUMNS, { ([Product].[Product Categories].[Subcategory].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE

This results in empty data in PowerPivot. i know i can fix it by including a measure but seems to me this is a bug?

Answer 1

You might want to try a query against the UDM. I was able to import data for the Product dimension using the following MDX query to query the UDM

SELECT * FROM [$PRODUCT].[$PRODUCT]

Another alternative syntax would be:

SELECT * FROM [Adventure Works].[$Product]

HTH

John Desch

Answer 2

This behavior is indeed a confirmed bug and has been addressed in RC0 (next release after Nov CTP). Before RC0 is available, you would have to use the workaround of adding measure when you need to import a particular dimension attribute members. Thank you for reporting your experience to us.

Lisa

Answer 3

Thanks for confirming this, i understand your solution John but i don't want workarounds :)

Answer 4

To workaround the problem, you can also add connection string property DbpropMsmdFlattened2=true in the Extended Properties box in Table Import Wizard. The following are the steps:
1. Launch Table Import Wizard from PowerPivot | From Database | From Analysis Services & PowerPiovt
2. Specify the AS server and database
3. Click Advanced button
4. In the box next to the Extended Properties (the first entry in the list of the properties), type DbpropMsmdFlattened2=true
5. Connect to AS database, select your dimension members, make sure Show Empty Cell is selected, and follow the wizard for the rest of the steps

The import should now transfer all the members of your choice without the need to select measure at the same time.

The following forum documents this workaround in detail:
http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/cfac680a-032a-4f3f-9d1a-c639568e5e90

Thanks,
Lisa

Answer 5

This has been fixed in RC0/RTM as we are automatically adding the DbpropsMsmFlattened2 =true in the connection string.
For now, you can change the connection string and you should be good to go.

Thanks,
Deva [MSFT]

---

Deva

Answer 6

Unfortunately this syntax only works if you're connecting to SSAS as an administrator :-(

Chris

---

Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/