Power BI Model

[ddfreedie]

Hi

I am new to PBI and pl help me understand some basic things.

I have a complex datamodel with 10+ tables. 5+ transaction tables (no aggregation) and 5+ Dimension tables (Master data) from SAP.

Around 5 tables have 5m records, and each of the transaction tables have 10-25m records. They are all 1-many joins only. 

Can PBI in general handle such model with complex joins, so many tables and with this data volume.

We do face issue and i can get to the specifics of the issue one by one later but would like to understand the above.

TIA.

[Samarth_18]

HI @ddfreedie ,

Yes Power BI can handle such model but the size of PBI file become large. You can follow below practises to reduce the data size:-

1. Load only those column which is required instead of complete table.e.g. Your table is having 10 columns but for report purpose you need 2 columns only so load only those 2 columns.

2. Try to load only those years of data which is required instead of all history load.e.g. you have 10 years of data but for reports purpose you need only two years data so load only 2 years of data.

3. Avoid using custom columns and try to create a measures.

4. If you have any filter which you are going to put on report level,try to put that filter you can put before loading data.

Thanks,

Samarth

[ddfreedie]

@Samarth_18 Thank you. 

Yes these are actually adopted. 

But on top of this the visualization break.

Issue 1:

We have the below approach

Database --->  Dataset(shared) ----> Multiple Visual/Report

The idea is that there are many reports which needs same data set and hence we came up with above.

But on this, the visualization seems to be breaking (cant show the view error) or its very very slow.

When trying Import mode for each report i.e. Database ---> dataset + Visual , it seems to be working fine. But we do not want this approach as there are 10s of reports which will hit the DB that many number of times for same table.

Issue 2:

I see the query from the PBI is nomore in the database. But still the PBI refresh says in Progress. Few days it gets over in 30 mins and few days it keeps running for 2+ hours but no query in DB. Not sure if there are ways in the PBI service where we can track the query/whats in progress with the dataset

[Anonymous]

Hi @ddfreedie,

#1, AFAIK, these query tables are different instances (power bi will create multiple query tables with data connector to get data from different database tables) so power bi will send requests multiple times.

For this scenario, you can try to get the table list from your database then create refer query tables to refer subtables from the main table records.

#2, I'd like to suggest you check the database logs about power bi request sessions and correspond operations to get further information.

Session Tracing (SAP Library - Administration Manual)

Displaying Logs and Traces (sap.com)
Regards,

Xiaoxin Sheng

[Samarth_18]

Issue 1:- you can try to implement data flows where you can put all the big tables in dataflows and import that dataflows to your reports so instead of refreshing it from 10 reports you just need to refresh 1 dataflow and all report will have latest update.

Issue 2:- You can enable performance analyzer and can check which table or column having issue.