Analyzing and Navigating Snowflake SSAS Structure on Power BI

[mmagagula]

I have a Fact Table with a measure called Loss.

This Fact Table has a dimension called Company (With Company Name column) and I have another 'dimension' table ONLY connected to the Company table called CompanySector (With Sector column). 

So my relationship is like so.

Fact Table (Loss measure) > Company > CompanySector = Snowflake schema

Now, when I select Loss and Company, I get the correct results in my visualization. 

However, when I select Loss and Sector I am expecting to get all Tax Losses for that Sector. 

However, my visualization shows the same value across the Sector. 

I can fix this using a Star Schema by embedding Sector column inside Company Dimension but I'd like to try and understand (and possibly fix) why this is happening on Snowflake structure?

[mmagagula · Accepted solution]

So I ended up getting it to work. 

In SSAS, I made sure that the Data Source View was correct with the correct relationships. I had no problems here.

I then right clicked on Cube and created a New Cube from the DSV. If your relationships are correct and your foreign and primary key are name the same then you can go on and click to the end.

Now, adding a cube will create dimensions. The mistake I made is I added a separate dimension called CompanySector and yet CompanySector is a subsidiary of Company.

So all I had to do was change it from displaying the CompanySectorId and instead display the CompanySector. To do that you make sure that in the NameColumn under properties fo the CompanySectorId you select CompanySector as a tableID and the name you want (e.g. Sector) as a ColumnID. You can of course rename the attribute to Company Sector instead of Company Sectord ID.

After that, Power BI displays correctly when you connect it to the Cube.

[v-piga-msft]

Hi @mmagagula，

I'm not very clear about your scenario. It may caused by your realationships for your tables.

If it is convenient, please share the data model and your expected, so that we can help further investigate on it?

Best Regards,

Cherry

[mmagagula]

Thanks. I managed to get it to work.