Welcome to our Blog Post on creating Manually Partitioned Fact Tables for MicroStrategy.
This post will be of interest to you if you use both MicroStrategy and SQL Server in your Business Intelligence Solution.
In 2005 Microsoft introduced support for Partitioned Tables in the Enterprise Edition of SQL Server.
The database supported partitioning is quite simplistic in that it only provides one partitioning field. Even MYSQL supports more than that.
However, it was still useful to migrate from the manual partitioning schemes used previously to the database supported partitioning offered by MicroSoft in the SQL Server Enterprise Edition.
This support was enhanced in 2008 and 2008R2.
However, when Microsoft released SQL Server 2012 the licensing fee for Enterprise Edition was altered so that it was only available on a per core basis.
This means that companies that had small numbers of connected users and were using CAL licensing on SQL Server 2008 R2 and were using partitioning support, would have to move to per core licencing for SQL Server 2012.
This could represent quite a jump in license fees depending on the clients particular situation.
If your company is in the position of using SQL Server Enterprise Edition supported database partitioning and you would like to move to a lower level of SQL Server at 2014/2016 level you might want to consider reviewing this blog post.
If you have Microstrategy and SQL Server at a lower level than Enterprise Edition and you have not implemented any sort of partitioning scheme and you have fact tables over the 100 Million row mark?
You might want to review this blog post to learn how to implement manual partitioning in SQL Server and properly access it via MicroStrategy.
Of course, many of the items mentioned in the blog post work on other databases.
However, most of the other databases do not have a significant fee jump to get their database supported partitioning features and so in most cases using their database supported partitioning feature is more desirable.
This post is intended for MicroStrategy users who are using SQL Server at a lower level than Enterprise Edition and wish to gain the benefits of physically partitioned tables without the associated cost of the Enterprise Edition license.
MicroStrategy with Standard Edition at SQL Server 2014 level with 132GB of memory supported works quite fine for most medium sized enterprises.
Please click the link to download the PDF for this blog post.