June 17, 2015 at 8:30 am
Good Day
I have a question/issue on a dimension I created in SSAS.
See attached files (Dimension) and (Relationship).
What I am trying to accomplish here is to query against another dimension [Dim Acct Profiles] by field [VZW ALT 4 NM]. If I select a business
e.g Bob's Builders, SSAS should query the owner of Bob's builders and display only one name for the field [vzw alt 4 nm] - eg Bob.
However what it is doing is pulling all the names in the column [vzw alt 4 nm] and displaying the same FACTS across all names.
I know this has something to do with the relationships i have set coming from the data source where I have an intermediate table between FACTS and Accounts called [DimEntDataOrgProfiles] but I cant seem to understand where one unique account wont display the owner of that account only
Thanks
June 17, 2015 at 8:44 am
Could you post the details of the "Dimension Usage" in your cube? This is where many:many relationships have to be defined in your cube.
June 17, 2015 at 8:48 am
See attached Martin
I have this key directly in the FACT table as well as in the intermediate table (ie acct profile id). I tried it both as a regular and referenced relationship type to no avail
June 18, 2015 at 2:04 pm
I have had a bit more time to look at this issue and wanted to explain further with some clarity.
In the DSV, the relationship are as below
DimEntOrg-------->DimEntOrg_Acct_Profiles----------->DimAcct_Profiles-------->FACTTABLE
I am querying from the FACT Table slicin by an attribute in DimEntOrg [vzw_level_4_nm].
I believe the reason why the referenced dimension usage isnt working is because the DimEntOrg_Acct_Profiles table doesnt touch the FACT table directly, it has one more hop (DimAcct_Profiles) to go through.
I do however have a key in DimEntOrg_Acct_Profiles that is also available in the FACT Table.
Is it possible to create a dimension usage hoping 2 tables?
June 19, 2015 at 2:13 pm
So I was able to get my problem resolved.
I explained there were two hops from the dimension to the FACT table. The reason I was getting duplicate rows was because the first table had no relationship with the FACT.
Thanks to the link below, I was able to set the first table as a Measure group and connect my DimEntOrg dimension to it with a Many to Many relationship in the dimension usage.
The keys are now connected and all the necessary values are populating correctly.
This learning curve took me through the use of M:N as a dimension usage relationship - one I had never used before.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply