Analysis Services What is the Difference between Tabular and Multidimensional Models
HTML-код
- Опубликовано: 28 июл 2024
- In Microsoft Analysis Services there are two model types: the Multidimensional and the Tabular. In this video, we'll explain what the difference is and which should you choose?
Наука
I came back to this video once more. Never found a better intuitive explanation for a Cube.
Thanks
finally, someone who speaks proper English
Video was made 5 years ago, yet still relevant. Your explanation of MDX OLAP as a series of pre-calculated group by queries saved on disk is brilliant.
Bryan the way you explain things is awesome. I am one of those “dinosaurs” and your lessons have really kickstarted my journey back. Thx
Good stuff! I started to use mOLAP since 2005 and rOLAP since 2015. But this is one of the best explanations on these concepts I've ever watched. Thanks Bryan!
Excellent video, just the straight forward summary I was looking for!
Seriously this was the best most concise talk on the subject I listened to. Raised pertinent questions and the answers were on the point. Brilliant.
Thanks.
Thanks
So clean explantation !! Bravo, really so clear and makes you perfectly get the difference between Multidim & Tabular models ! can't believe i didn't come accross this beafore ! Thank you !
The best practical explanation I got so far about multidimensional models!
Probably one of the best videos i've watched on a deep and technical topic. I'm a complete noob when it comes to BI and it's been daunting to say the least, but I've answered so many questions for myself with this 30 minutes that has saved me hours of running around the net to figure out the answers. Thank you
Glad it helped! Thanks.
You are a great example of someone who is really understand the topic. Your ability to clarify and give simple examples helped me a lot!
Thank you once again
Thanks!
Exactly what I was looking for. Thank you!
Brilliant video explained very clearly, using great analogies :) Really appreciate it, thanks!
Good stuff, you made it very clear the differences between the two. I'm transitioning from an IT support career to SQL and database management and I will be watching more of your videos. BTW it's good to hear a Northern accent, I moved from New England to the mid-South a few years back. When I move back I'll be joining your RI user groups.
Thanks. I look forward to meeting you when you come back to NE.
I have used cubes since SQL Server 2000 and this is the best explanation of the two models I have seen. Great work. The book mentioned by Marco Russo and Alberto Ferrari is a great way to go. They have been key to BI for over a decade.
Thanks!
I was just looking for this, thanks for the clear explanation!
Great explanation - thank you!
All clarification is really crystal clear and thanks a lot for an awesome video.
By far, the best video I've seen on the subject
Great Video! much better than most of the tutorials on this topic.
Awesome intro and intuitive explanation
Excellent video. There's no nonsense. Straight to the point with great examples.
Thank you.
very clear explanation and really helpful. I found it is very important to understand theses basic concepts and the relationships among them. Thanks for your great job!
Thanks!
Excellent explanation!! Thank you!
Great explanation. Thank you Bryan!
Awesome explanation, thank you.
Bryan, Very helpful in tying together the pieces for me. I use SSIS, SSRS, and Power BI--very familiar with the tabular model--but can now take it to the level of an enterprise tabular model. Have previous experience with the SSAS multi-dimensional model--but really needed to get up-to-date on SSAS with the tabular model. Will check out some of your other videos. Keep up the great work!
Thanks. Glad they are helpful!
Bryan, this was very helpful, thank you!
Great explanation - thanks
Very helpful - thank you!
thank you. Best video so far for explaining the difference. Can you also post a video where we can join multiple fact tables via Link Table ?
what a great explanation. highly appriaciate.
Thank you. Very nicely presented.
Awesome video. Thanks much.
Thanks million @Bryan Cafferky awesome video, perfect explanation.
Thanks!
Good summary. Thank you.
Thank you Bryan for this great video so as a beginner, I will focus on studying the Tabular instead. GBU.
Very nice and informative presentations. I learned a lot. Thanks.
Thanks.
Nice Explanation
excellent presentation, very helpful
Thanks Steven!
Please consider writing a a book. You have solid practical experience.
I wanna give you a hug. I love how you express the concepts. Definitely subscribing and will watch all your videos!!!
Thanks Steven. Glad to hear they are helpful to you!
Hi Bryan, your videos are awesome. I have learned so much. Do you have any detail or videos on creating the dimensions and fact tables. Maybe an example. Taking data from the staging tables and making the fact tables. We are using ssis
Thank you!
9:20 important concept, cubes, dms and dws
Thanks for the video. Much appreciated
YW! Please share my channel with others in your network.
@@BryanCafferky bought your book😉
thank you Bryan, you make everything clear and easy to consume for the brain :)
Thanks. Glad it helps.
@@BryanCafferky thank you very much Bryan.
i have a business case and i might need your lights, may i ask you a quick advise ? thanks a million in advance.
@@luckyprod9013 Sure. Np.
@@BryanCafferky Thank you Bryan :)
Look, i'm a 15+ years Financial Planning & Analysis manager working for different small businesses now after years in the high tech industry.
i would love to build a portal where my client could log into and get access to their reports and other market data that i provide.
i've been an advanced BI user for decades but now i feel it's my turn to build lights BI environments for my clients.
As you know (better than me) there is a zillions way to do what i want to do, and i've been searching for the last 2 weeks the most effective way to proceed for me (as a non dev/coder person).
Now my feeling is that i want to go 100% azure for the sack of simplicity + all the products available.
i think of my production pipeline as of :
datafactory for data integration
sql server for data storage
SSAS + power Bi for data rendering
wordpress for publishing
2 questions for your :
1 technical : shall i use active directory for client authentication and can i use that with wordpress and if yes, is it a good strategy ?
2 feedback : does the here above pipeline sound ok for you or would you have any feedback on a smarter way to proceed ?
thanks a million and have a good sunday in new england :) :)
@@luckyprod9013 Without knowing the details, overall the tools/services sound like good ones. Active Directory is great to secure things. You can use Azure SQL DB and Power BI which work well together. Power BI Premium offers nice scaling and is expanding to include Analysis Services Tabular Model. You can also use Azure Analysis Services. Not sure if you are thinking of a hybrid or full cloud architecture but all Azure makes maintenance easier. Not sure about Wordpress. What would Wordpress be used for?
Bryan great stuff. Do you think with the new features of Columnstore Indexes in 2016 and Sql Server VNext, that you can get great performance right on the star schema itself? With the right Clustered Columnstore and Non-Columstore index combos on the Star Schema along with Materialized views, I would think you could almost do away with OLAP. Is this all becoming semantics now on which way you do it? Great stuff!
Hi Jack, Thanks for the feedback and glad you like it. Columnstore can be a great way to get great performance on the OLTP instance. However, Analysis Services wraps up a lot of source to model build services, i.e. via VS drag and drop, transform, etc. to build the tabular model. Also, SSAS/AAS has a lot of extra features like perspectives, row level security, and support for languages like DAX. I also like the idea of separating the data analytics data model from the SQL Server database instance so performance and resources can be managed separately. Tabular models (and in memory/Columnstore tables( take up a lot of memory and can have a lot of query demands. Azure Analysis Services lets you create multiple query replicas, i.e VMs, to balance load these demands. People won't wait abymore, they want lightning fast response.
@@BryanCafferky I would think that the narrow fact table and its sparsity would be a great candidate for the Columnstore Clustered index available in 2016 along with the ability to add traditional non-clustered indexes to the Clustered Columnstore index. Queries analyzing millions of rows are coming back in sub-seconds --> ruclips.net/video/CeaEiPX2_QE/видео.html
like your other videos, it is 36 mins of pure GOLD!
but i have a question.. a multi dimensional cube is a part of OLAP, is a Tabular Model also a part of OLAP? Also can Power BI consume multi dimensional cubes? if so, how can it, especially since Power BI's analysis service component is based on Tabular model?
Thanks. The multi-dimensional model is an older way to support quick visualizations and the Tabular Model is a newer, all in memory, approach. Tabular is becoming the standard. Yes. Power BI and even Tableau can use multidimensional cubes. If you import the data from a cube/save it, Power BI will store it as a tabular model. See this link: docs.microsoft.com/en-us/power-bi/desktop-ssas-multidimensional
HOLAP is the way to go. It's an art to tune, though. You have to look at both usage patterns and the structure of things like product hierarchies so you know what to calc and store and what to calc on demand. Just by getting rid of any stored sums from lightly-queried parents with less than 5 children can cut cube calc times by magnitudes. I've personally cut the calc time off an Essbase cube for a large utility company from 18 to 2 hours by going through their outline with a fine-toothed comb and turning stored junk to dynamic.
Solid video, thanks! Also, has anyone ever told you that you look like Marc Maron when you're wearing that baseball cap?
Or does Marc Maron look like me? :-) Thanks
Thank you Bryan. you're the best instructor. I've learned many things from you. i have a question. is mdx used in tabular models? for someone who wants to work with tabular models what technologies you recommend to learn? ( i already work with power bi but I want to expand my knowledge in tabular)
MDX can be used with tabular models but DAX is the language for tabular models. I am a bit biased on what to focus on but I like to focus on architecture. Things worth knowing to distinguish yourself from others include DAX, performance optimization, when to use DirectQuery vs. Import mode, how to scale up with Premium Service and/or Azure Analysis Services. My video on this is at: ruclips.net/video/R7k4qJ120dY/видео.html
Also, I love the book by Marco Russo, 'Tabular Modeling in Microsoft SQL Server Analysis Services'. Very step by step format.
I try to learn what to do when things are not so simple. Because they are never simple. :-)
@@BryanCafferky Thank you very much. I will watch this video. I really want to thank you once again for all the great information you share here and for your help and recommendations :)
@@malihebayat2723 You're welcome. Glad it is helpful.
Hi there, I'm just looking to get an opinion on a potential approach to a solution I've been asked about, if you've the time :)
I've been developing Power BI models with Datawarehouses for a year or so now, holding the roles of DBA/Data Analyst/Report Builder along the way. During this time, I've developed tabular models with SSAS using these DW sources, and for these tabular models I've always used Direct Query. But I'm in a situation now where I may need to become involved in the development and use of a Multidimensional Model (not much experience here, but this video has really helped get my head around the concepts, thanks!). The question has been floated if the data aggregated in the Miltidimensional model can share a home in a Power BI model, with say an actual Datawarehouse/Data Hub.
The reason I ask is that while with Direct Query your hands are tied when editing or adding data sources the the Power BI model - but if you Import the data, those limits die off, and you are effectivly left with an in-memory tabular model using Power BI's 'lite' SSAS, with all of your Multidimensional data + the ability to add further
elements and data sources to the Power BI model.
I'm just curious about the implications of importing a Multidimensional Model to Power BI (beyond the memory constraints that would inevitably need to be weighed up), and wondering if you (or anyone :) )have an opinion ? Do you loose functionality or structure defined in the Miltidimensional model if you import it to Power BI locally?
Hi Ciaran,
That's quite a question and a good one. My consultant mindset kicks in when I see a question like that because I want to make sure the best solution is reached. In general, DirectQuery is something I would only use if really needed because you lose a lot on the performance side. A better option would be to build a tabular model in Azure Analysis Services because it scales well and has very high storage limits, up to 400 GB after compression which is a lot of data! You can even have multiple servers to support queries. These are called Query Replicas, which include load balancing so very high demand can be supported. With Azure Analysis Services you get click speed response with great scalability. See docs.microsoft.com/en-us/azure/analysis-services/analysis-services-overview for more information. If Azure is not an option, you can do SSAS Tabular models on premises.
For the MDX model, would it be possible to convert it into a tabular model and incorporate it into your tabular model solution as I described above? MDX was designed around constraints that no longer exist and I think is a lot more complex to maintain.
If you have Power BI Premium, you may be able to achieve everything within that service without using the separate Azure Analysis Services. I have not vetted the blog I that I am about to provide thoroughly but I like the comparison that Power BI Premium is Software as a Service and AAS is Platform as a Service. I like the additional control of AAS but for true end user BI, Power BI Premium can be a good option.
For information about when DirectQuery makes sense, see docs.microsoft.com/en-us/power-bi/desktop-directquery-about
Hope that helps.
@@BryanCafferky Thanks so much for your quick reply and details.
I'll definitely be taking these reccomendations on board - esspecially around the MDX model suggestion - it's along the lines of what i was thinking.
I've some more digging to do now! Thanks agian Bryan!
Hi Bryan, thanks very much for the super clear and well-structured lesson on this. One (rooky ;-) )question: At 22:36 you show a star schema model and distinguish this from snow-flaking however the product class table is connected solely to the product table and not directly to the fact table, so it this not a case of snowflaking?
Doh! Yes. You are correct. I must have thought I saw a line there connecting product class to the fact table. However, this confirms you understand the difference! Congratulations padawan! Good work.
@@BryanCafferky haha great! Thanks for the confirmation! 🤗
nice one. im an anlyst that dont know what im building..thought they are same, multid and tabular model. now have clearer understanding. side note: we are using both. multid in cognos and tabular in azure
got question on star schema. is this really the iron clad rule to stick with it? what if its required to do snowflake to allow specific behaviour on the model? would it impact much? how could we quantize it also?
There are no iron clad rules but when you go away from star schema, it should be done with careful consideration of why and understanding the consequences. See www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/snowflake-dimension/
This blog explains the differences but disagree with the author's treatment of both as equal.
www.xplenty.com/blog/snowflake-schemas-vs-star-schemas-what-are-they-and-how-are-they-different/#:~:text=Challenges%20of%20Snowflake%20Schemas&text=Complex%20data%20schemas%3A%20As%20you,more%20complicated%20source%20query%20joins.
Great Video, thanks! (Hey, is product - product class [22:55] snowflaking?)
Yes. It is but I think the line is hard to see. Thanks!
Indeed great explanation.
On a side note, I am willing to pay monthly subscription fee for same videos with closed captions
(not auto-generated subtitles).
I need to learn how to do that. Thanks.
@Bryab Cafferky you mentioned you have a video on dimensional modelling, can you please share the link on that?
Sure. Its ruclips.net/video/lWPiSZf7-uQ/видео.html
Had a question though:- What could be the reasons for a slow cube performance in Excel pivot tables compared to PowerBI reports?
Hi Deepu, Hard to say without a lot of details but Excel runs on your client and Power BI reports run on Azure. If the data is on Azure, especially if it's in Azure Analysis Services, it will have low latency and Power BI is really optimized (I think) to work with Analysis Services. Excel also has memory limits that may come into play (not sure).
Thanks for watching!
This video is super helpful so thank you for posting. Im a user of many of these tools but I’m trying to learn more about how they work. For example, I often write MDX queries in SSMS to pull from our cube. So I imagine that means our company has created a multidimensional model, bc it’s using MDX right? But I know that we use Azure Analysis Services so that means it has to be tabular… right?
I have some clarifying questions, I apologize this is so long but I just really want to learn the foundations correctly.
1) does Azure Analysis services support multidimensional models or only tabular like SSAS? (Related to above question)
2) isn’t the cube preaggregating all the combinations and loading them into a “table” in storage? So isn’t that the same as tabular loading all the aggregations into a table in memory? Furthermore, in powerBI, when I look at the relationship manager, the schema looks very similar to the star schema used in a multidimensional model, so how is it tabular but not multidimensional? I guess my question is do both models use the star schema?
3) When you create a model in PBI with relationships between different tables, have you essentially created a tabular model even if you are querying data from an OLTP relational database?
4) do we still use the words dim and fact in relational databases? And if not, what do we call the “points of the star” that come from a single transaction table in a regular relational database schema?
Thank you for your help, this video is great!
Thanks.
1) No. Azure Analysis Services only supports the tabular model
2) The Tabular Model does NOT pre-aggregate data. It does slicing/dicing and aggregation on the fly. The Multidimensional Model pre-aggregates all the possible permutations (generally) of the dimension values to the fact measures. The Multidimensional Model is read from disk, not held in memory which is why it had to use pre-aggregation. The Star Schema is a concept for Dimensional Modeling, i.e. data warehouse design. Power BI is a consumer of data and usually works better when the source is Dimensionally Modeled, i.e. a Star Schema. Power BI is a user of the data. Do not confuse it with a Data Warehouse.
3) Yes. Creating a model is Power is creating a Tabular Model. The sources of the data do not matter. Technically, the Tabular Model does not have to look like a Star Schema but usually does as they generally perform better.
4) do we still use the words dim and fact in relational databases? And if not, what do we call the “points of the star” that come from a single transaction table in a regular relational database schema?
4) No. In an OLTP database which is designed to optimize data maintenance, you do not call tables Facts and Dimensions. It should NOT look like a Star Schema. Descriptive and Quantitative values are usually stored on the same table. OLTP databases follow the Laws of Normalization and use Entity Relationship Modeling. See docs.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description
Data Warehouses often use Dimensional Modeling which focuses on fast data retrieval for reporting and separates the data into Facts and Dimensions. See Kimball Data Warehouse Design. www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/
Hope that helps.
Bryan
@@BryanCafferky thank you SO much for taking the time to answer all my questions. This helped clarify a lot.
I think I got confused at 28:06 when you mentioned a tabular model is like a table in memory- but it's not a preaggregated table, its only created once the slicing/dicing has been selected. Is it fair to say that dimensional modeling is essentially creating a "cube" (tabular cube in memory, or multidimensional in storage), which ingests a data warehouse but they are separate things? The cube is the output of dimensional modeling?
Regarding OLTP and normalization, lets say I have one table with all the sales transactions and it has a "customerID" field, and another table that is for customer information, isn't this the same IDEA as a star schema? Is the only main difference OLAP can handle redundancy (dimensional attributes and transactions in the same table, surrogate keys instead of primary/foreign keys) and OLTP is not redundant and completely normalized (connected by primary and foreign keys)? This link says its an ER diagram for OLTP, but to me it looks like several fact and dim tables: vertabelo.com/blog/vertabelo-tips-good-er-diagram-layout/
Thank you again, I appreciate your guidance and quick response!
@@riaverma8945 It's not about what a tool can handle. Modeling is about organizing the data optimally for HOW it will be used. Insets/Updates (OLTP) or reading/reporting Dimensional. If you look closely as the ER Model you usually see a mix of measures like sale amount on the same table as sales reason, sales date, and product. And the ER diagram has tables pointing to each other in many directions. A star schema has one fact table (measures) and dimension tables with only values that describe a transaction. Dimensional tables do NOT point to other Dimension tables, ie. airport -> flgiht _> manifest in your example should not be done in a Dimensional Model. I too was confused about this at first. one thing to remember, OLTP seeks to eliminate data redundancy while Dimensional Modeling seeks to increase redundancy (in the interest of easier reporting). A typical sales table OLTP might be sales (ID, SalesAmount, SalesDate, SalesType, RegionCode, StoreID, TaxAmount). For Dim Model , this would replace SalesDate with SalesDateKey (so it can use a DImDate table to get data attributes, StoreID might be replace with SalesDimension which includes RegionCode, RegionName, StoreName, StoreAddress, etc. No dimension table has a key on it to another dimension table, i.e. you would NOT have DimStore with a RegionID key pointing to a DimRegion table.
A Dim Model can get all data for a query by joining from the fact table DIRECTLY to its dimensions so no nested joins. When you violate this principal it is called Snowflaking which is considered a bad practice.
@@BryanCafferky Thank you again for being so thorough in your responses! You've really helped me to understand this concept better and how a "cube" is just another layer of a datawarehouse created through dimensional modeling.
Cannot recommend your videos & your teaching enough! I'll be sure to check out your other courses.
How do we calculate if our data sets will fit into the memory ?
I don't know of a formula to do that. It depends on the data and the compression it can achieve. Usually a high compression rate can be achieved but you would need to experiment. This link may help. exceleratorbi.com.au/table-size-from-power-bi-desktop/
Are Multidimensional Models just pivot tables for MS SQL/SSAS?
No. They're a different thing. They store multiple summary levels by the dimension values. They are slowly being deprecated I think but not officially.
Hi, can we have Relational data model in AAS which can generate SQL Query in backend when users select few columns, instead of MDX or DAX
Not exactly but you can use Power BI Direct Query which lets you connect directly to some data sources like Azure SQL DB but you need to be watchful of performance degradation and the database round trips are not likely to be at click speed.
@@BryanCafferky Thanks a lot 👍
Agreed on the statement, "IF you know Snowflaking, just don't do it..." :D
At 23:15, there is indeed snow flaking happening between product and product_class table.
Why using powerbi? I can use T-SQL on SSMS and get my data also
Good point. Analysis Services is mainly intended to support Power BI and Power Pivot in Excel. It is focused on supporting click speed response to slicing (filtering) and dicing (aggregating) to end users. So the need it fills is not the same as a SQL Server database. Thanks for the question.
Never had a need even to try tabular. Multidimensional model is native to multidimensional datawarehouses, has great performance and any small detail can be tuned. Mdx takes some time to learn but once you get what a set is and what a tupel is it all makes sense. Tabular model is more suitable for large flat tables where you would use a pivot table in excel. Numeric colums would be measures while nominal be attributes. Any one has experience integrating many tables ?
I think it depends on the requirements. For Power BI, tabular is optimal if it can be used. Because everything is compressed in memory, it should give the best performance and is generally faster to load and easier to build. Multidimensional models are generally considered legacy now but do have use cases.
@@BryanCafferky Also, Tableau has a reputation for not "playing well" with SSAS multidim.
Sir - your voice is too low. Content is good.
why do we use SSAS to create cubes when we can create cubes in SSMS besides tabular model?
SSMS is a user interface but SSAS is the service it is using. To develop Analysis Services models, Visual Studio is a good interface. You can create legacy cubes (MDX) or tabular models. If you use Azure Analysis Services, you can only create tabular models. Thanks for your comment.
@@BryanCafferky Thanks for this prompt reply. Great video as always :D I mean like we can create aggregate tables(which may be cubes) on sql server so why use SSAS to create cubes?
@@javedabdool1187 Glad to help. An aggregate table is not the same as a cube. A cube is a nested set of aggregations, i.e. by every possible value combination of dimensions. It is a multidimensional table. You cannot create that with SQL. Hope that helps. This blog gives a better picture of what I mean. docs.microsoft.com/en-us/analysis-services/multidimensional-models-olap-logical-cube-objects/cube-cells-analysis-services-multidimensional-data?view=asallproducts-allversions
@@BryanCafferky ahh ok much more complicated than I expected :D... Thank you again
do you think multidimesional will be obsoleted?
No. SQL Server 2019 still supports the multidimensional model so does not look like it will go away soon. A lot of customers use it as it has been around a long time. See this link for details. docs.microsoft.com/en-us/analysis-services/comparing-tabular-and-multidimensional-solutions-ssas
thank you very much for your great video and comment. you just make me think this. same with > 20 years sql concepts, eventhough the new Nosql database fix some weakness of sql databases. sql databases will stay for a while.
@@BryanCafferky Yes, but like you said, Azure Analysis Services is Tabular only. And cloud-based is the most likely future.
@@calvint3419 A bit early to write of relational databases if you take into account scale-out versions like Snowflake. Time will tell.
Though azure analysis service only supports tabular models.
Correct and that is usually the best choice for Power BI.
@@BryanCafferky im a beginner and have learned power bi and sql so what should i learn next azure analysis services or ssas which has more opportunities
@@BryanCafferky and what other things i should learn in azure to grab a sure shot job suggest me which has less coding.
@@hiteshjoshi3148 Why do you want less coding? more coding = more $$$.
@@BryanCafferky you are right sir but I'm more from excel and power bi side and have learned SQL also
You saved my job I guess Bryan
How so Deepu?
@@BryanCafferky sir which has greater demand multidimentional or tabular????
I meant in-memory Tabular since 2015...
Did anyone else get an a-ha moment trying to visualize n-dimensional cubes from the tesseract scene in Interstellar?