Note: You can listen to the blog post on the video or read the blog post.
Hello and Welcome.
I am Esther.
I am Peters A I Assistant to create voice overs.
I will simply read Peters blog posts, so that you have a choice of reading the blog post, or listening to my voice.
Hello and welcome Gentlemen.
As I have recently announced on my channel.
I will be doing some opinion pieces and response videos.
This blog post is just an opinion piece on SSAS Tabular format.
And really, it’s not even an opinion piece.
It’s just my comments.
For my sins in a prior life I have had to take a crash course on SSIS recently.
As I expected it is not a million miles away from Informatica or DataStage.
I actually did some testing on it and it turns out that the See TL scheduler can run SSIS mappings as SQL Server Jobs inside the scheduler.
So I will be doing a blog post about that in the next blog post.
But as I was playing around with SSIS I wondered to myself about what level of support is available for SSAS tabular version.
Meaning, is table partitioning supported in SSAS Tabular version in Standard Edition.
I was wondering about this because we have recently figured out how to support updates of partitions in cloud Power BI.
In cloud Power BI it is possible to set up the refresh of the data set such that if an older partition changes you can reload just the partition that changed.
For example?
Let us say you have five years of detailed sales data in your data warehouse.
And say you want five years of summary data in your Power BI report.
And say that for whatever reason you want to be able to accept sales adjustments back to the beginning of the financial year.
So what you would do is load up the five years of history at the date of rolling out the dashboard.
You would create monthly partitions for the sales going back five years.
Now, if some accountant has mandated that the amount of a sale must some how be altered historically then you want just that month that has been changed to be reloaded.
Since Power BI only supports partition reloading and not updating you have to truncate and reload the partition.
So if the month affected is six months ago?
In that nights refresh for the Power BI Dashboard you want only the current month, and the month six months ago to be refreshed.
So we figured out how to do that.
So with having done that recently the thought occurred to me to check for partitioning support in SSAS tabular version.
Of course, with this build up?
You have guessed that partitioning in tabular version is only supported in Enterprise Edition.
The list price of Enterprise Edition being just over eight thousand dollars per core.
Of course, knowing Microsoft I should have guessed that was the case.
This creates an interesting topic to talk about.
It is obvious that Fabric will be priced in a similar range to SSAS Tabular.
So to carry the same workload as say four cores of SSAS Microsoft is going to price Fabric Compute Units to be in the range of thirty two thousand dollars over 4 years of use.
Somewhere in that range.
So I looked up Fabric pricing.
Gentlemen, it’s eye watering.
Microsoft is charging a lot for Fabric Compute Units which are also tied to maximum model sizes.
So this brought me back to Power BI and my perennial favourite Meta five.
Firstly Power BI.
With Power BI Professional Edition you can refresh partitions from the underlying database.
You can also implement drill through to the detailed tables and you can create hierarchies of multi level summaries in the Power BI models.
That means with our multi level summary capabilities in See TL you can very easily use Power BI directly over the top of our data models on SQL Server Standard Edition.
You don’t have to have some dedicated form of SSAS or Fabric to implement Power BI.
You can do it right on top of SQL Server Standard Edition.
My last check the price per core for Standard Edition is two thousand one hundred dollars per core.
This means that Standard Edition is about one quarter the price per core compared to Enterprise Edition.
So today you can buy a sixteen core license for SQL Server Standard Edition for thirty three thousand six hundred dollars approximately.
Of course that will vary with where the license is actually purchased.
The restriction on using Power BI directly against an SQL Server to load data into Power BI Datasets is that you have to refresh each model for each Power BI Data Set rather than have an SSAS tabular data source or Fabric data source.
But that is a relatively small inconvenience against the cost saving of not having SSAS tables or Fabric at all.
You can’t really have large tables storing data over a long period of time in SSAS tabular without partitioning.
The same is true at database level.
When it comes to data warehousing and storing sales and financial transactions over many years?
All businesses of any significant size need partitioning whether it be manual or database supported.
What you have available to you is the ability to have multi level summary data in Ralph Kimball type dimensional databases.
You can query them directly with full hierarchy navigation with Power BI without the need for a separate dedicated storage area for SSAS tables or Fabric.
And Power BI Professional Edition supports the partitioning of the dataset that is hosted on Azure.
That is a pretty compelling entry level offer from Microsoft.
It just needs See TL to handle the multi level aggregation of data in the ETL System.
The other point I wanted to make was this.
Excel is still king of the hill when it comes to reporting.
Lots of companies bought SSAS to be able to do their reporting in Excel.
Microsoft has very stubbornly refused to release a product like at scale which can send MDX queries to an underlying relational database.
Given the revenue from SSAS you can’t really blame them.
Ralph Kimballs product now called Meta5 supports sending data into the Excel Power Pivot model from whatever data source you are authorized to have access to.
It’s unique feature is this.
Say you are a retailer and you have four hundred stores and you have twenty regional managers managing twenty stores each.
You also have four hundred store managers.
And you have some people in head office who want daily reports.
With SSAS you create one dashboard per purpose and then each person who uses that dashboard selects their names and the query to SSAS returns just their data to their dashboard.
This requires SSAS or similar products.
This requires a round trip each click on a filter.
Every drill down, every filter click, is a round trip.
And the size of the SSAS Server soon grows at Enterprise Edition costs per core.
With Meta5 you create a template Excel dashboard with just one row in each of the tables in the Power Pivot model just to make it work.
You create one template dashboard for each dashboard.
Then each night you want to run the reports?
The template is copied and the data for each user of the dashboard is sent into the template copy one at a time.
So if you had ten daily reports for the region managers you would have ten templates but you would get two hundred daily reports.
And the daily reports would be sent to the one drive of each of the region managers.
If each of the store managers received one comprehensive daily report each morning?
You would copy the template four hundred times and put just the data for each store manager into each excel report and then send the finished excel report to the store manager.
Of course.
When the region managers or store managers open the spreadsheet it will be stored locally on their own PC and so response will be pretty much instant for drill downs and filter clicks.
The downside is that the data has to fit into the power pivot model inside the spreadsheet.
Of course you can also have a combination of both.
You could use SSAS where you needed the report to have access to large amounts of data than an excel spreadsheet can hold.
However, experience shows that for more than ninety five percent of all reporting that needs to be done, can be done with the data inside the excel spreadsheet.
There is a reason why Excel remains king of the hill for all reporting around the world.
It is good enough in most cases.
And Meta5 is a cheaper and easier way to get your excel spreadsheets implemented.
Now that I know that in SSAS Tabular version you have to buy Enterprise Edition to get partitioning.
That seems to add some more weight to the argument that it’s a good idea to use See TL to get multi level summaries in dimensional models.
Then use either Power BI and or Meta five plus excel to do dashboarding and reporting.
If you would like to find out more please just contact me and I will give you some links to some demos.
I find all this stuff pretty interesting because I have spent much of the last 25 years avoiding the use of most of the multi dimensional databases.
I did sell Essbase and Holos in the past.
But it has always been clear that the relational databases will gain enough performance to make the multi dimensional databases redundant.
SSAS Tabular is pretty much Microsoft waving the white flag over multi dimensional databases.
I really should have realised they would only support SSAS Tabular partitioning in Enterprise Edition.
And with that?
I hope you found this blog post interesting and informative.
Thank you very much for your time and attention.
I really appreciate that.
Best Regards.
Esther.
Peters A I Assistant.
