The best explanation so far regarding this awesome feature! I always like Alberto's and Marco's style to dig deeper into new stuffs and to analyze its strengths and weaknesses! Thanks a lot Alberto!
"Hurry up, don't change everything today but start learning because this is really going to change the way you look at your business models" :) Thanks for the video!
This is the killer feature. Sorry to bring up OBIEE again, but inability to standardize your organization's semantic model was a HUGE drawback of Power BI in comparison with the industrial BI solutions such as OBIEE and Cognos. THIS solves this problem. It is now that we can finally separate metadata stewardship to more qualified analysts and deevelopers, but keep the Power BI's unmatched front-end flexibility in hands of advanced users. Separation of duties is a big thing in large companies.
This come just as we got the go for attempting full coverage of our BI with SSAS, I'll be looking forward to the new best practices for tabular models to take advantage of it!
Thank you, this has really helped understand the potential and how to start to explore the best practise in using the new features. This is game changing and what so many businesses have been asking for.
Amazing! Really interested to implement this, having a full enterprise model managed by global IT teams and allowing local teams to extend the model as they need. Thanks a lot for this video with a very good explanation and demostration of this feature!
I would normally connect to DataFlows instead of DataSets precisely because this seemed the only way to add other tables and relationship (with the drawback of having to start with no base-model at all each time). This new feature does sound revolutionary! Then the question is: are there any use cases left for DataFlows?
Whenever you want to separate the transformation from the data model. For example, you can easily have a Product table share in many datasets. If the transformation is complex, you can keep it in a single place using a dataflow.
This is really huge, tried it since yesterday and really feeling amazed. After going through the documentation, came to know that 3 chain of model is the limit in preview. Now if we think of an organisation and multiple analysts creating their own model on top of enterprise bism, will it deviate from golden dataset!? And will the admin be able to control the publishing of these local models? Thank you for taking up this. So this brings up lots of questions related to data Island, weak and strong relationship and other existing modeling and DAX concepts (will it continue to behave same as existing). Curious to learn about this.. will look forward for things around DQ against semantic model.
We are just at the beginning and there are bugs and lessons to learn. Moreover, the feature is in preview, expect changes before its release. In general, we think that easy tasks like importing an Excel file, creating an additional calculated column and renaming a measure will not have particular issues. Creating mash up with two or more models and adding complex relationships between models, interacting with calculation groups,, managing security (not supported in this first preview)... well, that's another story. We need to try, study, and learn.
@@SQLBI Yes agree, this is just the beginning and lot more things will be coming out of the box. Many things to learn on the way. Appreciate it and welcome the New beginning.
@@SQLBI Thanks a lot Alberto! Can we immagine to create different models that can interact within each other in a modular way? What do you think about provide the user with simple models that he can compose in a scalable way? Does it overlap the dataflow concept?
Hi Alberto, Thanks for the excellent presentation on the new feature. What happens when business users need to reflect their data just updated in SQL (storage model) to power bi report? as you recommeneded semantic layer refreshing, power bi shared dataset can only be refreshed once in 30 mins unless done externally or you prefer to connect to analysis services and it connect to sql by direct query and power bi connects to it. please let me know your thoughts.
It really depends. You can refresh quicker with Power BI Premium, using DirectQuery has a big impact on performance and usually it is acceptable having some latency because of the refresh obtaining better scalability and response time. If you really need real-time data, you have to use DirectQuery, but you should be aware of the issues in data consistency in the report (if you run a report during a refresh, you can get different numbers in visuals that should provide the same measure in the same report, for example a line chart and a table). Real-time update is a tough business.
You can find free content (including videos) from this list: www.sqlbi.com/topics/composite-models/ And more content on SQLBI+: www.sqlbi.com/p/plus/ www.sqlbi.com/whitepapers/composite-models/
The composite model is great, but I cannot refresh the report as it has calculated tables and columns which seems to be a limitation when refreshing from powerbi service. Any help is appreciated.
Love this new feature, but it leaves me wondering a bit. Doesn't this pretty much make dataflows obsolete already? Why should I use a dataflow instead of this solution?
A dataflow split the business logic of a transformation from the dataset. It could be useful to reuse the same transformation logic in different models. A dataset is table+relationships+mesures, it's a more high-level concept. If you used dataflows as a way to share tables between datasets, yes, you used a temporary solution and this is better. However, you might end up having the need of reusing the same table (dimension?) in different datasets (connecting a dimension in one dataset to a fact table in another dataset is intuitively a bad idea, even though we have to practice to evaluate pros-cons). Thus, you might still want to use dataflows. Each tool is a just tool. It doesn't have to be used just because it exists, but when it's useful!
You need the latest version of Power BI Desktop and you have to click on "Make changes to this model" button in Modeling ribbon. If you don't see that button and you have Power BI Desktop December 2020, you are probably connected to a database that is not supported (only Power BI datasets and Azure Analysis Services databases are supported. Power BI Report Server is not supported).
This is a cool feature, does this create a brand new model on top of existing analysis model in Power BI. means it also needs to be refreshed frequently for any dataset (Excel, SQL server etc) added by user.
Now in April 2022, I can appreciate how powerful this is because we seem to be denied it with Live Connect to SSAS and it's positively disabling. I'm stuck with the model's design, even for adding simple slicer columns such as Price Range. Am I missing something? Is there some option I need to enable? (HELLLLPPP!)
Amazing!! I was awaiting a lot my end user will stop hear "We can't include that shitty local Excel file in our robust model enterprise model. Even that is important for you."
great video!!! I have a few questions... where is stored the data from excel? is stored in a new power bi dataset? or you have to upload the new composite model as a new dataset publishing it?
Thank you Alberto for this amazing demonstration! Off-topic: what is the model of the microphone you're using? I find it really convenient for recording screencast videos.
Hi Kay, I am still testing several ones. Because I have seven monitors in front of me, there is a lot of echo coming back and I need an very close mic. In this video I am using a PROEL condenser mic (HCM23SE) even though I am not really satisfied with the audio results. I plan to experiment with different headsets in the next weeks... I am in the process of learning the secrets of audio tuning! :)
There should be a limit on the number of indirections, it seems it is two or three by the first tests we made but it could change and we didn't run extensive tests yet.
@@SQLBI Thanks. I like the idea of being able to distribute load (and hence costs). If you have 3 servers making up a composite model you could make it so the more accessed parts of the model reside on the highest tier server and data that you are happy to wait for can be on the lowest cheaper tier. From the user experience point of view there is still only one connection point and it presents as massive integrated model that still performs where needed.
After you pull in your own data and it is local, then do you publish it back up to your tenant? I have seen a bunch of videos like this and its great that you can do this but they never explain what to do after you merge the data. From what I understand, you just keep it local..?
The local data stay local. You can publish the second model to your tenant, but also in that case you still have two separate databases (they could be in different tenants - assuming you have authorization to connect).
@@SQLBI - Is that common to do that? You would need to publish the model to a different tenant? What happens if you publish back into the main tenant the connected data set was from? Would that add the Excel file to that data set? Thanks!
Amazing Video Alberto! Learnt a lot from your videos as always. A question though, I couldn't figure out how the DAX of the ABC Amt RT calculated column works. Did a lot of research on the function but still couldn't figure out. Are you please able to explain a bit? or suggest any document I can read? thank you!!!
Thanks for this, i was looking for the part of the running totals with percentage that way a long time. Can you tell me how you can filter this? if i add a filter on a different column the filter does nothing, it only filters when adding a column to the filter which also contains the added calculated columns for the running total calculation.
Only Azure Analysis Services. Analysis Services on-premises is not supported. They could support in the future but deploying a big update to the on-premises version (perhaps, SQL vNext?).
It depends on the cardinality of the relationships between different data sources. If you use a remote dataset (not a DirectQuery connection to SQL) the performance is relatively good in that case. DirectQuery over a relational database was, is, and will continue to be much slower.
Thanks for this overview of this amazing new feature. I would like to only use 1 table from my cloud model. It is a calculated table that aggregates the „result“ of my cloud model in just the right granularity. Until now I just exported this table as an excel table using DAX Studio and importing it in my local model that works with this result. At first composite models seemed to be a much more elegant solution for this scenario. Testing it though I always have ti import the intire cloud model with all the table and all the complexity. This also crowds up my local model with a lot of tables that i do not need. Any advice on best practises here ?
Try the last version of Power BI Desktop: you can select the tables to include. Hint: create a local table first, then connect to the external model. At this point you can select the tables to import.
What version of power bi desk top are using, because it doesn't work with my. I am using analysis service live connect and I can't add anything else. Like importing excel
Hello, There may be a big catch! Let me explain myself : Today I always encounter a very annoying issue with PBI desktop when I use a table from a query (let's say Table1) to create other tables (Crossjoin or simply Table2=Table1). whenever you create a new column on Table2 you will find yourself with a "not enough memory" error on updating Table1 with more rows. I'm pretty sure when the model in the cloud will increase it's number of rows you should encounter the same problem obliging you to remove all the columns updating and putting back the columns again on each updates . Please let me know. Christian
As usual, you have to be aware of what is going on when you create composite models. Take a look at this other video: ua-cam.com/video/9lkVk4t2qL0/v-deo.html
Can we create a composite model making one dataset as live connection and another one as an imported table, but both the queries are fetching data from a single data source. For example Oracle database.
once you have created your model locally in desktop can you publish it to power bi server? Let's assume instead of having your budget dynamic table Ijust want to add a static table. But create a new model in power bi.
Great video Alberto. Any thoughts on how calculation groups would work when combining two datasets? Fx a main dataset with sales and calculations for time intelligence, combined with website data wanting the same time intelligence calculations
HOLY COW, I just built a report on TWO cloud datasets! It's slow, but it's working. Very promising feature! The bad things that will happen: all the display folders will be gone, naming conventions should be in place ( I still don't know what will happen in the naming conflict), and I couldn't find a way to disconnect from a dataset as they don't appear in the Power Query.
That's great news. But do you know if we can do the same thing with Multidimensional Cube? I tried and it seems still not working, maybe I miss something? Well, I hope so. Thanks!
Great! What happens if i connect 2 dataset with their own date table (to simplify with the same structure)? Remove one table and recreate the relationship or create a bidirectional relationship between the two?
It's too early to say. We are experimenting. There are several pros-cons in each scenario and there are so many combinations: - local copy of Date connected to the two Date tables in remote datasets - use one Date table and hide the other, using a single-direction filter - show both Date tables and create a one-to-one relationship (bidirectional) On top of these solutions, you have the options of the granularity of the relationship between datasets: Date, Month, Year? It depends, you have to balance granularity of analysis with performance. At the beginning, start with simple things, like adding a local table or a calculated column. Mash up of two or more remote datasets in unexplored territory, nobody has experience to define best practices.
Very informative. With this new feature (assuming it works as expected), does it mean that we don't need SSAS or even Azure Analysis services? We could take raw data (from ODS for example), build a virtual operational datamart, then load that data via import mode to Corporate Power BI Dataset (which might combine other datamarts) and make it available to business users? (especialy if one has Power BI Premium). Or would it make more sense to have a physical operational datamart and load it through direct query to Corporate Power BI dataset for further business usage? The last option doesn't make sense to me - but I might be missing things :) I am just trying to picture the enterprise data architecture with Power BI and ODS/Datamarts on MS SQL Server. Don't have plan to use AAS as plan to have PBI Premium.
It's still too early to say, but this tool is not a way to avoid ETL/data cleansing and a good architecture. You can certainly create reports integrating additional data, but creating complex mashups with multiple data sources is not a good idea. You would always pay a technical debt in data quality and performance.
Nice video, i tried to do the same, but because i am connected to a published powerbi report all my buttons to add data, and also Transform data are greyed out. How can i access does to do what you do and add an extended model?
First of all enable the preview feature, then create the local data model. Otherwise there could be something that disabled the feature at the tenant level, but it would be strange.
3 роки тому
Excellent video on an excellent new feature of Power BI, indeed! I am also grateful for all the tutorials you are creating and sharing. However, my question refers to the ABC classification example. I have a model where I have a product table (16 k rows) and another table with locations (cities) belonging to different countries, countries belonging to different business units (1 BU could be 1 country, but there are cases where 1 BU means 4 countries together). What is your advice to have ABC classification that would respect the filtering by country and/or by BU? If I add calculated columns to the product table as you suggested I would not get correct classification. Many thanks for the feedback!
I suggest you taking a look at the ABC Analysis pattern at daxpatterns.com. There, you'll find plenty of options. In your case, I think you will like the dynamic ABC, or the snapshot ABC. Both should work well in your scenario
3 роки тому+1
@@SQLBI Many thanks for the feedback and tip. It looks like the snapshot ABC would be best option for me so I would spend some time to read it carefully and implement it. Mille Grazie!
Excellent video... Just tried this with my model (using Dataflows). As soon as i enabled the DirectQuery i lost the date heirarchy in all of my date fields in the table. Does anyone know why this happened and can i fix this ?
It could be a bug or not - the feature is new and in preview, there are knew limitations published in the release doc and there are probably several bugs. However, if you are using auto date/time (as it seems from the description), you should stop it now! :) See ua-cam.com/video/Bzruqrj-wZg/v-deo.html
We use "new composite models" to remove the ambiguity with "DirectQuery for relational data sources". When you connect to a published dataset with a new composite model (officially "DirectQuery for Power BI dataset and Analysis Services") you can expect the same performance of Live connection, which is orders of magnitude better than "DirectQuery for relational data sources" (like DirectQuery for SQL Server).
Thank you so much Alberto - This is amazing - side question - SQLBI normally advise against using Many to Many, when you added the budget to the model and added the relationship, is there an alternative best practice or is this the time when it's acceptable to use Many to Many?
In this case it is acceptable. However, we strongly advice to avoid the bidirectional filter. The many-many cardinality relationship with a single direction filter is usually good as long as you are aware of the side effects you have on measures used below the cardinality of the relationship.
@@SQLBI Thank you, when you state below the cardinality of the relationship - I presume you mean further away from those tables - so if you have a fact table on your product and product budget, on one side of the model. The issue might start to appear if you want to look at the budget from another completely separate dimension table that holds not direct relationship to the product/product budget table?
by different granularity I mean that with a many-to-many cardinal it’s relationship at the product category level with the budget, you cannot split by product name the budget (but you can split the sales by product name).
@@SQLBI - I think I'm beginning to understand this point now. Is there any visual that I can look at to drive this home more simple. I feel this is very important point, and want to ensure I fully appreciate your comments
You can start here: www.sqlbi.com/articles/budget-and-other-data-at-different-granularities-in-powerpivot/ www.sqlbi.com/articles/working-below-a-dax-formulas-granularity/
With Direct Query when i publish it gives a warning saying calculated column may not work in Power bi Service, & Eventhough in Desktop its showing when we publish in service it does not show. What is the solution for it?
Live Connection allows to connect to a single data source. With new composite models, you can have multiple live connections within the same model (and report).
Thanks so much for the video, I watched it when it first came out....and now again for the 3rd time ! Yes indeed a game changer, I wonder what your opinion is now that we are 10 months in from the first launch...would be great to understand your learnings, pros and cons.
@@SQLBI when you copied the line in the switch function i noticed your cursor is on the last letter they you highlighted the whole line then pasted, is it shift+down arrow
@@SQLBI i mean i know is not possible to use time intelligence functions with DQ. My questions is: is it possible to use time intelligence functions with a model that has DQ and import mode?
You CAN use time intelligence functions with Direct Query (docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about). You just have to add a Date table to the model, which is a best practice for any model. See ua-cam.com/video/Bzruqrj-wZg/v-deo.html and ua-cam.com/video/xu3uDEHtCrg/v-deo.html
You need the latest version of Power BI Desktop and you have to click on "Make changes to this model" button in Modeling ribbon. If you don't see that button and you have Power BI Desktop December 2020, you are probably connected to a database that is not supported (only Power BI datasets and Azure Analysis Services databases are supported. Power BI Report Server is not supported).
my concern is that calculate column is quite heavy if your data table is over 1 million line , the function is cool but not so workable for real big data senario.
A column True/False is not that big, its cost in memory and processing time is moderate. I would be careful with 1 billion rows (or at least 100 million rows), definitely not with 1 million rows.
Hi Alberto, am I dreaming or is this feature not available any more in Power BI Desktop? I cannot find the any button to generate the local model (Direct Query) in a Power BI live connection ... thank you for your help.
Your emphasis on this feature being historical just made me more excited to watch the rest of your video. Thanks for that, Alberto.
It is not so historical . Strategy companion analyzer already has this "recombinant bi" feature since 2013 which also works on multidimensional models
The best explanation so far regarding this awesome feature! I always like Alberto's and Marco's style to dig deeper into new stuffs and to analyze its strengths and weaknesses! Thanks a lot Alberto!
"Hurry up, don't change everything today but start learning because this is really going to change the way you look at your business models" :)
Thanks for the video!
For me, this provides the best clarity to this highly-anticipated improvement to Power BI. Thank you.
Glad I woke up early. At 5:37 good tip on creating sort column with DAX.
This is the killer feature. Sorry to bring up OBIEE again, but inability to standardize your organization's semantic model was a HUGE drawback of Power BI in comparison with the industrial BI solutions such as OBIEE and Cognos. THIS solves this problem. It is now that we can finally separate metadata stewardship to more qualified analysts and deevelopers, but keep the Power BI's unmatched front-end flexibility in hands of advanced users. Separation of duties is a big thing in large companies.
This come just as we got the go for attempting full coverage of our BI with SSAS, I'll be looking forward to the new best practices for tabular models to take advantage of it!
As always your manner of how you explains are incredibly clear. So excited on how and where apply this
Always enjoy watching your videos, thank you for the demo! :)
Thank you, this has really helped understand the potential and how to start to explore the best practise in using the new features. This is game changing and what so many businesses have been asking for.
Great step by step on ABC analysis!
this guy is a solid unit of eloquence and presentation skills. you are a master on your craft sir.
Amazing! Really interested to implement this, having a full enterprise model managed by global IT teams and allowing local teams to extend the model as they need. Thanks a lot for this video with a very good explanation and demostration of this feature!
Amazing SQLBI Team!!! Thank you for sharing all these videos!!!
It is great to see you ENJOYING DAX!
How your explanation this much smooth and simple. thank you Alberto.
This is just mind blowing. Simply by using this feature my model size reduced from 185 MB to 4MB
I want to watch all of the man's videos.
I would normally connect to DataFlows instead of DataSets precisely because this seemed the only way to add other tables and relationship (with the drawback of having to start with no base-model at all each time). This new feature does sound revolutionary!
Then the question is: are there any use cases left for DataFlows?
Whenever you want to separate the transformation from the data model. For example, you can easily have a Product table share in many datasets. If the transformation is complex, you can keep it in a single place using a dataflow.
Enjoy Power BI... Really very well detailed explanation.. thank you so much...
Thankyou so much for the explanation. It is very useful and now I can use my semantic layer datamodel with local model. kudos
Grande Alberto! Great news from MS and great video. Moreover your public speaking skills are improving at best level. TOP!
We are waiting this for a long time. That’s really awesome Alberto. Thank you for helping us understand and study this feature.
This is really huge, tried it since yesterday and really feeling amazed.
After going through the documentation, came to know that 3 chain of model is the limit in preview.
Now if we think of an organisation and multiple analysts creating their own model on top of enterprise bism, will it deviate from golden dataset!?
And will the admin be able to control the publishing of these local models?
Thank you for taking up this. So this brings up lots of questions related to data Island, weak and strong relationship and other existing modeling and DAX concepts (will it continue to behave same as existing). Curious to learn about this.. will look forward for things around DQ against semantic model.
We are just at the beginning and there are bugs and lessons to learn. Moreover, the feature is in preview, expect changes before its release.
In general, we think that easy tasks like importing an Excel file, creating an additional calculated column and renaming a measure will not have particular issues. Creating mash up with two or more models and adding complex relationships between models, interacting with calculation groups,, managing security (not supported in this first preview)... well, that's another story. We need to try, study, and learn.
@@SQLBI Yes agree, this is just the beginning and lot more things will be coming out of the box. Many things to learn on the way. Appreciate it and welcome the New beginning.
@@SQLBI Thanks a lot Alberto! Can we immagine to create different models that can interact within each other in a modular way? What do you think about provide the user with simple models that he can compose in a scalable way? Does it overlap the dataflow concept?
Amazing video. Very clear explanation. Grazie mille !!
First time I hear from you "Not interested in Performance " 😂
Well well well... I said "I am not interested in performance" :) Actually, testing performance is kind of hard in this scenario... I am working on it!
@@SQLBI yep ur right 🙂
Great video! Is there any chance to get a video about connecting to datasets from different workspaces and the data gateway settings? Thanks a lot
Hi Alberto, Thanks for the excellent presentation on the new feature. What happens when business users need to reflect their data just updated in SQL (storage model) to power bi report? as you recommeneded semantic layer refreshing, power bi shared dataset can only be refreshed once in 30 mins unless done externally or you prefer to connect to analysis services and it connect to sql by direct query and power bi connects to it. please let me know your thoughts.
It really depends. You can refresh quicker with Power BI Premium, using DirectQuery has a big impact on performance and usually it is acceptable having some latency because of the refresh obtaining better scalability and response time. If you really need real-time data, you have to use DirectQuery, but you should be aware of the issues in data consistency in the report (if you run a report during a refresh, you can get different numbers in visuals that should provide the same measure in the same report, for example a line chart and a table). Real-time update is a tough business.
Awesome!
This is what I'm waiting, this is what all my end users are looking for.
I love this huge update.
Thank you Alberto for this video.
After 2 years, would be great to get an update on this topic ;)
You can find free content (including videos) from this list:
www.sqlbi.com/topics/composite-models/
And more content on SQLBI+:
www.sqlbi.com/p/plus/
www.sqlbi.com/whitepapers/composite-models/
Grazie mille for the excellent explanation!
The composite model is great, but I cannot refresh the report as it has calculated tables and columns which seems to be a limitation when refreshing from powerbi service. Any help is appreciated.
Great video, Is it possible to apply RLS when using direct query on analysis server using a calculated column?
Yes, it is supported, but the security is not fully implemented as of today (April 2021) - it should be fine at GA.
Love this new feature, but it leaves me wondering a bit. Doesn't this pretty much make dataflows obsolete already? Why should I use a dataflow instead of this solution?
A dataflow split the business logic of a transformation from the dataset. It could be useful to reuse the same transformation logic in different models. A dataset is table+relationships+mesures, it's a more high-level concept. If you used dataflows as a way to share tables between datasets, yes, you used a temporary solution and this is better. However, you might end up having the need of reusing the same table (dimension?) in different datasets (connecting a dimension in one dataset to a fact table in another dataset is intuitively a bad idea, even though we have to practice to evaluate pros-cons). Thus, you might still want to use dataflows.
Each tool is a just tool. It doesn't have to be used just because it exists, but when it's useful!
Does this mean we can connect multiple dataset in one report?
Yes!
@@SQLBI awesome. funny as of today I could not see that capability when I clicked on transform only Datasource setting is enabled.
You need the latest version of Power BI Desktop and you have to click on "Make changes to this model" button in Modeling ribbon. If you don't see that button and you have Power BI Desktop December 2020, you are probably connected to a database that is not supported (only Power BI datasets and Azure Analysis Services databases are supported. Power BI Report Server is not supported).
Just a BIG WOOOOW.....Reusability, conformed Fact feature on enterprise level is almost there. Very excited!!
This is a cool feature, does this create a brand new model on top of existing analysis model in Power BI. means it also needs to be refreshed frequently for any dataset (Excel, SQL server etc) added by user.
The new model links the existing one. You need to refresh it only if you *also* import other tables, otherwise no refresh is required.
OMG... I love your videos. Just now i can see the importance of this model. Thank very much for this video.
Now in April 2022, I can appreciate how powerful this is because we seem to be denied it with Live Connect to SSAS and it's positively disabling. I'm stuck with the model's design, even for adding simple slicer columns such as Price Range. Am I missing something? Is there some option I need to enable? (HELLLLPPP!)
Amazing!! I was awaiting a lot my end user will stop hear "We can't include that shitty local Excel file in our robust model enterprise model. Even that is important for you."
Sempre top! #vivadax
great video!!!
I have a few questions...
where is stored the data from excel? is stored in a new power bi dataset? or you have to upload the new composite model as a new dataset publishing it?
This is a great video!
Please have more videos like this. If you could do in regards to DAX as well :)
Thanks so much!
Thats a Game changing feature is it possible for SAP HANA Direct Query as well or is it only for Analysis for services?
Only Power BI datasets and Azure Analysis Services databases. But you can publish and connect to a Power BI Dataset connected to SAP HANA, of course!
Amazing feature from MS and wonderful explanation by my friend 👏
Tante grazie, Alberto!
Awesome! Just one thing, what All() does? It remove the filters that are being applying automatically?
ALL is like REMOVEFILTERS; more details here: www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/
Thank you Alberto for this amazing demonstration!
Off-topic: what is the model of the microphone you're using? I find it really convenient for recording screencast videos.
Hi Kay, I am still testing several ones. Because I have seven monitors in front of me, there is a lot of echo coming back and I need an very close mic. In this video I am using a PROEL condenser mic (HCM23SE) even though I am not really satisfied with the audio results. I plan to experiment with different headsets in the next weeks... I am in the process of learning the secrets of audio tuning! :)
Sounds like they took a look at Apache Superset. That or Quicksite. Also
Nice explanation, thank you. What if you publish this model, can you connect a composite model to it and then publish that and on and on and on...
There should be a limit on the number of indirections, it seems it is two or three by the first tests we made but it could change and we didn't run extensive tests yet.
@@SQLBI Thanks. I like the idea of being able to distribute load (and hence costs). If you have 3 servers making up a composite model you could make it so the more accessed parts of the model reside on the highest tier server and data that you are happy to wait for can be on the lowest cheaper tier. From the user experience point of view there is still only one connection point and it presents as massive integrated model that still performs where needed.
Are we able to mix a table created using the direct query method in a pbix that references a data model in SSAS?
SSAS is not supported yet, only Power BI datasets and Azure Analysis Services.
hi what about combining two sap sources both through Directquery?
After you pull in your own data and it is local, then do you publish it back up to your tenant? I have seen a bunch of videos like this and its great that you can do this but they never explain what to do after you merge the data. From what I understand, you just keep it local..?
The local data stay local. You can publish the second model to your tenant, but also in that case you still have two separate databases (they could be in different tenants - assuming you have authorization to connect).
@@SQLBI - Is that common to do that? You would need to publish the model to a different tenant? What happens if you publish back into the main tenant the connected data set was from? Would that add the Excel file to that data set? Thanks!
more power for self service, good news for analysts
Amazing Video Alberto! Learnt a lot from your videos as always. A question though, I couldn't figure out how the DAX of the ABC Amt RT calculated column works. Did a lot of research on the function but still couldn't figure out. Are you please able to explain a bit? or suggest any document I can read? thank you!!!
See these patterns:
www.daxpatterns.com/cumulative-total/
www.daxpatterns.com/abc-classification/
Thanks for this, i was looking for the part of the running totals with percentage that way a long time. Can you tell me how you can filter this? if i add a filter on a different column the filter does nothing, it only filters when adding a column to the filter which also contains the added calculated columns for the running total calculation.
You might want to read these:
www.sqlbi.com/articles/computing-running-totals-in-dax/
www.daxpatterns.com/cumulative-total/
Thank you Alberto for your great demo. Do you know whether it will support connection between multiple Analysis service by any chance?
Only Azure Analysis Services. Analysis Services on-premises is not supported. They could support in the future but deploying a big update to the on-premises version (perhaps, SQL vNext?).
Unfortunately the performance is really low when using dax between import tables and direct query tables
It depends on the cardinality of the relationships between different data sources. If you use a remote dataset (not a DirectQuery connection to SQL) the performance is relatively good in that case. DirectQuery over a relational database was, is, and will continue to be much slower.
Thanks for this overview of this amazing new feature. I would like to only use 1 table from my cloud model. It is a calculated table that aggregates the „result“ of my cloud model in just the right granularity. Until now I just exported this table as an excel table using DAX Studio and importing it in my local model that works with this result.
At first composite models seemed to be a much more elegant solution for this scenario. Testing it though I always have ti import the intire cloud model with all the table and all the complexity. This also crowds up my local model with a lot of tables that i do not need.
Any advice on best practises here ?
Try the last version of Power BI Desktop: you can select the tables to include. Hint: create a local table first, then connect to the external model. At this point you can select the tables to import.
Super Interessante! Grazie Alberto!
What version of power bi desk top are using, because it doesn't work with my. I am using analysis service live connect and I can't add anything else. Like importing excel
SQL Server Analysis Services is not supported, you should connect to Azure Analysis Services or Power BI Dataset.
Hello, There may be a big catch! Let me explain myself : Today I always encounter a very annoying issue with PBI desktop when I use a table from a query (let's say Table1) to create other tables (Crossjoin or simply Table2=Table1). whenever you create a new column on Table2 you will find yourself with a "not enough memory" error on updating Table1 with more rows. I'm pretty sure when the model in the cloud will increase it's number of rows you should encounter the same problem obliging you to remove all the columns updating and putting back the columns again on each updates . Please let me know. Christian
As usual, you have to be aware of what is going on when you create composite models. Take a look at this other video: ua-cam.com/video/9lkVk4t2qL0/v-deo.html
Can we create a composite model making one dataset as live connection and another one as an imported table, but both the queries are fetching data from a single data source. For example Oracle database.
Yes it should work.
This is huge!
once you have created your model locally in desktop can you publish it to power bi server? Let's assume instead of having your budget dynamic table Ijust want to add a static table. But create a new model in power bi.
Yes you can publish it.
Wonderful explanation. Greetings and thank you very much
Great video Alberto. Any thoughts on how calculation groups would work when combining two datasets? Fx a main dataset with sales and calculations for time intelligence, combined with website data wanting the same time intelligence calculations
Unfortunately... it depends! It's one of the many topic we have to cover in future articles and videos (and we don't have the full answer yet!).
Alberto... as with any new features... I just wonder what the TRAPS are :))) But at least your work is guaranteed and new books as well :)))
Very good tips Alberto, Thanks a lot. Just one question. Can we join datasets with this feature? Lat's play with that.
The notion of "join" in DAX is ambiguous... yes, you can create relationships between tables coming from different datasets if this was the question.
HOLY COW, I just built a report on TWO cloud datasets! It's slow, but it's working. Very promising feature!
The bad things that will happen: all the display folders will be gone, naming conventions should be in place ( I still don't know what will happen in the naming conflict), and I couldn't find a way to disconnect from a dataset as they don't appear in the Power Query.
This is the first preview, there are a lot of bugs to fix... Wait before going in production, but you can already see the potential!
Awesome🎉🎉
That's great news. But do you know if we can do the same thing with Multidimensional Cube? I tried and it seems still not working, maybe I miss something? Well, I hope so. Thanks!
No, Analysis Services on-premises is not supported, and AS Multidimensional will not be supported in this scenario, as far as we know.
@@SQLBI Thanks Alberto and Marco for huge work you have done! You are my idols in PBI world! :)
Whoa, that's awesome total shift
Excellent. Thank you.
Is it possible to use the composite models in the Power BI report server?
Great! What happens if i connect 2 dataset with their own date table (to simplify with the same structure)? Remove one table and recreate the relationship or create a bidirectional relationship between the two?
It's too early to say. We are experimenting. There are several pros-cons in each scenario and there are so many combinations:
- local copy of Date connected to the two Date tables in remote datasets
- use one Date table and hide the other, using a single-direction filter
- show both Date tables and create a one-to-one relationship (bidirectional)
On top of these solutions, you have the options of the granularity of the relationship between datasets: Date, Month, Year? It depends, you have to balance granularity of analysis with performance.
At the beginning, start with simple things, like adding a local table or a calculated column. Mash up of two or more remote datasets in unexplored territory, nobody has experience to define best practices.
@@SQLBI as expected, thank you!
Very informative. With this new feature (assuming it works as expected), does it mean that we don't need SSAS or even Azure Analysis services? We could take raw data (from ODS for example), build a virtual operational datamart, then load that data via import mode to Corporate Power BI Dataset (which might combine other datamarts) and make it available to business users? (especialy if one has Power BI Premium). Or would it make more sense to have a physical operational datamart and load it through direct query to Corporate Power BI dataset for further business usage? The last option doesn't make sense to me - but I might be missing things :) I am just trying to picture the enterprise data architecture with Power BI and ODS/Datamarts on MS SQL Server. Don't have plan to use AAS as plan to have PBI Premium.
It's still too early to say, but this tool is not a way to avoid ETL/data cleansing and a good architecture. You can certainly create reports integrating additional data, but creating complex mashups with multiple data sources is not a good idea. You would always pay a technical debt in data quality and performance.
This doesn't seem to work with cloud datasets in workspaces without dedicated capacity/power bi premium. Can you confirm this?
sorry, I forgot to enable the preview feature in options. After doing this, it works against a dataset in a non-premium workspace.
Bravo! Bravíssimo, Alberto!!!!! I'm so exciting too...thank you and Marco!
Nice video, i tried to do the same, but because i am connected to a published powerbi report all my buttons to add data, and also Transform data are greyed out. How can i access does to do what you do and add an extended model?
First of all enable the preview feature, then create the local data model. Otherwise there could be something that disabled the feature at the tenant level, but it would be strange.
Excellent video on an excellent new feature of Power BI, indeed! I am also grateful for all the tutorials you are creating and sharing. However, my question refers to the ABC classification example. I have a model where I have a product table (16 k rows) and another table with locations (cities) belonging to different countries, countries belonging to different business units (1 BU could be 1 country, but there are cases where 1 BU means 4 countries together). What is your advice to have ABC classification that would respect the filtering by country and/or by BU? If I add calculated columns to the product table as you suggested I would not get correct classification. Many thanks for the feedback!
I suggest you taking a look at the ABC Analysis pattern at daxpatterns.com. There, you'll find plenty of options. In your case, I think you will like the dynamic ABC, or the snapshot ABC. Both should work well in your scenario
@@SQLBI Many thanks for the feedback and tip. It looks like the snapshot ABC would be best option for me so I would spend some time to read it carefully and implement it. Mille Grazie!
Excellent video... Just tried this with my model (using Dataflows). As soon as i enabled the DirectQuery i lost the date heirarchy in all of my date fields in the table. Does anyone know why this happened and can i fix this ?
It could be a bug or not - the feature is new and in preview, there are knew limitations published in the release doc and there are probably several bugs. However, if you are using auto date/time (as it seems from the description), you should stop it now! :) See ua-cam.com/video/Bzruqrj-wZg/v-deo.html
What is the performance difference when connecting to a model as a live connection vs Direct Query?
We use "new composite models" to remove the ambiguity with "DirectQuery for relational data sources". When you connect to a published dataset with a new composite model (officially "DirectQuery for Power BI dataset and Analysis Services") you can expect the same performance of Live connection, which is orders of magnitude better than "DirectQuery for relational data sources" (like DirectQuery for SQL Server).
I have SSAS and trying to connect as Directquery source but it is not currently supported 😕☹️☹️. I am not able to add local data model
Local SSAS data model cannot be supported - that will require a new SSAS version, assuming Microsoft will release one (they didn't announce it yet).
Break through indeed
That is mind blowing! Grazie mille!
Thank you so much Alberto - This is amazing - side question - SQLBI normally advise against using Many to Many, when you added the budget to the model and added the relationship, is there an alternative best practice or is this the time when it's acceptable to use Many to Many?
In this case it is acceptable. However, we strongly advice to avoid the bidirectional filter. The many-many cardinality relationship with a single direction filter is usually good as long as you are aware of the side effects you have on measures used below the cardinality of the relationship.
@@SQLBI Thank you, when you state below the cardinality of the relationship - I presume you mean further away from those tables - so if you have a fact table on your product and product budget, on one side of the model. The issue might start to appear if you want to look at the budget from another completely separate dimension table that holds not direct relationship to the product/product budget table?
by different granularity I mean that with a many-to-many cardinal it’s relationship at the product category level with the budget, you cannot split by product name the budget (but you can split the sales by product name).
@@SQLBI - I think I'm beginning to understand this point now. Is there any visual that I can look at to drive this home more simple. I feel this is very important point, and want to ensure I fully appreciate your comments
You can start here:
www.sqlbi.com/articles/budget-and-other-data-at-different-granularities-in-powerpivot/
www.sqlbi.com/articles/working-below-a-dax-formulas-granularity/
With Direct Query when i publish it gives a warning saying calculated column may not work in Power bi Service, & Eventhough in Desktop its showing when we publish in service it does not show. What is the solution for it?
It's a current limitation of the product.
Dont you mean Direct Query with SSAS = Live Connection?
Live Connection allows to connect to a single data source. With new composite models, you can have multiple live connections within the same model (and report).
Great! I've been waiting for this for years )
Thank you much
Thanks so much for the video, I watched it when it first came out....and now again for the 3rd time ! Yes indeed a game changer, I wonder what your opinion is now that we are 10 months in from the first launch...would be great to understand your learnings, pros and cons.
hi alberto, whats the shortcut you use to select the whole line while you write the DAX?
If there is one, then I don't know it :) I just use SHIFT-ARROW DOWN at the beginning of a line to extend the selection to the next line.
@@SQLBI when you copied the line in the switch function i noticed your cursor is on the last letter they you highlighted the whole line then pasted, is it shift+down arrow
Thank you so much for this amazing video. I would like to ask if it is now possible to create dax intelligence functions with this composite models.
It was already possible to use time intelligence functions in other models - can you elaborate?
@@SQLBI i mean i know is not possible to use time intelligence functions with DQ. My questions is: is it possible to use time intelligence functions with a model that has DQ and import mode?
You CAN use time intelligence functions with Direct Query (docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about).
You just have to add a Date table to the model, which is a best practice for any model.
See ua-cam.com/video/Bzruqrj-wZg/v-deo.html and ua-cam.com/video/xu3uDEHtCrg/v-deo.html
Amazing!
I didn't work with me. Is it available with every type of datasets?
You need the latest version of Power BI Desktop and you have to click on "Make changes to this model" button in Modeling ribbon. If you don't see that button and you have Power BI Desktop December 2020, you are probably connected to a database that is not supported (only Power BI datasets and Azure Analysis Services databases are supported. Power BI Report Server is not supported).
my concern is that calculate column is quite heavy if your data table is over 1 million line , the function is cool but not so workable for real big data senario.
A column True/False is not that big, its cost in memory and processing time is moderate. I would be careful with 1 billion rows (or at least 100 million rows), definitely not with 1 million rows.
This is a great feature. Que bueno.
WOW!!!! thanks Alberto!!! just what I needed!!! thanks so much for you grat videos
Hi Alberto, am I dreaming or is this feature not available any more in Power BI Desktop? I cannot find the any button to generate the local model (Direct Query) in a Power BI live connection ... thank you for your help.
You should check that the feature is enabled, it's a preview feature in Power BI, go in Power BI Options and enable it first.
This is embarrassing (I checked it but obviously I oversaw the feature) - thank you so much :)