June 29, 2016 at 2:51 am
Hey,
I'm completely new to SSAS and doing some research whilst working through tutorials to build my first analysis database.
One thing I'm unclear on is how different multidimensional and tabular models are...from what I gather, the multidimensional model is better for dealing with complex datasets and business requirements, whilst tabular is more straightforward and can be efficient with smaller datasets. Do you have any specific examples where you've chosen one or the other & why?
Also are there any common "gotcha's" you would have liked to know about as a newbie? I already had a disaster yesterday where it stopped connecting to SQL Server...in the end I had to restart everything!
Would appreciate hearing others experiences. Also if anyone can recommend some more challenging tutorials than MS AdventureWorks that would be excellent...so far I've done that, and tried to investigate some of the smaller problems posted on this forum.
Thanks 🙂
June 30, 2016 at 8:06 am
Multidimensional is more mature right now. We have chosen it over tabular for 2 main reasons.
1. KPIs have a trend component (down side is it has to be written in MDX, but there is a template to get it done)
2. Many to Many relationship - Multidimensional has an easier way to get this done. The DAX needed for Tabular is complicated but doable.
The other reasons is the maturity of the product. It has been around since SQL Server 7.0. Tabular was released in 2012. There is alot of information (FREE) on the internet with detailed examples.
Some say DAX is easier to learn than MDX. I have not found that to be true. They both have complexities.
If you are just getting start, Tabular might be the route for you because it will have future improvements in the next releases of SQL Server and Multidimensional will not. If you go with Tabular, visit SQLBI.com for the most up to date information.
Thanks
Thomas
Thomas LeBlanc, MVP Data Platform Consultant
June 30, 2016 at 12:14 pm
one for tabular getting fast popularity is ease of use, i.e. no need to process cube, you can start designing reports using the dimensional model.
Also DAX is built based on Excel programming which makes it easier.
July 4, 2016 at 5:35 am
i had repaired a document a few years back. Might help you to understand it. Have some useful links as well.
July 5, 2016 at 3:57 pm
The answer is BOTH!
Love tabular, easy to set up models and use DAX for calculations etc.
Need the multidimensional mode for mining etc. If the Excel mining plug in connected to the tabular models it's a no brainer for us.
Querying Microsoft SQL Server 2012/2014 - Certified
July 6, 2016 at 7:48 am
tindog (6/29/2016)
Hey,I'm completely new to SSAS and doing some research whilst working through tutorials to build my first analysis database.
One thing I'm unclear on is how different multidimensional and tabular models are...from what I gather, the multidimensional model is better for dealing with complex datasets and business requirements, whilst tabular is more straightforward and can be efficient with smaller datasets. Do you have any specific examples where you've chosen one or the other & why?
Also are there any common "gotcha's" you would have liked to know about as a newbie? I already had a disaster yesterday where it stopped connecting to SQL Server...in the end I had to restart everything!
Would appreciate hearing others experiences. Also if anyone can recommend some more challenging tutorials than MS AdventureWorks that would be excellent...so far I've done that, and tried to investigate some of the smaller problems posted on this forum.
Thanks 🙂
It's very common that I run into a database with tables where an exported report from another platform or application is slammed into a single table. No real data modelling applied, no real thought around management of data over time and how it may change, just a straight drag and drop into a database.
I can tell you without a doubt, I prefer multidimensional structures over tabular for analytics and reporting. This is primarily because I feel the data is better managed in one versus the other. For example, being all of my dimensions are split across multiple tables, I can easily pull features of the data without having to sift through billions of records. If I want to see how many colors are in my rainbow, I simply only need to pull the DimColors tables to see all the various colors available. Then I can filter on those colors on the FactRainbow table just on that one dimension.
Once I have a structure in place, I can slice and dice the data any way I see fit. It's very structured, can scale and fit many requirements including going from multidimensional back into tabular for the data scientist who love flat denormalized data. That's what SQL views are for.
The other thing I really like is conforming and merging multiple data sources (or tabular datasets) into one. Splitting data in a multidimensional fashion allows me to create multiple star or snowflake schemas per data source and then merge them together seamlessly. I can do this physically in a table or with consolidated views. It really compliments the data well on many levels that again is manageable and most importantly, scalable.
July 6, 2016 at 8:16 am
xsevensinzx (7/6/2016)
tindog (6/29/2016)
Hey,I'm completely new to SSAS and doing some research whilst working through tutorials to build my first analysis database.
One thing I'm unclear on is how different multidimensional and tabular models are...from what I gather, the multidimensional model is better for dealing with complex datasets and business requirements, whilst tabular is more straightforward and can be efficient with smaller datasets. Do you have any specific examples where you've chosen one or the other & why?
Also are there any common "gotcha's" you would have liked to know about as a newbie? I already had a disaster yesterday where it stopped connecting to SQL Server...in the end I had to restart everything!
Would appreciate hearing others experiences. Also if anyone can recommend some more challenging tutorials than MS AdventureWorks that would be excellent...so far I've done that, and tried to investigate some of the smaller problems posted on this forum.
Thanks 🙂
It's very common that I run into a database with tables where an exported report from another platform or application is slammed into a single table. No real data modelling applied, no real thought around management of data over time and how it may change, just a straight drag and drop into a database.
I can tell you without a doubt, I prefer multidimensional structures over tabular for analytics and reporting. This is primarily because I feel the data is better managed in one versus the other. For example, being all of my dimensions are split across multiple tables, I can easily pull features of the data without having to sift through billions of records. If I want to see how many colors are in my rainbow, I simply only need to pull the DimColors tables to see all the various colors available. Then I can filter on those colors on the FactRainbow table just on that one dimension.
Once I have a structure in place, I can slice and dice the data any way I see fit. It's very structured, can scale and fit many requirements including going from multidimensional back into tabular for the data scientist who love flat denormalized data. That's what SQL views are for.
The other thing I really like is conforming and merging multiple data sources (or tabular datasets) into one. Splitting data in a multidimensional fashion allows me to create multiple star or snowflake schemas per data source and then merge them together seamlessly. I can do this physically in a table or with consolidated views. It really compliments the data well on many levels that again is manageable and most importantly, scalable.
A well written post, thankyou for sharing. I've just sent an e-mail to my employer asking to be sent on an SSAS course I found, the more I look the more complex (but exciting) this all looks! I've spent the days since my post developing a tabular solution which is very straight forward, but I'm interested to take a greater look at multidimensional.
July 6, 2016 at 12:26 pm
TinDog,
If you haven't done so already, check out PragmaticWorks.com Brian Knight knows SSAS inside and out. There are a bunch of free tutorials on their website. I think you have to register, but you rarely get e-mails from them. I went to a UG presentation where he built a cube in an hour in SSAS. It was really cool.
Pieter
July 8, 2016 at 11:48 am
We use both multidimensional and tabular. One thing we have discovered is that tabular connects directly to "big data" technologies such as Hive without needing linked servers. Multidimensional, by contrast, required us to use linked servers. That resulted in much slower performance.
July 9, 2016 at 7:57 am
imani_technology (7/8/2016)
We use both multidimensional and tabular. One thing we have discovered is that tabular connects directly to "big data" technologies such as Hive without needing linked servers. Multidimensional, by contrast, required us to use linked servers. That resulted in much slower performance.
Well that makes sense because you're connecting to the summarized or sometimes raw files in tabular formats within NoSQL. However, you risk letting one system conform the other system versus just moving fully to one versus the other too. 😛
In my scenarios, it's not RDBMS -> NoSQL, but NoSQL -> RDBMS.
But bear in mind, I'm actually not using SSAS for that multi-dimensional conversion. It's stored in the database as such and pushed into additional cubes if needed for further summarization.
July 11, 2016 at 6:13 pm
xsevensinzx (7/9/2016)
imani_technology (7/8/2016)
We use both multidimensional and tabular. One thing we have discovered is that tabular connects directly to "big data" technologies such as Hive without needing linked servers. Multidimensional, by contrast, required us to use linked servers. That resulted in much slower performance.Well that makes sense because you're connecting to the summarized or sometimes raw files in tabular formats within NoSQL. However, you risk letting one system conform the other system versus just moving fully to one versus the other too. 😛
In my scenarios, it's not RDBMS -> NoSQL, but NoSQL -> RDBMS.
But bear in mind, I'm actually not using SSAS for that multi-dimensional conversion. It's stored in the database as such and pushed into additional cubes if needed for further summarization.
I'm not quite sure what you mean. We are using Hive to create a SQL-like layer of the data. From there, we are loading the data into Tabular. The reason why we are doing that is so we can do reporting using either Power Pivot or Power BI. Are you saying we should skip the Microsoft technologies altogether and simply find another way to present the data to the users?
July 13, 2016 at 12:03 am
imani_technology (7/11/2016)
xsevensinzx (7/9/2016)
imani_technology (7/8/2016)
We use both multidimensional and tabular. One thing we have discovered is that tabular connects directly to "big data" technologies such as Hive without needing linked servers. Multidimensional, by contrast, required us to use linked servers. That resulted in much slower performance.Well that makes sense because you're connecting to the summarized or sometimes raw files in tabular formats within NoSQL. However, you risk letting one system conform the other system versus just moving fully to one versus the other too. 😛
In my scenarios, it's not RDBMS -> NoSQL, but NoSQL -> RDBMS.
But bear in mind, I'm actually not using SSAS for that multi-dimensional conversion. It's stored in the database as such and pushed into additional cubes if needed for further summarization.
I'm not quite sure what you mean. We are using Hive to create a SQL-like layer of the data. From there, we are loading the data into Tabular. The reason why we are doing that is so we can do reporting using either Power Pivot or Power BI. Are you saying we should skip the Microsoft technologies altogether and simply find another way to present the data to the users?
I'm saying that ideally, you have to dimensionalize the data at some point. This is going to require processing time as part of the ETL. In practice, you are loading tabular data regardless if it's coming from NoSQL or some other data source into an engine that will dimensionalize the data and summarize the facts on the chosen grains. Versus dumping tabular data into NoSQL and sticking with it because you don't want to dimensionalize the data into a proper star or snowflake schema.
I'm similar to you, but I use other NoSQL technologies that feed tabular data into a RDBMS where it's put into a snowflake schema. Then all reporting is done from the RDBMS into reporting platforms like PowerBI. NoSQL becomes the data lake. The centralized repository of both structured and non-structured data that feeds the RDBMS and the ad-hoc analysis for data discovery. Then the RDBMS becomes the data warehouse and the BI platform of structure (column and row) data where it's stored in a multi-dimensional structure that can also be converted with views (or physical tables) of tabular data either within itself or a separate data mart per the business requirements.
I understand that takes more processing and is more complex than just feeding tabular data into NoSQL directly to a tabular format for PowerBI, but what exactly is the benefits there in that process if the data changes, needs to be updated, refreshed and so on? More hive schemas ontop of the data?
July 13, 2016 at 4:54 pm
Yes, I'm actually starting to question the value of using Big Data for data warehousing. It seems like a lot of work just to re-invent the wheel.
xsevensinzx (7/13/2016)
imani_technology (7/11/2016)
xsevensinzx (7/9/2016)
imani_technology (7/8/2016)
We use both multidimensional and tabular. One thing we have discovered is that tabular connects directly to "big data" technologies such as Hive without needing linked servers. Multidimensional, by contrast, required us to use linked servers. That resulted in much slower performance.Well that makes sense because you're connecting to the summarized or sometimes raw files in tabular formats within NoSQL. However, you risk letting one system conform the other system versus just moving fully to one versus the other too. 😛
In my scenarios, it's not RDBMS -> NoSQL, but NoSQL -> RDBMS.
But bear in mind, I'm actually not using SSAS for that multi-dimensional conversion. It's stored in the database as such and pushed into additional cubes if needed for further summarization.
I'm not quite sure what you mean. We are using Hive to create a SQL-like layer of the data. From there, we are loading the data into Tabular. The reason why we are doing that is so we can do reporting using either Power Pivot or Power BI. Are you saying we should skip the Microsoft technologies altogether and simply find another way to present the data to the users?
I'm saying that ideally, you have to dimensionalize the data at some point. This is going to require processing time as part of the ETL. In practice, you are loading tabular data regardless if it's coming from NoSQL or some other data source into an engine that will dimensionalize the data and summarize the facts on the chosen grains. Versus dumping tabular data into NoSQL and sticking with it because you don't want to dimensionalize the data into a proper star or snowflake schema.
I'm similar to you, but I use other NoSQL technologies that feed tabular data into a RDBMS where it's put into a snowflake schema. Then all reporting is done from the RDBMS into reporting platforms like PowerBI. NoSQL becomes the data lake. The centralized repository of both structured and non-structured data that feeds the RDBMS and the ad-hoc analysis for data discovery. Then the RDBMS becomes the data warehouse and the BI platform of structure (column and row) data where it's stored in a multi-dimensional structure that can also be converted with views (or physical tables) of tabular data either within itself or a separate data mart per the business requirements.
I understand that takes more processing and is more complex than just feeding tabular data into NoSQL directly to a tabular format for PowerBI, but what exactly is the benefits there in that process if the data changes, needs to be updated, refreshed and so on? More hive schemas ontop of the data?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply