Can one have to many DAX formulas?

[shophoney]

I'm wondering if having about 100 DAX measures could slow down my system. My file is about 95mb, SQL connection to our retail management system 4.7gb.

But it seems to be getting slower. When i refresh now it's about 15-20 to update daily.

Any thoughts? Suggestions on speeding it up? Things to avoid making a system slow.

All data is brought in through power query, connection to a 2012 SQL Server file. My system is a i7 Xeon 6700, 32gb, SSD ...

Thanks

 

[gazpage]

Measures are calculated at Query and not at refresh so it shouldn't slow things down. Calculated columns are calculated at refresh so I suppose they would slow things down. How many rows of data are you pulling in?

Can you get SSAS tabular on your server? Or if you can get the gateway installed you could put it on the Power BI service. Either would allow you to schedule a refreshes out of office hours.

 

[shophoney]

Measures are calculated at Query and not at refresh so it shouldn't slow things down. Calculated columns are calculated at refresh so I suppose they would slow things down. How many rows of data are you pulling in?

Can you get SSAS tabular on your server? Or if you can get the gateway installed you could put it on the Power BI service. Either would allow you to schedule a refreshes out of office hours.

Hi I have several tables. I would guess about 18 tables with 200,000 -300,000. And about 12-24 columns depending.

And about 15-20 calculated columns.

Could the 60-80 pivot tables drawn from the power pivot data slow it down too?

I refresh everything daily.

 

[gazpage]

I should have been clearer. Measures are only calculated when they are used, it doesn't matter how many you have, it matters how many times they are used. The number of pivot tables matters rather than the number of measures.

That said, power pivot is so fast id be surprised if they took anywhere near as much as a second each. I would expect the time to be get n the last import, but that doesn't seem like that much data.

Can you refresh the tables one at a time and see how long they take? I presume you are using Power Query? Can you check the queries and see if they are using query folding.

 

[shophoney]

Not sure what query folding is?

From what I read, it sounds like rather than having power query remove unneeded data I should have my sql query filter the data first prior to having powerquery remove data.

Example I have power query remove sales prior to 2015. Would it be better to have the sql query remove the data, then pass it to power query.

Thanks

 

[gazpage]

Query folding is when Power Query takes your transformations, writes a SQL query, and passes it back to the SQL server. If you go into your query and right click the steps and click View Native Query. At some point in your transformations you may see this option greyed out, in which case this step and all subsequent ones are done locally. So to answer your question, you should let Power Query write the SQL queries unless you are a SQL ninja (I'm not). You want as much as possible folded so if you see your native query greyed out it might be worth seeing if you could reorder your steps. Basically if you have a step that uses transformations not available in SQL you should do that later if possible.

Few questions given I don't know what your data and model are like.

Is your data model a star schema with facts and dimensions separated? Why are there so many tables? Is there any overlap between them that could be reduced? Is it possible to have queries reference one another at all, or use staging queries?