Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

1 Table Must Filter All Other Tables - Power BI Embedded

Hi, everyone! 

 

When they decided on the structure and business rules, it was established that there would be only one report for all customers and the division of the data would be done by Power BI Embedded via the customer ID.

 

However, a modeling identical to the one in the current database (CosmosDB) was applied in Power BI. For a better performance of the reports, a redesign and an ETL process was suggested to denormalize some tables for a better performance.

 

When I started the remodeling I ran into the filter problem for the other tables because, since we only have one report for several customers, the customer ID should filter ALL the information from ALL the tables and not just the fact table.

I have looked at several solutions to apply the Star Schema model, but unfortunately, I am unable to get away from the Snowflake model, which greatly hampers the performance of the report.

 

Does anyone have any tips or suggestions on how to proceed? 

Here is a picture of the best template I've been able to get so far (it doesn't have the client ID/ClientRowKey in the "DeskGroups" table yet)

 

Thanks a lot!

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Anonymous - it looks like you are using the DimClients as a Row Level Security (RLS) for the Booking table, and it sound like you want to use this to filter the DimDesks.  Is this correct?

    I am thinking that you could try the following options:

    • Create a second DimClient table (hidden) which is contected to DimDesks.  Then configure additional RLS rules based on this table.
    • If the filters are hidden, Slicers based on the DimDesk table could be restricted using a measure lookup active client match.
    • Use Bi-direction relation from DimDesks and Bookings.  But this could create performance issues.
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you very much for the suggestion!

       

      I have a few more tables besides these in my model (maybe there are 15 more tables to be added).

       

      Because of this, I don't know if it would be worth adding another Clients table for DimDesks. Maybe I would have to repeat this process for the other tables as well (?).

       

      I will try to follow up with the other tips. Thanks a lot!

      Here is an illustrative photo of how I would like the filter to work (I know there are a lot of errors in this model, it's just to illustrate that clients should filter all other tables): Anonymous 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Anonymous - I am wondering if you can use the single DimClient table, and create individual inactive relationships.  The RLS settting would then need to trigger USERELATIONSHIP.  You could try but I starting to be concerned about performance because of all the additional RLS rules.  I will have to try to replicate this sometime.