SSAS Relationship Issue -- Only 1% of Data

[GregOnBI]

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.

[luisrh]

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.

---

[v-piga-msft]

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

[GregOnBI]

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.  

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.

[GregOnBI]

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.