cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

SSAS Relationship Issue -- Only 1% of Data

Not sure if this is the right place to post -- if not, please point me in the right direction 🙂

 

I have SSAS sitting between my data warehouse and Power BI. In the data warehouse, I can query against the fact table and join to dimension tables, and get back the exact data that I expect. In the Power BI report, however, some of the dimension data isn't getting picked up correctly -- maybe only 1% of the data.

 

Specifically, the fact table has both location and device IDs. A SQL query in the data warehouse that starts with the fact table and LEFT JOINs on both the device and location table brings back the expected device and location records in all cases. In the Power BI report, however, there are some fact records showing up with no location, only the device. Almost all the records are linked correctly, it's only two devices that this is happening with and a small percentage of the overall fact records.

 

Is there something in the SSAS relationship definitions that could be causing this? I don't really know where to look to resolve this problem, since it works in 99% of the data and also works fine when querying the data directly.

4 REPLIES 4
Highlighted
Resident Rockstar
Resident Rockstar

Re: SSAS Relationship Issue -- Only 1% of Data

Hi @GregOnBI ,

I still have a little confused about your scenario.

If I understand it correctly that you didn't get all of the data from SSAS in Power BI Desktop?

Does everything work fine in SSAS?

If it is convenient, could you share some data sample or screensorts to describe your scenario better?

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: SSAS Relationship Issue -- Only 1% of Data

Hi @v-piga-msft ,

 

You're right, screen shots will definitely help.  I put together a few screenshots, from both the Power BI report and the data warehouse SQL query results.  

 

MissingLocationExample.png

To try to clarify, the Room is missing but only in some cases, and only when the data flows through SSAS to be displayed in the Power BI report.  I queried the same fact records directly in the data warehouse, and the Room is not missing.  

 

Additionally, in most cases, the Room is shows up as expected in Power BI.  This problem only appears for two different Devices.  But, Devices and Rooms are not linked in SSAS -- the link comes from the fact table having a link to both the Room table and Device table.  So, it doesn't make sense to me why this problem only appears for a small sub-set of the fact records.

Highlighted
Frequent Visitor

Re: SSAS Relationship Issue -- Only 1% of Data

As an update, I determined that the particular Room that is linked to this device did not get pushed into the SSAS cube.  The hourly cube update process in Azure has been running successfully, and I finally found that the SQL query that populates that table had a bug that kept a few Rooms out of the cube.  I've fixed that now and the next refresh should pick up the missing data. 

 

But, at least it makes sense now -- the Room wasn't showing up on the report because it didn't exist in the SSAS cube data.  I'm glad the issue is resolved now, since I really didn't have a clue what could be the cause when I first found it.

Highlighted
Resolver III
Resolver III

Re: SSAS Relationship Issue -- Only 1% of Data

This sounds like a modeling issue.  How are your fact and dimensions joined in the Tabular model?   

Please check relationships (1 to many , many to 1 )  - ensure you have the right direction on them on your model.  Reprocess and then you should see what's expected.   Recheck your queries to the DW that are sourcing your dimensions and measures.


Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.