As per this docs.microsoft.com/en-us/power-bi/desktop-aggregations#validations, the detailed table must be a direct query and is it because of this you were not able to use the Calendar table in Manage aggregations
Great video! A question, it is not possible to filter the table using slicers unless the field of the slicer is listed in the "group by" function. Is that right? Thanks!
Hi Paz, in the Aggregated table why didn't you created a relationship with the OrderID with the Big Fact table? Love your videos! You are really good at explaining all concepts :)
In preparing your aggregate table, why do you not first add a calculated column for total sale amount for each row by multiplying unit price by the quantity before aggregating so you can then sum the total sale amount when you group by to yield total amount per order number. It seems that if you sum price and quantity separately you can no longer get the total sale amount per order, because multiplying sum of quantity by sum of unit price will not yield the same result . Or have I missed something very basic?
@@CurbalEN okay, got it. Sorry to nitpick. Found the video very useful to understand the rather mysterious things that power bi does in the background. And I agree with your sentiment that some of the newer features are bordering on making powerbi overly complex for self service bi.
Great, again you have taken a very complex subject and made it easy to understand for others. If you're missing a topic for Dax / Statistic Friday, I can suggest "correlation"
Using data from two different databases in SSMS requires a many to many relationship in our testing, even when all sources are in DirectQuery mode. Can you help explain this?
Hi Ethan, Any relationships that are cross-source must be defined as having a cardinality of Many-to-Many, regardless of their actual cardinality. In my example, all tables come from the same source. More info here: docs.microsoft.com/en-us/power-bi/desktop-composite-models Or here: m.ua-cam.com/video/au1buy6NBAI/v-deo.html /Ruth
Ruth, Many thanks for your swift response. A follow up question for you: Would you suggest then avoiding aggregation / composite models when data is cross source and Many:Many is forced? In our scenario, most of our data is within a single database EXCEPT the Date Dimension. IT does not want to duplicate multiple date dimensions across all the databases. And based off of your examples, we cannot use an M Code Date Dimension AND avoid Many:Many when we do an aggregation model. We noticed in SQL Profiler that when aggregations is used with Many:Many, sometimes it used Direct Query or In Memory (which isn’t ideal). This in addition to being forced to use a Direct Query Date Dimension is limiting our use of this feature. To us, this great feature should be really only used on top of a defined DataMart and not multiple sources within the PowerBI Model. Any guidance would be appreciated.
Unless you are experienced with M:M I would avoid them and specially if you are trying to create self-service models. So, either you add the date dimension in the model or you import the data into power bi (instead of DQ or Live). That is your best bet! /Ruth
Thanks! Sorry, another question: Are you aware of a limitation on the Manage Aggregations when you want to sum decimal numbers? We are creating the Aggregated table in Power Query utilizing M and query folding. Everything was going great until we went to Manage Aggregations. We are not allowed to Sum our numeric columns. Going back into Power Query, it seems that when we used the Group By it converted Whole Numbers (123) to Decimal Number (1.2). If we try to Change Type we get a notification that step is not supported in Direct Query Mode and wants us to Import. Importing is not an option, we will not be able to set the table property to Dual. We know we can do the aggregations on the SQL Server side, but we thought this would work as well and we wanted to compare the two. Do you have any insights into this?
Yes, happened to me too. This is what the docs say: If the Query Editor query is overly complex, an error will occur. To remedy the error you must either delete the problematic step in Query Editor, or Import the data instead of using DirectQuery I was changing a column type to date, so I wouldn’t call that complex. What I think is happening is that when you make a transformation that breaks the query folding then it forces you to import. Group by will break query folding... test it. /Ruth
Hi Ruth, when we talking about big data, the big data table would always need to be direct query because it's too large for import or dual right? Direct Query should be available for all datasources right? thanks and great video again ;)
Hi, exactly, when you have a lot of data, your only option is Direct Query. If you have composite models, you can only use Import with Dual to avoid M2M, so either you convert everything to dual and import or you have dual and direct query. Import and direct query is a BIG NO-No. Hope I explained myself? /Ruth
Hi ruth can u plse share this database wid me . I downloaded the northwind database u were talkin about but it doesnt have ordersbig table and i kind of have to give a presentation on aggregations in power bi this monday.
Don't understand why you need all these aggregations if you can make a separate table in Power Query with aggregated data (as you did) and use it when appropriate instead of Big table?
Hello, i like to see your videos and the way you explain topics. I have a question if Power Query can help. I have a list of names with different Start and End dates columns with different leave types. Dates may be in continuation but in different rows. i need Power query to club the dates as 1-20 March in one row and 23-26 march in another row as Annual leave and sick leave remains as is in a separate row. Possible? Name Start Date End Date Leave Type Mr. Smith 01-Mar-20 05-Mar-20 Annual Leave Mr. Smith 06-Mar-20 09-Mar-20 Annual Leave Mr. Smith 29-Mar-20 31-Mar-20 Sick Leave Mr. Smith 10-Mar-20 20-Mar-20 Annual Leave Mr. Smith 23-Mar-20 26-Mar-20 Annual Leave result expected: Mr. Smith 01-Mar-20 20-Mar-20 Annual Leave Mr. Smith 23-Mar-20 26-Mar-20 Annual Leave Mr. Smith 29-Mar-20 31-Mar-20 Sick Leave
Hi Ruth, I am working with large Data and this video helps in case case of aggregation, thanks for that. But the next level difficulty is how to drill through actual table after aggregation. Like I am having 10 billion rows and after aggregation, I can get summarized data very fast, but when I drill through detail table It take very long time. How can I get into detail data very fast?
As per this docs.microsoft.com/en-us/power-bi/desktop-aggregations#validations, the detailed table must be a direct query and is it because of this you were not able to use the Calendar table in Manage aggregations
Thank you, thank you, thank you !! Will pin your comment for more visibility!
/Ruth
Great video! A question, it is not possible to filter the table using slicers unless the field of the slicer is listed in the "group by" function. Is that right? Thanks!
Great video. What's the minimum laptop configuration to run the data? What's the configuration of your laptop?
I have a surface book 2 , but you can manage with less depending on your data.
/Ruth
Gracias Ruth, con este video nos abre un nuevo horizonte para la consulta de datos. saludos.
Asi es! Ahora a ponerlo en práctica!
:)
/Ruth
Hi Ruth,
Have you tried to use average as an aggregation ? I can not see the option.
regards, Akthar
thank you very much Ruth. I posted this before watching the video
No worries! Hope it was helpful!
/Ruth
Hi Paz, in the Aggregated table why didn't you created a relationship with the OrderID with the Big Fact table?
Love your videos! You are really good at explaining all concepts :)
In preparing your aggregate table, why do you not first add a calculated column for total sale amount for each row by multiplying unit price by the quantity before aggregating so you can then sum the total sale amount when you group by to yield total amount per order number. It seems that if you sum price and quantity separately you can no longer get the total sale amount per order, because multiplying sum of quantity by sum of unit price will not yield the same result . Or have I missed something very basic?
The purpose of the video is to show aggregations, so the example might not be the best!
/Ruth
@@CurbalEN okay, got it. Sorry to nitpick. Found the video very useful to understand the rather mysterious things that power bi does in the background. And I agree with your sentiment that some of the newer features are bordering on making powerbi overly complex for self service bi.
😂😂 Don’t worry, your question was very valid!
Let’s vote for a “disable enterprise features” button !
;)
/Ruth
Great, again you have taken a very complex subject and made it easy to understand for others.
If you're missing a topic for Dax / Statistic Friday, I can suggest "correlation"
Thanks Sten! Makes me feel proud when I hear that feedback ! 😊
...and correlation is on my list , but first up is poisson.dist ... cool lite distribution;)
/Ruth
Using data from two different databases in SSMS requires a many to many relationship in our testing, even when all sources are in DirectQuery mode. Can you help explain this?
Hi Ethan,
Any relationships that are cross-source must be defined as having a cardinality of Many-to-Many, regardless of their actual cardinality.
In my example, all tables come from the same source.
More info here:
docs.microsoft.com/en-us/power-bi/desktop-composite-models
Or here:
m.ua-cam.com/video/au1buy6NBAI/v-deo.html
/Ruth
Ruth,
Many thanks for your swift response. A follow up question for you:
Would you suggest then avoiding aggregation / composite models when data is cross source and Many:Many is forced? In our scenario, most of our data is within a single database EXCEPT the Date Dimension. IT does not want to duplicate multiple date dimensions across all the databases. And based off of your examples, we cannot use an M Code Date Dimension AND avoid Many:Many when we do an aggregation model.
We noticed in SQL Profiler that when aggregations is used with Many:Many, sometimes it used Direct Query or In Memory (which isn’t ideal).
This in addition to being forced to use a Direct Query Date Dimension is limiting our use of this feature. To us, this great feature should be really only used on top of a defined DataMart and not multiple sources within the PowerBI Model. Any guidance would be appreciated.
Unless you are experienced with M:M I would avoid them and specially if you are trying to create self-service models.
So, either you add the date dimension in the model or you import the data into power bi (instead of DQ or Live). That is your best bet!
/Ruth
Thanks! Sorry, another question:
Are you aware of a limitation on the Manage Aggregations when you want to sum decimal numbers?
We are creating the Aggregated table in Power Query utilizing M and query folding. Everything was going great until we went to Manage Aggregations. We are not allowed to Sum our numeric columns. Going back into Power Query, it seems that when we used the Group By it converted Whole Numbers (123) to Decimal Number (1.2). If we try to Change Type we get a notification that step is not supported in Direct Query Mode and wants us to Import. Importing is not an option, we will not be able to set the table property to Dual.
We know we can do the aggregations on the SQL Server side, but we thought this would work as well and we wanted to compare the two. Do you have any insights into this?
Yes, happened to me too. This is what the docs say:
If the Query Editor query is overly complex, an error will occur. To remedy the error you must either delete the problematic step in Query Editor, or Import the data instead of using DirectQuery
I was changing a column type to date, so I wouldn’t call that complex.
What I think is happening is that when you make a transformation that breaks the query folding then it forces you to import. Group by will break query folding... test it.
/Ruth
Hi Ruth,
when we talking about big data, the big data table would always need to be direct query because it's too large for import or dual right? Direct Query should be available for all datasources right?
thanks and great video again ;)
Hi, exactly, when you have a lot of data, your only option is Direct Query.
If you have composite models, you can only use Import with Dual to avoid M2M, so either you convert everything to dual and import or you have dual and direct query. Import and direct query is a BIG NO-No.
Hope I explained myself?
/Ruth
Hi ruth can u plse share this database wid me . I downloaded the northwind database u were talkin about but it doesnt have ordersbig table and i kind of have to give a presentation on aggregations in power bi this monday.
Hi! My database is the Northwind database, I dont have a big order table I can share unfortunately!
/Ruth
It seems this feature is still not available for Power BI Report Server (Jan 2019)
You are probably right, report server is usually 6 months behind :(
/Ruth
Where did you get the write-over tool?
It is called epic pen. Really good :)
/Ruth
Don't understand why you need all these aggregations if you can make a separate table in Power Query with aggregated data (as you did) and use it when appropriate instead of Big table?
Nice features.... will see how I can use this for my models
Give them a go, it is worth it!
/Ruth
I dont see the table properties so I could set the storage properties? I'm using this verion 2.71.5523.941 64-bit (July, 2019). Any help?
You can not change the storage model on a Live connection, maybe that is your problem?
/Ruth
Hello, i like to see your videos and the way you explain topics. I have a question if Power Query can help. I have a list of names with different Start and End dates columns with different leave types. Dates may be in continuation but in different rows. i need Power query to club the dates as 1-20 March in one row and 23-26 march in another row as Annual leave and sick leave remains as is in a separate row. Possible?
Name Start Date End Date Leave Type
Mr. Smith 01-Mar-20 05-Mar-20 Annual Leave
Mr. Smith 06-Mar-20 09-Mar-20 Annual Leave
Mr. Smith 29-Mar-20 31-Mar-20 Sick Leave
Mr. Smith 10-Mar-20 20-Mar-20 Annual Leave
Mr. Smith 23-Mar-20 26-Mar-20 Annual Leave
result expected:
Mr. Smith 01-Mar-20 20-Mar-20 Annual Leave
Mr. Smith 23-Mar-20 26-Mar-20 Annual Leave
Mr. Smith 29-Mar-20 31-Mar-20 Sick Leave
Hi Ruth, I am working with large Data and this video helps in case case of aggregation, thanks for that. But the next level difficulty is how to drill through actual table after aggregation. Like I am having 10 billion rows and after aggregation, I can get summarized data very fast, but when I drill through detail table It take very long time. How can I get into detail data very fast?
You can’t, if you need to read the entire table, the read speed will be slow, unless you can aggregate that too.
/Ruth
Very well made video! Lot of questions answered and the way you explained is amazing.
Music to my ears!! Thanks for sharing :)
/Ruth
1000 thanks !!!
You welcome!!! :)
/Ruth
Again the sound is bad.
Is it? I have to go to the doctor!!
/Ruth