Query help to avoid duplicates

[largeselection]

Hi,

I'm very new to powerpivot. I am trying to pull data from an analysis services cube. Because I keep getting timeout errors when trying to pull the full data I have divided it into two sections, one which pulls the measures I want at the level I want (Field x Week x Location) and one which pulls the descriptive information for each field. The issue that I have is that there are a few instances where an entry has more than one value for "Level2". Out of 20k entries only about 500 fall into this category, but it's enough that I can't just create a relationship between my measures table and my descriptive information table.

If I were doing this in an Excel worksheet I would be totally fine just doing an index/match to pull in the descriptive information (since I don't mind if it gives me the first of multiple/different values for "Level2"). Also, I should add that part of why I don't mind if it pulls back one or the other of the two values that exist is because they are very close. Say for example one of my entries is "basket" and "Level2" represents cost - I might have 2 different costs in my data, but they are usually pretty close ($29.03, $28.94), so for the purposes of my analysis I don't mind if it pulls either of those. But I don't know how to replicate this looseness in powerpivot.

So is this possible? Would this be something that I implement in when I create the relationship (ie somehow specify a many to many (even though it's not that many) relationship)? Or would I pull the descriptive information into my measures table using a different formula than "related" and without creating a relationship between the two tables? Or is this something that I can address in my query so that it just pulls one value for each entry rather than all?

Thank you so much for your help or any direction you can give me! I've pasted the query I'm using below.

 SELECT NON EMPTY { [Measures].[Field1], [Measures].[Field2]} ON COLUMNS, NON EMPTY { ([MHier].[Mhier].[Level1].ALLMEMBERS * [Mhier].[Mhier].[Level2].ALLMEMBERS * [MHier].[Time].[Year].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Location].[Location].&[100000] } ) ON COLUMNS FROM ( SELECT ( { [MHier].[MHier].&[100], [MHier].[MHier].&[134], [MHier].[MHier].&[135], [MHier].[MHier].&[101] } ) ON COLUMNS FROM ( SELECT ( { [Time].[Time].&[2016], [Time].[Time].&[2015], [Time].[Time].&[2014] } ) ON COLUMNS FROM [cubMStandard]))) WHERE ( [Time].[Time].CurrentMember, [Location].[LocGroup].&[100000] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

 

[largeselection]

I have a thought, but not sure how to implement...

Is is possible to add a "helper column" in my descriptive information table? Something that essentially looks at the "Level1" description (in my example it would be "basket") and basically just says if this value is found in this column above this cell, then input "X1" or any value that increments up each time (something like "X & Row()", otherwise input "the value at "Level1". So that this new column would have only 1 of every "Level1" and where there were duplicates will now be unique values themselves like X1, X2, X3, etc. This way I could create the relationship based on that column since it would contain only unique values. I could create a formula like this in a regular excel worksheet, but I don't know how in power pivot.

 

[ImkeF]

As you're basically destroying the multidimensional structure of your SSAS data when importing them into Power Pivot you should check if it might be better to source the underlying relational database instead. That might perform faster.

If this isn't an option, you can use Power Query to import the data and do the necessary transformations before loading them to Power Pivot. As a rule of thumb: Instead of creating calculated columns in Power Pivot you should create the columns in Power Query instead.

 

[largeselection]

Thanks for the suggestions!

Unfortunately I don't have direct access to the underlying database, only via analysis services. I also don't have power query installed (but could look into it potentially) since the last time I tried to install it, it did not seem to play nicely for whatever reason. Also this is a work computer so they are pretty tight about installing stuff.

If it's not possible to get it then I suppose I could just dump the data into an excel sheet, put a formula there to limit the duplicates so I can just refresh (rather than using "remove duplicates), and then re-import that transformed table into power pivot to then create the relationship

 

[ImkeF]

There are issues with Excel 2010 but using the current PQ version with Excel 2013 should be fine.

 

[largeselection]

Yup that must be the issue. My office is still on 2010