Composite model or not?

[PowerRon]

HI

we have created a dataset that resides in a separate workspace.
This dataset contains millions of rows on transaction level.
Probably this is not the way to go because the dataset is already getting too big, bigger than 1 GB.

Ou requirement is that we have a few users that really want to see the data from aggregation to detail. And we have more users, also from other departments, that want to see the data just on an aggregated level. 
So everybody has a live connection to a dataset in a dataset workspace. 

What would be the way to go for the few users (which are the most important users by the way) that also want to see detail?
Create a composite model with the dataset in the workspace (which is then more an aggregation) and a Direct Query connection to the database when they want detail?
How to create this step by step?

I think we are going to have this use-case with more area's, more fact tables which all contain more than a 100 million rows.. Would be nice to have some standard 

regards
Ron

[parry2k]

@PowerRon Agreed with @Greg_Deckler , if you can control access to the backend database, the user will be able to see data based on aggregation but not otherwise. The only point of concern will be user experience, visual will given an error which not look decent, but in theory, this solution will work.

[parry2k]

@PowerRon Great question, I don't think you can restrict that, since the call if DQ or Aggregation to be used is based on how the visualization is done, if a visual needs detail-level data, it will go that route and you cannot restrict that. 

[PowerRon]

Thnx @Greg_Deckler and @parry2k for explaining. Completely clear. A shared dataset with aggregation and Direct Query. Thnx.
One more question comes to my mind 🙂
What if everybody is allowed to see / query on the aggregation but only a few people (owner of the data) may dive into the details? Can that be handled with just one shared dataset, or do I the need two:
- one with Direct Query option

- one without Direct Query option, so only having the data on an aggregated level?

regards

Ron 

[Greg_Deckler]

@PowerRon So, untested but in theory you would control access to the DirectQuery (source) at the source to limit those who can actually get to the source data but since the Aggregation table is separate then it should work for everyone. In theory...

[parry2k]

@PowerRon what @Greg_Deckler suggested, you have aggregation in the shared dataset and also the direct query in it, and the Power BI model will decide when to use aggregation and when it switches to DQ. This will get you what you are looking for. Once you have created the aggregation in your model, you don't have to redo it, on refresh, aggregation tables (import) will automatically update. Not sure if this answered your question or missed the point.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[PowerRon]

Hi @Greg_Deckler 

Thnx for your answer.
But what If I want the aggregation (so in fact the dataset that is in a dataset workspace) to be able to be used by other persons as well and I don't want to over and over create that aggregation?
So in fact my question is: can I combine a shared dataset (live connection) with Direct Query? Whereby the shared dataset acts as the aggregation?

[Greg_Deckler · Accepted solution]

@PowerRon Right, so you are on the right track. DirectQuery with an aggregation table:

Power BI Aggregation: Step 1 Create the Aggregated Table - RADACAD

DirectQuery only gets used when you get down to the detail level.