Need better approach of SQL use for SSAS Tabular Models

Question

Need better approach of SQL use for SSAS Tabular Models

Naidu PK

SSCrazy

Points: 2481
More actions
August 16, 2018 at 10:22 am

#377631

Hi,
I am new to SSAS tabular models. In the last 3 months, I did 3 models which act as source to self service power BI reports. Below is the approach I followed for all of my work. Is that approach correct? Are there better ways or best practices for performance?
Approach I followed:
1. Create SQL Views to get all the rows with amount/values of query/table as columns using Pivot
2. Import the SQL View into the model, establish the relationships between tables in the bim file
3. Create measures on the columns in model and hide the columns.
4. Let Power BI connect this model and user can do self service with the available measures.
My Qs:
1. Does PIVOT on 100s of columns can be a performance issue? If yes, any other way to avoid PIVOT to get row data as columns?
2. Best practices for .bim file or ssas tabular model?
3. Where to have DAX Queries in SSAS Model vs Power BI?
Thanks,
Naveen.
Every thought is a cause and every condition an effect

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

In terms of PIVOT, if you are using the actual PIVOT keyword as part of your query, that CAN be trouble, especially with a large number of resulting columns. It's often better performance-wise to use a CROSS-TAB type of query, but not always. It's also usually a good idea to test such queries and find out for yourself, as "your mileage may vary". That's also known as "IT DEPENDS" !

I can't speak to the rest of your questions, as I don't have the exposure to this stuff yet. I just figured I'd toss my two cents in on the performance of PIVOT, which in some cases can be fantastic, and others horrid.... That's usually why it's a good idea to try both and see what you get. However, be aware that the moment you need to pivot based on more than one column, you can no longer use PIVOT because it just CAN'T do that. Then a CROSS-TAB type of query is your only option.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 2

You can try to aggregate the data vertically (without the pivot) and feed it into the analytical engine. Then pivot from the analytical engine as it will have the data cached and use in-memory to pivot the data faster and more dynamically.

jklenk86 Old Hand Points: 388 More actions · Answer 3

Naveen PK - Thursday, August 16, 2018 10:22 AM
3. Where to have DAX Queries in SSAS Model vs Power BI?

In my experience, having DAX queries written and calculated in the model before Power BI connects to Analysis Services is largely better for performance and report load times.

Ultimately, whatever route you choose, just be consistent. I've seen issues (largely visual load performance but also memory allocation problems) when DAX queries exist in both the pbix and the model, and are actively used in conjunction with each other.