Best way to Model data based on existing constraints

[Anonymous]

I have to create multiple Power BI reports based on 3 tables in Azure DB. There are no joins between any of these tables,as of now.
The biggest table of these 3 has a size of 6 GB, which could double in future.
Different tabs of the report would be powered by one of the 3.
Some of our use cases would require creation of calculated columns and calculated measures using DAX formulas - sum, rankx, calculate, filter, IF etc.

Given these constraints,what would be the best method to model our data and why -
1. Direct Query storage mode in Power BI
2. Dual Query storage mode in Power BI
3. SSAS tabular model in Direct Query mode
4. SSAS tabular model in-memory mode
5. SSAS multidimensional
5. Anything else which I have missed out.

[zoloturu]

@Anonymous ,

There are different constraints:

* Not all DAX functions are supported in DirectQuery mode - https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-directquery-mode-ssas-2016.

* You also have a limit of RAM, so I'm not sure if it can handle your requests. We need exact numbers of RAM and report's usage as well

* When you connect to SSAS (Tabular/Multidimensional) using live mode then you are not able to create calculated columns via DAX on Power BI side

So based on this I would recommend to think on combined model in Power BI. There is a great article about it - https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table. Read and follow all steps.

In general it means you will have different queries (tables) in Power BI:

* dimension tables with Dual and some with Import mode

* fact tables with DirectQuery mode

* smaller fact tables with Import mode (only frequently used columns aggregated on higher level).

Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups