Datamart report slow

[itchyeyeballs]

Hi All,

I have been playing with the Datamarts feature, has anyone else noticed it's really slow?

I've recreated a portion of an existing dataset as a datamart, (reports against the original larger/more complex dataset run fine). My first (basic) report against the Datamart is really slow, just expanding rows in a single matrix table takes ages (380k rows fact table, 4 small dimensions).

I'm using the option to connect to the autogenerated dataset rather than the SQL endpoint as I assumed that would give the best performance.

Anyone else find the same? It's a preview feature so some issues expected but I'm a bit disappointed with it at the moment. 

[itchyeyeballs]

Hi all,

MS has released an update to dataflows that appears to indicate better performance, has anyone noticed any improvements? (is there a way to check if the new "auto import" feature is on?)

https://powerbi.microsoft.com/en-us/blog/whats-new-in-datamarts-august-2022/


My tests still seem really slow, if I connect to the datamart it's worst, connecting directly to the datamart dataset is slightly better but still not great and nowhere near as good a stand import dataset.

[otravers]

Is your existing dataset in Import or DirectQuery mode?

[itchyeyeballs]

Import, direct query has always been way too slow to be useful with our on prem sql server and gateway.

This datamart experience feels like it has the same direct query bottleneck

[GilbertQ]

If it has always been slow even when importing I would suggest making sure your dataset is using a star schema. 

DirectQuery is as fast as the underlying data source, along with once again a star schema design.

[itchyeyeballs]

Hi, we are using a star schema.

As a test I lined up two instances of PBI Desktop, one using the original data model and one using the new datamart (both identical in terms of tables, joins and import modes).  I created identical (simple) matrix visuals and tried to expand the same row in each version of the report.

The original data model has an instant reaction every time, the datamart version shows a spinning loading icon for at least 5 seconds (sometimes more)

[GilbertQ · Accepted solution]

Hi @itchyeyeballs 

yes this is because the datamart using DirectQuery to the underlying database (SQL Server), while the other report uses the Power BI Vertipaq engine which is super fast.

[GilbertQ]

Hi @itchyeyeballs 

This is a version 1 of datamarts so it is still a work in progress.

Another thing to note as of today is even with the auto generated dataset it is still querying the underlying SQL Server database. So if there are a lot of rows and columns this will take some time to show all the rows.

It should improve over time.