SQL: Better to connect to raw table and apply steps or filter/join with query?

[WESTi]

We have an on-premise data source (SQL Server 2008 - I know its old...) housing our ERP data (Microsoft Dynamics AX 2009). We are currently using PowerBi to connect to this data using an import connection which is suitable for those current datasets/reports. I am now looking at building out standardised data sets for various finance related functions using Direct Query connections.

I am trying to find the best way to set these data sets up. My options so far are:

1. Simply connect to the server and data base using the PowerBi prompt and apply transformations/joins using the PowerBi native functions (steps etc.). Pretty much click everything together.

2. Write a TSQL query that performs all filters, joins and transformations and connect using that.

3. Pretty much step 2 but using Views.

I am not using SSAS as our SQL Server version is so old and I hear PowerBi has issues connecting to this (multi-dimensional connections are not supported until SQL Server 2016).

Basically I would like to understand how PowerBi handles Direct Query connection requests (i.e. user interaction with a report).

1. If the data set is clicked together through PowerBi would it effectively construct SQL queries based on the filters etc applied by the report and user and pull only the appropriate data?

2. If this is the case will it perform the same if I script the source data through using TSQL or connecting to a view - or is it irrelevant?

3. Or, does PowerBi always attempt to pull down the entire data set (table/s) from our database and then apply its steps (filtering/transformations etc.) after pulling the data down?

Am trying to ensure as much performance as possible. Direct Query is required as, for example, we will have people post journals/sales orders etc. and want to see the immediate statement listing.

If anyone has any input or links for me to read through to understand how PowerBi issues or constructs its queries that would be great. 

[Seth_C_Bauer]

@WESTi Just to add a few comments. SSAS for MD is support much earlier than 2016. The current support is SQL Server 2012 SP1

I am in agreement with what has been said already, there are a variety of factors. Another thing to keep in mind. If you are using Direct Query, you won't have some of the modeling capabilities in Power BI to transform the data, so that's where Views become very useful because they add that layer of abstraction that you can manipulate to support your visuals. I can't say I've tested all these scenerio's (I think I will shortly).

[Seth_C_Bauer]

@WESTi Just to add a few comments. SSAS for MD is support much earlier than 2016. The current support is SQL Server 2012 SP1 CU4 or later versions of Analysis Services.

I am in agreement with what has been said already, there are a variety of factors. Another thing to keep in mind. If you are using Direct Query, you won't have some of the modeling capabilities in Power BI to transform the data, so that's where Views become very useful because they add that layer of abstraction that you can manipulate to support your visuals. I can't say I've tested all these scenerio's (I think I will shortly).

[sdjensen]

@Eno1978 - just a small comment regarding the support for SSAS MD. If you don't have SQL Server 2016 you will need an enterprise or BI edition of the SQL Server since DAX to MDX is not supported in the standard editions of SQL Server 2012 or 2014.

[Greg_Deckler]

Some general comments about SQL data access:

If your data is in a single table and is not large then querying a table will be faster than creating a view first and querying it for required data as it will avoid a step. If your data is not large and the columns your where clause are properly indexed then in most cases queries will be faster going directly against tables. Views should be used when you have a very large data set in a single table and you need to operate on small subset very frequently. In this case views will fetch the required data only once thus this will help minimize re-execution of search queries (on single table or a join). Bottom line, it is going to depend on your data and what your queries are so you are probably just going to have to test each approach and see what works for your situation.

Now, for the other perspective, there is a school of thought that in general you should simplify queries as much as possible by using views. The prudent use of views makes management of your application simpler and also helps you avoid repetition of query logic (joins, WHERE clauses, etc.). However, views can be ill-suited for a particular query and thus lead to poor performance as they might lead to unnecessary operations for particular queries.

[WESTi]

Thanks for the responses everyone. Your insights are much appreciated.

The issues with transforming data in PowerBi while using Direct Query was the key chain of thought in determining whether to use views so that I could consistently refer to the same data set if it was going to be utilized over multiple data models. Relying on written queries could become difficult to manage if I need to change how they work (with a view I could update one object and have that flow through to all data models). For now I will stick with a standard query and implement views should the need arise.

I think from here I will write the queries to build out the base data for the model and to reduce the amount of steps that PowerBi needs to apply. Though, when I get time I do plan on testing to see exactly what is happening when PowerBi is connecting to the database when the data model has been 'clicked' together, i.e. run a trace to understand the mechanics of what is going on.

Any further thoughts are welcome 🙂