Forum Discussion
AdministratorPerformance PowerBi. What is the most optimal?
Good afternoon community.
I have some doubts regarding the performance with 3 custiones.
1- What is the best performance with respect to the ETL part?
I understand that to do data cleaning in powerBi it is convenient to do it from powerQuery and not with DAX functions.
But suppose I have a SQL server database and a table of 50 million records, of which 10 million have to be filtered because they are not useful.
It is convenient to make a view in SQL, clean all the data (do the ETL process) and then from PowerBi I take that view with the data that is useful to me?
Or do I take the table directly from powerBi and do ETL from powerQuery?
2- Is it convenient to put together a "massive" data flow from which you can then connect from different reports?
Assuming this flow connected to a SQL server database and took 1 hour to upgrade. Assuming a super consultation.
Or is it better to query the database directly in each report?
One way I see that it is better not to stick to the database many times for the same information, but in the other way I think it is more efficient.
In my opinion it is more effective to throw 100 small queries to the database, than a single query that contains the 100.
3- What would be the best way to "relate" or solve this problem.
I have 3 tabs. Form A/Form B/Users.
Form A and B are related. And both have a user code that relates to the users table.
BUT, the user of Form A and B are not necessarily the same.
Should you duplicate the User table and relate each one to each Form? Or should you have the table loose and search from both forms with a calculated column your user?
Or if there is another alternative it would help me.
This example also appears to me in case in the same table I have more than 1 way of relating to another.
Example if Form had a cod_usuario_creador / cod_usuario_aprobador / cod_usuario_modifica.
Thanks a lot!
5 Replies
- Migasuke
Memorable Member
Hi Syndicate_Admin ,
1. If you can - prepare data in the source. If your data source is a database, you can also take advantage of query folding - basically you do all your steps in Power Query, but they are automatically prefiltered on the side of the database. Check more here:
https://learn.microsoft.com/en-us/power-query/power-query-folding
2. Difficult question. If you know you might querry the same data multiple times in different reports, it might have sense to prepare some Dataflow, which stores all your data and you don't overhwelm your database. If the SQL queries pull different data I would skip Dataflow.
3. Can you provide some printsreen of model or desired outcome? - AllisonKennedy
Community Champion
Wow! Lots of great questions. You may get better response if you post them as 3 separate questions, as they are different enough that different people may be required to answer them. I don't have all the answers but will try.
1) In my experience it's better to push the transformations back as close to the source as possible. So creating views in SQL and pull those into Power BI is best.
2) This is a tricky one and it depends. How much of the dataflow is used in each report? How much overlap is there between reports? Keep in mind that when a dataflow refreshes, the entire thing must refresh or if one part fails the entire thing fails, so may be better to split the data flow into 'core dimensions' that get used across most reports, and then put the other tables in another data flow or dataflows.
3) How does Form A relate to Form B? Not knowing the forms, I'd guess that User relates to Form A and User relates to Form B but that form A doesn't relate directly to form B. I need more detail on the forms though to confirm that.
For the different users (created by, modified by, approved by, etc) you can use role playing dimension and inactive relationships, or you can have an 'approvers' user table and a 'creators' user table. This also depends on your desired end result -
do you want to see how many forms Jorge created and approved in the same visual? then use role playing dimensions
Or do you want to filter by Jorge as an approver, and then filter by the creators in a separate filter / visual - then use approvers and creators separate user tables.
Alexx95Helper I
Maybe this can help for the question 3.
Form A have one Form B associate. 1 to 1 relation.
Both of them have a User. BUT users between they can be different.
In my experience i can do this in 3 ways.
1- With a loose table, then in the Forms tables i can use a calculate column, with something like this: Caculate(users_table(user_name), user_table(id) = FormA/B(user_id))
2- With duplicate table, then userA is related with FormA. And userB is related with FormB.
3- With one user table and related with both of then, BUT one of the relation should be inactive.
If i understand correctly AllisonKennedy you said that if i need 2 filters in mi visuals that are for users ( in this case we need that ) the correct way will duplicate the table.
I understand that the user table generally is "small", this option make sense.
Migasuke I don't know if i understand correctly, but you said that how my source is a SQL database literally is "almost" the same because query folding is optimiced for this source?
I understand that if we can use the source to ETL process we should use that, but is irrelevant in this case?
Thanks so much both.- Migasuke
Hi Alexx95 ,
I am affraid I won't help you with your third question, nevertheless I can clarify you additional question towards me.
I give you example of two different scenarios:
1. I want to pull 100K rows from Excel and I need to select only top 10K rows.
In Power Query I am forced to pull all 100K rows and then remove the rest 90K of rows.
2. In case of query folding, which is possible on Azure SQL, and other SQLs scenario is different.
In Power Query I want to keep top 10K out of 100K. Power Query sends SQL command back to the database and I will not download all 100K but only 10K. The reason is, database will return already processed data with only 10K so the operation is done on the side of database and not on the side of Power BI.
So outcome is:
Do your transformation in the source if you can, nevertheless with query folding sources (databases) it does not really matter. - AllisonKennedy
Yes, you understood correctly. The best option in this case would be to duplicate the User table so you can filter by creator, and then further filter by approver.
