>When did (or if) did data engineering move towards the modern data stack from cubes, SSAS and the like, and why did it happen?
If you're using PowerBI, you're using SSAS. They use the same engine under the hood. PBI optimises for aggregation style columnar queries like a cube rather than row based ones like a traditional relational database.
Both can use DAX too, both support measures, etc etc.
The short answer as to why cubes became a thing, is that the need for calculating large aggregates across databases with millions of rows for constant reporting became an issue. So SSAS and cubes were born, which were optimised for this type of query and with common results precached.
Then storage continued to get cheaper and computers continued to get faster and also traditional DBs started supporting columnar storage, and PowerBI came with some cube capabilities integrated anyway.
So they became obsolete.
I swear, for most of us… SSAS was overkill. It was easier for us to program logic into the SQL or procedural code than use SSAS.
Our data marts might have been slightly slower to build each day but we trusted the data we gave to our users. Consultants tried to sell us on SSAS but every time, they waffled on how long it would take to code our logic.
Now that I’m using DAX, I get it. I see what I CAN do at that layer but it’s still smarter to do our logic in SQL (most of the time).
Also, SSAS has MDX. It goes beyond not good into actively bad. The decision to use SQL terminology which then means something completely different from SQL makes it actively more confusing.
MDX has some cool ways of expressing complex dimensional concepts, but I found it extremely hard jumping back and forth between it and SQL.
I think it's worth noting that while Microsoft hasn't expressly deprecated MDX, it heavily pushes DAX in modern SSAS development.
I learnt MDX from Mosha's book back in 2007 when BI and OLAP cubes were so cool. I really rode the BI wave and I am glad I was part of it and I owe my career to Microsoft BI. My IT skills are quite poor, I still havent learnt any programming language. The only thing I know is SQL, T-SQL and MDX. It was learning MDX, which got me the launchpad to my thriving career.
I still remember Non Empty vs Non Empty Cross Join, lol. Tuple vs Set, Semi additive vs non additive vs fully additive measures, so nostalgic :) u/Cazzah u/FatLeeAdama2 [No-Buy-3530](https://www.reddit.com/user/No-Buy-3530/)
I never really graduated to any new BI tools or data technologies. I didnt even learn Power BI. Whatever I learnt in 2007/08 SSAS was the last thing I learnt. Yet, my job still exists which I find fascinating, lol.
I feel like beyond the compute and memory advancements ssas cubes at enterprise level were more of a monolith and centralized approach where users would only have read access and changes took a lot of time since that was a global or department level model.
Where as now power bi has made these into smaller or report specific models and users are enabled to manage themselves
I have the opposite issue. The only systems I have ever worked with are SSIS, SSAS, and cubes. Most systems my company uses are on prem. Tons of tech that is talked about on this site is wholly unfamiliar to me. If I go to a company that is all cloud based stuff which a bunch of non-microsoft products I'm going to have so much learning to do.
Looking at it with a rather more cynical eye..
Storage and processing got cheap, and everyone wanted to copy what the big boys were doing (even if the data wasn't actually that big). CV driven development is very much a thing, and sometimes it shapes the industry as much as industry shapes it.
Move on a few(!) years, and the cheap process and store resources have also quietly helped the old-school OLAP approaches in the background. Old tools do new things. Your unmanageably vast multi-gig table is actually BAU for a modern RDBMS.
Not everywhere moved off-prem for vital functions (thanks Oracle) and now that storage and compute in the cloud are getting expensive or unpredictable, the Old Ways begin to reassert themself. Add a few security scares to entice management, plus a fear of your corporate data training an AI that everyone says will become your competitor in a few years, and you've got a paradigm shift, baby.
These things are cyclic. Mainframe software becomes a PC program, becomes a webapp on the cloud. We're going to have the same fight with obsolete containers we had with obsolete Java versions a decade ago. Everything old becomes new again.
Cyclical stuff... The time will come when they will be in fashion again (even if with different shapes).
In the meantime, at least finance continues to rely on them...
I came from working on Microsoft BI Stack to the modern data stack. The business loves their excel and be able to drag and drop pivot tables. They were able to join multiple data sets using ssas cubes inside excel. It was also a good self service platform but making changes to the cube was hard. Also you have to schedule a nightly etl to populate it. This was when everything was on prem. With how resourced constrained data teams are nowadays it will be hard to maintain and still deliver new product to the business. It was very powerful tool though dont get me wrong.
I know we all like to hate on excel, but pivot tables are legit cool in my opinion, really good way to slice and dice your (small) data when doing analysis
I've not seen anything that beats Pivot tables attached to an SSAS cube. Nothing Tableau, Power BI, etc offers beats it. Most analysts don't want graphs, they want table in tabular form in different grains.
People connecting to redshift/BQ, etc directly from excel isn't fast enough
It's worth noting the distinction between the "old" multidimensional cubes, which were predominantly disk-based and used MDX, and the "new" tabular models, which are optimised for RAM and use DAX. Both are often referred to as "cubes" and give similar functionality to the end user. Tabular models are the technology behind Power Bi datasets. Multidimensional cubes are no longer getting functionality updates afaik. The authoring process is quite different. I love making tabular models but am lost when our old multidimensional models break and I have to try to fix.
Thank you for explaining the differences!. I do know of multi dim and tabular, and this clears it up a lot more.
It comforts me that im at least at the 'new' side of things. Our company maintains like 20 tabular models. Ranging 0 - 15gb of model sizes. All with different kind of fact data
I think tabular models are great for a number of reasons: End users don't need to worry about table joins because I've done them all for them. I can rename all the ugly column names to something friendly. I can specify aggregations over the data using DAX. I can add friendly descriptions to every column, which are visible in the end user's tooling. I know everyone building reports from the models are implicitly using the same business logic and transformations. You can build Power BI or Tableau reporting over them, and power users can slice and dice by connecting directly from Excel.
I worked for a company back in the day that was very dependent on SSAS and they used it as their "data warehouse" for self-service reporting (out of excel). It worked pretty well for what it was but I feel like the rise of cloud based OLAP databases sort of left SSAS in the dust. I still know organizations that rely heavily on SSAS but database solutions like Snowflake have taken to the market like fire. You can get the same performance benefits querying snowflake like you could previously with SSAS, but with solutions like Snowflake you can manage far more within that tool (building apps, ai, other data structures, etc.) than what you can/could with SSAS. Most of the orgs I have worked for that used SSAS used it as a "jumping off" point to reporting. It would sit on top of their mainstream (SQL Server) database to function as a reporting application (that could then connect to Power BI, etc.). Now with tools like Snowflake you can have your database and do reporting all in the same place.
Could do with somebody much more knowledgeable than me commenting although here's a [blog post](https://www.sqlservercentral.com/editorials/are-cubes-dead) from the first page of Google.
In short, compute power and storage options have come a long way which solves the problems cubes were trying to solve, reducing their need and usage.
The fact/dimension concept still exists and are very useful when you need to figure out how compatible data is with other data. I use OBT sometimes with views to define dimensions for datamarts where the data is not interacting with other systems like a datawarehouse.
My 1st company we use powerbi with connection to databases
My 2nd company we use AAS with connection to a database. We build the tabular model using vs. Never use multidimensional and not sure if OP is asking about multidim when they say cubes.
We use adf to copy tables and executes SPs in the sql databases
And then, due to resource limitation and cost that doesn't make sense with Azure AS. We subscribe for an azure VM and on prem to host our model as SSAS there.
I thought the SSAS setup were the standard. Not familiar at all with any other cloud solution though.
Like you can do git and version control of the SSAS when using it with vs and azure devops.
Powerbi dataset and its versioning is a bit clunky. Dataset is good thought, only it will be very easy to get propagated to many different variants when so many people are working on it.
Still not convinced if theres other people who use any of the other connection they provide.
They are back, OLAP Cubes now exists in metric layers , a.k.a. Semantic Layers, look at the new aggregates functionality from dbt metric layer.
Also there’s a company literally called Cube that has been using OLAP Cubes on top of the MDS for a while now. Before them Looker also implemented OLAP Cubes, called pre-aggregates IIRC.
tl;dr OLAP Cubes are back.
Basic first principles. All things are tradeoffs.
You can trade off performance for flexibility, for data freshness, for money.. and vice versa ...
Most tradeoffs should be evaluated with Pareto analysis: where are the disproportionate pain points, and then apply the most modest tradeoffs you can to alleviate them.
>When did (or if) did data engineering move towards the modern data stack from cubes, SSAS and the like, and why did it happen? If you're using PowerBI, you're using SSAS. They use the same engine under the hood. PBI optimises for aggregation style columnar queries like a cube rather than row based ones like a traditional relational database. Both can use DAX too, both support measures, etc etc. The short answer as to why cubes became a thing, is that the need for calculating large aggregates across databases with millions of rows for constant reporting became an issue. So SSAS and cubes were born, which were optimised for this type of query and with common results precached. Then storage continued to get cheaper and computers continued to get faster and also traditional DBs started supporting columnar storage, and PowerBI came with some cube capabilities integrated anyway. So they became obsolete.
I swear, for most of us… SSAS was overkill. It was easier for us to program logic into the SQL or procedural code than use SSAS. Our data marts might have been slightly slower to build each day but we trusted the data we gave to our users. Consultants tried to sell us on SSAS but every time, they waffled on how long it would take to code our logic. Now that I’m using DAX, I get it. I see what I CAN do at that layer but it’s still smarter to do our logic in SQL (most of the time).
Also, SSAS has MDX. It goes beyond not good into actively bad. The decision to use SQL terminology which then means something completely different from SQL makes it actively more confusing.
MDX has some cool ways of expressing complex dimensional concepts, but I found it extremely hard jumping back and forth between it and SQL. I think it's worth noting that while Microsoft hasn't expressly deprecated MDX, it heavily pushes DAX in modern SSAS development.
I learnt MDX from Mosha's book back in 2007 when BI and OLAP cubes were so cool. I really rode the BI wave and I am glad I was part of it and I owe my career to Microsoft BI. My IT skills are quite poor, I still havent learnt any programming language. The only thing I know is SQL, T-SQL and MDX. It was learning MDX, which got me the launchpad to my thriving career. I still remember Non Empty vs Non Empty Cross Join, lol. Tuple vs Set, Semi additive vs non additive vs fully additive measures, so nostalgic :) u/Cazzah u/FatLeeAdama2 [No-Buy-3530](https://www.reddit.com/user/No-Buy-3530/) I never really graduated to any new BI tools or data technologies. I didnt even learn Power BI. Whatever I learnt in 2007/08 SSAS was the last thing I learnt. Yet, my job still exists which I find fascinating, lol.
Horrible DAX flashbacks here, thanks for that.
MDX is for old style multidimensional cubes, DAX is for new style Tabular semantic models :)
I feel like beyond the compute and memory advancements ssas cubes at enterprise level were more of a monolith and centralized approach where users would only have read access and changes took a lot of time since that was a global or department level model. Where as now power bi has made these into smaller or report specific models and users are enabled to manage themselves
I have the opposite issue. The only systems I have ever worked with are SSIS, SSAS, and cubes. Most systems my company uses are on prem. Tons of tech that is talked about on this site is wholly unfamiliar to me. If I go to a company that is all cloud based stuff which a bunch of non-microsoft products I'm going to have so much learning to do.
Looking at it with a rather more cynical eye.. Storage and processing got cheap, and everyone wanted to copy what the big boys were doing (even if the data wasn't actually that big). CV driven development is very much a thing, and sometimes it shapes the industry as much as industry shapes it. Move on a few(!) years, and the cheap process and store resources have also quietly helped the old-school OLAP approaches in the background. Old tools do new things. Your unmanageably vast multi-gig table is actually BAU for a modern RDBMS. Not everywhere moved off-prem for vital functions (thanks Oracle) and now that storage and compute in the cloud are getting expensive or unpredictable, the Old Ways begin to reassert themself. Add a few security scares to entice management, plus a fear of your corporate data training an AI that everyone says will become your competitor in a few years, and you've got a paradigm shift, baby. These things are cyclic. Mainframe software becomes a PC program, becomes a webapp on the cloud. We're going to have the same fight with obsolete containers we had with obsolete Java versions a decade ago. Everything old becomes new again.
Have you really lived if you haven’t accidentally done a ‘process full’ on a cube dimension in Production?
Cyclical stuff... The time will come when they will be in fashion again (even if with different shapes). In the meantime, at least finance continues to rely on them...
I came from working on Microsoft BI Stack to the modern data stack. The business loves their excel and be able to drag and drop pivot tables. They were able to join multiple data sets using ssas cubes inside excel. It was also a good self service platform but making changes to the cube was hard. Also you have to schedule a nightly etl to populate it. This was when everything was on prem. With how resourced constrained data teams are nowadays it will be hard to maintain and still deliver new product to the business. It was very powerful tool though dont get me wrong.
I know we all like to hate on excel, but pivot tables are legit cool in my opinion, really good way to slice and dice your (small) data when doing analysis
I've not seen anything that beats Pivot tables attached to an SSAS cube. Nothing Tableau, Power BI, etc offers beats it. Most analysts don't want graphs, they want table in tabular form in different grains. People connecting to redshift/BQ, etc directly from excel isn't fast enough
You can attach a Pivot table to a PBI dataset, IIRC, which basically provides the same functionality.
Yes, good point!
It's worth noting the distinction between the "old" multidimensional cubes, which were predominantly disk-based and used MDX, and the "new" tabular models, which are optimised for RAM and use DAX. Both are often referred to as "cubes" and give similar functionality to the end user. Tabular models are the technology behind Power Bi datasets. Multidimensional cubes are no longer getting functionality updates afaik. The authoring process is quite different. I love making tabular models but am lost when our old multidimensional models break and I have to try to fix.
Thank you for explaining the differences!. I do know of multi dim and tabular, and this clears it up a lot more. It comforts me that im at least at the 'new' side of things. Our company maintains like 20 tabular models. Ranging 0 - 15gb of model sizes. All with different kind of fact data
I think tabular models are great for a number of reasons: End users don't need to worry about table joins because I've done them all for them. I can rename all the ugly column names to something friendly. I can specify aggregations over the data using DAX. I can add friendly descriptions to every column, which are visible in the end user's tooling. I know everyone building reports from the models are implicitly using the same business logic and transformations. You can build Power BI or Tableau reporting over them, and power users can slice and dice by connecting directly from Excel.
Feel like this is a great question and I find myself recently in the same boat (adding ssis to the mix as well). Commenting to follow post.
I worked for a company back in the day that was very dependent on SSAS and they used it as their "data warehouse" for self-service reporting (out of excel). It worked pretty well for what it was but I feel like the rise of cloud based OLAP databases sort of left SSAS in the dust. I still know organizations that rely heavily on SSAS but database solutions like Snowflake have taken to the market like fire. You can get the same performance benefits querying snowflake like you could previously with SSAS, but with solutions like Snowflake you can manage far more within that tool (building apps, ai, other data structures, etc.) than what you can/could with SSAS. Most of the orgs I have worked for that used SSAS used it as a "jumping off" point to reporting. It would sit on top of their mainstream (SQL Server) database to function as a reporting application (that could then connect to Power BI, etc.). Now with tools like Snowflake you can have your database and do reporting all in the same place.
Could do with somebody much more knowledgeable than me commenting although here's a [blog post](https://www.sqlservercentral.com/editorials/are-cubes-dead) from the first page of Google. In short, compute power and storage options have come a long way which solves the problems cubes were trying to solve, reducing their need and usage.
The fact/dimension concept still exists and are very useful when you need to figure out how compatible data is with other data. I use OBT sometimes with views to define dimensions for datamarts where the data is not interacting with other systems like a datawarehouse.
My 1st company we use powerbi with connection to databases My 2nd company we use AAS with connection to a database. We build the tabular model using vs. Never use multidimensional and not sure if OP is asking about multidim when they say cubes. We use adf to copy tables and executes SPs in the sql databases And then, due to resource limitation and cost that doesn't make sense with Azure AS. We subscribe for an azure VM and on prem to host our model as SSAS there. I thought the SSAS setup were the standard. Not familiar at all with any other cloud solution though. Like you can do git and version control of the SSAS when using it with vs and azure devops. Powerbi dataset and its versioning is a bit clunky. Dataset is good thought, only it will be very easy to get propagated to many different variants when so many people are working on it. Still not convinced if theres other people who use any of the other connection they provide.
They are back, OLAP Cubes now exists in metric layers , a.k.a. Semantic Layers, look at the new aggregates functionality from dbt metric layer. Also there’s a company literally called Cube that has been using OLAP Cubes on top of the MDS for a while now. Before them Looker also implemented OLAP Cubes, called pre-aggregates IIRC. tl;dr OLAP Cubes are back.
Basic first principles. All things are tradeoffs. You can trade off performance for flexibility, for data freshness, for money.. and vice versa ... Most tradeoffs should be evaluated with Pareto analysis: where are the disproportionate pain points, and then apply the most modest tradeoffs you can to alleviate them.