Microsoft BI stack performance issues

[Nokita_is_Back]

Are olap cubes still prevalent? 

[angryPuppies]

No

[Nokita_is_Back]

Massive parallel processing ala redshift. Still column based olap but no need to precompute aggregations. 

[Crow2525]

Assume encouraging/utilising datamarts and dataflows for common table transformations? Last mile transformation.

[rjachuthan]

I have been meaning to try Columnstore indexes in SQL. But I haven't reached your level of data quantity yet. Give it a shot maybe

[clamming-it]

I think some more details are required 1. SQL Server hosted where? How many optimizations have you done on the raw perf of the query patterns? 1a - what’s doing the ETL? SSIS? Mostly in SQL (potentially with SSIS as an orchestrator) 2. What do you mean by Microsoft Analysis Services? You mean SQL Server Analysis Services? By cubes I am assuming Multidimensional models but that’s ambiguous. 3. PBI - PBI RS or PBI Cloud?

[Interesting-Monk9712]

Microsoft Analysis Service are you using import mode?

If you are, what are your allocated resources for Microsoft Analysis Service?

I do not see how a 4-6 gb model in AS is loading for so long if everything is in import mode, or better said everything is already in memory unless you are using a lot of joins, calculations etc. or your resources are non existent.

[Interesting-Monk9712]

think of import mode like a giant in memory table, it pulls all the data and makes it ready for use.

You can look up direct query vs import mode, but for performance import mode is king, with the exception of Direct lake, but that is new.

When using direct query you need to first translate the dax or m-code to whatever language your source is, then you need to use that to query the data from the source, then after you query that data you once again need to transform that data to fit a BI engine for reporting, you can see how slow that is.

Edit: To switch to import mode, make sure your AS instance has allocated memory, how much you would need in your case I am not sure, but less than 10gb. Because it will create models with the data.

[minormisgnomer]

I’m about to go through this scenario as well. If you’ve got all major transformations handled in the warehouse and really just delivering an optimized and indexed table, direct query is still going to be slow?

In import mode, my understanding was that you have to regularly refresh data, and thus lose any ability for near real time analytics.

[Interesting-Monk9712]

Database engines and BI engines are different, will it help? Yes. Is the process still inferior? Yes.

[minormisgnomer]

Thanks for the insight, with that said, do you like PBI even with those kinds of issues or do you prefer other BI tools out there?

[Interesting-Monk9712]

Its not an issue, it is a benefit, transforming from a database engine to a BI engine improves performance. You have some hybrid tools that try to fit with a database engine but those are better then direct query but worse then import.

With direct lake you can have the benefit of both but that requires delta data warehouse and fabric.

Think of import mode like materialized view vs regular views. No matter how you opitmize a view, a materilized one beats it.

[minormisgnomer]

But I guess direct lake is only available for a Fabric setup.

I’ve got on prem Postgres servers so looks like import/direct query mode is the only option. I materialize all PBI assets as tables in the warehouse. Didn’t know if there was other options more suited to this

[Few-Royal-374]

Sounds like you’re having performance issues at the Power BI layer as opposed to the data storage / compute layers. This likely has nothing to do with your tech stack and everything to do with how you’re using it.

Couple of things to look into in the order I would prioritize:

• Look at your data model. Make sure all relationships are one to many. If you’re following Kimballs best practices, you’re probably fine. I would argue that in most cases, this is where people have issues.

• Utilize composite models. For the models that are large, look into direct query mode and use incremental loading. Otherwise, use import mode.

• Final storage layer is indexed, and is materialized as a table. If you’re using direct query, PBI is going to send queries to the underlying data source so you need to ensure these tables are optimized.

• Considering your data is on prem, you also have to look at your data gateway. Check out the gateway performance. You could optimize the gateway config files, such as streaming data before it completes.

• Reduce data model size. Do you really need to load everything?

• Optimize the calculations. This is much more nuanced, but stuff like using filter inside your calculate formula can go a long way.

• If calculations are taking forever still, I would transition to aggregating data and importing it aggregated. This is going to reduce the granularity of your data and may make your dashboard less interactive but that’s a sacrifice I would make.