Sparks from the Anvil

Microsoft deliver on their promises, handsomely!

At the SQL PASS Summit in October, James Phillips made a promise that everything he talked about or demonstrated in his keynote would be available by the end of the year.  Well they have delivered, handsomely! 

Today SQL Server 2016 CTP3.2 has been made available which delivers mobile reporting to SQL Server Reporting Services (SSRS).  SSRS has been ignored by Microsoft for years as James Phillips acknowledges in his keynote, but today SSRS gets a complete makeover and lots of new features including the integration of the Datazen mobile technology (screenshot below).  About time on-premise reporting got a makeover!


Report Manager’s new web portal also provides support for Key Performance Indicators (KPIs), which starts taking Report Manager into the realms of the dashboard arena. 


The December update to Power BI desktop includes the ability to include R visualisations into your dashboards (screenshot below).  R is an exciting development for BI professionals as it allows them to deliver practical tools to data scientists who are a demanding and extremely well educated community of end-users, very familiar with the tools and techniques required to big data analysis.  SQL Server 2016 includes R services, so Microsoft are only a small step away from delivering an end-to-end R solution with both the massively parallel data querying to the visualisations.  If you missed it, it is well worth watching Jason Wilcox PASS Summit presentation on The Future of Analytics


Power BI’s SSAS multidimensional support just got better to, with the introduction of support for hierarchies.  Power BI is really galvanising renewed interest in SSAS multidimensional as it is one of the best ways of implementing a user specific dashboard where different users use the same dashboard, but see their own data. I am working on this scenario at the moment for one client where different manages in the firm get to see their own team’s sales data and statistics.  The infrastructure to support this scenario is the newly announced Power BI Enterprise Gateway and the existing Azure Active Directory which provides single sign-on (SSO) access to thousands of cloud SaaS Applications like Office365, Salesforce, DropBox and Google Apps.

Power BI desktop also gets tighter control of formatting and many enhancements to various visualisations.  I love the new Format Painter! Take a look at the release video here.

Microsoft also announced the Data Insights Summit will take place in Bellevue, WA in March.  Maybe worth a trip!

Right, time to install CTP3.2 and get cracking on that mobile BI demo

Posted by DrJohn with no comments

Continuous Integration and the Data Warehouse

SQLSaturdaySouthamptonI am excited to say that over the next two weekends I am speaking at PASS SQL Saturday events in Southampton and Slovenia on Continuous Integration and the Data Warehouse.  Over the coming weeks I hope to provide some supporting posts outlining exactly how to set up CI in the data warehouse context.  However, I am taking a new contract next week, so my time may be sparse!

In my view, the key to implementing Continuous Integration is to move away from the old migration-based development of your SQL database to the declarative model approach offered by SQL Server Data Tools (SSDT).  In my opinion, without this transition you will be fighting an up-hill battle!

SQL Saturday SloveniaI recently attended a SQL Supper session by Alex Yates of RedGate where he discussed the pros and cons of state-based declarative model versus migration-based database development strategies.  Although he outlines the problems inherent in both approaches, he did not outline how SSDT and good source code management overcomes all these issues as his talk was tool-neutral.  He is also speaking at Southampton, so please go along.

With the declarative model approach, problems typically arise when you:

  1. Add a non-nullable column to an existing table which contains data
  2. Rename an existing column
  3. Two developers change the same objects at the same time one adds a new column to a table and a related proc, and the other developer renames the same table. This is the example Alex uses in his presentation.

I am sure there are a few other scenarios, but none immediately come to mine on this a cold November morning.

SQL Server Data Tools overcomes these issues by the following specific functionalities:

  1. Simply select “smart defaults” when you publish your database.  This will add a new nullable column to the table, then populate it with zeros and finally make it non-nullable.  All automatically!  You can then run your own data fix to update the zeros with the correct values. Clearly on a table containing a large amount of data, populating the new column with zeros will take a long time, so you may want to think about your deployment methodology. 
  2. Use the Refactor-Rename option on the SQL menu; but remember to check in the newly created refactor file!
  3. Of course, when the second developer checks the code into source control, he will be made aware of conflict and will have to merge his changes into the branch. If he does it wrong, SQL Server Data Tools will immediately identify that he broke the related stored proc, particularly when he compiles the project.  Even if he checks in the broken code, your continuous integration environment will pick up the flaw and highlight the broken code.  If you don’t have CI, come and find out how to implement it at my talk!

So please come along to one of my PASS SQL Saturday presentations or watch this space for more on Continuous Integration and the Data Warehouse

Posted by DrJohn with no comments

Supercharge your SSDT database projects using T4 to auto-generate T-SQL code!

There are many techniques that data warehouse developers could learn from other parts of the software industry and automated code generation is one of them. Most database developers have at some point in their careers written T-SQL code to query the SQL Server catalog views to automate the generation of some SQL code. However, such approaches always suffer from the same drawback: they are working with a materialized copy of the database you are developing, not the up-to-the-minute actual code you are working with within SQL Server Data Tools (SSDT).

Introducing Text Template Transformation Toolkit

The Text Template Transformation Toolkit (T4) is easy to overlook because it is baked into the heart of your favourite development tool SQL Server Data Tools (SSDT). First released in Visual Studio 2005, the Text Template Transformation Toolkit has been the standard code-generation technique for all application developers. One of the key reasons T4 has not been widely adopted by the database community is that there are precious few examples out there about using T4 to generate T-SQL code and the official Microsoft documentation uses HTML or C# code generation in all their examples (yuk!). Also when you open Visual Studio, T4 does not jump out and grab you. You need to know where to look.

Why use T4 Templates?

Essentially T4 Templates can be used anywhere in your SSDT project where you need to generate multiple database objects based on other database objects such as tables. In the context of data warehouse development, T4 Templates are particularly useful for generating MERGE statements, triggers and history tables (i.e. tables to record changes to data in other tables).

As an example of their practical use, let’s take a real-world scenario. Many financial institutions have a regulatory requirement to record every change to data that appears in the data warehouse so they know how a report looked like at any point in time. A simple but effective solution to this requirement is to create a set of history tables which have exactly the same structure as the data warehouse dimension or fact table but with an extra column which records the date the data was changed (i.e. the effective date). An update trigger is then created on the source table which populates the history table every time the source table is updated. In this situation two new database objects need to be created for each source table – a history table and a matching update trigger. Of course, these two objects can be created manually, but imagine doing that for 200 source tables; that is 400 objects you need to code by hand! This is where T4 Templates come in. Using a T4 Template containing only a few lines of code, you can easily generate all the history tables or update triggers which are automatically added to your SSDT project as a SQL file. Even better, when you add another source table, you simply run the T4 Template again and it will generate the matching history table and update trigger. This is precisely the scenario I have implemented in the code example provided with this blog.

There are three ways you could implement T4 Templates:

  1. Use ADO.NET to query the SQL Server catalog views on a materialized (deployed) copy of the database
  2. Use the Data-tier Application Framework (DACFx) to query the content of the DACPAC generated by SSDT on your last build
  3. Use the Data-tier Application Framework (DACFx) to query the in-memory content of your SSDT project

The problem with the first two approaches is that the schema is out-of-date, albeit by minutes. You want to be dealing with the code you are developing now. This is why I chose the final method for coding up my T4 Templates provided alongside this post. It gives you access to the in-memory model of your database, not some old out-of-date copy.

Where do T4 Templates appear within SSDT?

The following screenshot of a SSDT solution shows that each T4 Template consists of two objects. The T4 Template itself which has a .tt suffix and the corresponding generated .sql code file which is presented below. The .sql code file generated by the T4 Template becomes part of the solution and will be compiled into your DACPAC when you build. During the build process, the .tt file is ignored – only the generated .sql code file is compiled.

In the example solution, I have placed all the T4 Templates within one folder. The structure is entirely up to you. However, when referencing include files, keeping everything in one folder makes life much easier.


So, for each of the 26 dimension and fact tables in the Adventure Works DW database, as per the requirements outlined above, I want to create a set of history tables and matching update triggers using a single T4 Template. So let’s review the code in the T4 Template file.


As you can see, the T4 Template mostly consists of T-SQL code with which you are very familiar. The code between the angle brackets is C# code handling the iteration over the database model and returning the names of the objects found. It is a bit like old ASP or modern day BIML. Let’s look at each line of code to see what they are doing.


This is a T4 include directive which includes the file which holds the library of common functions I have written which make working with the Data-tier Application Framework (DACFx) much easier.


The first thing we do is get a handle on the in-memory database model (i.e. the content of your SSDT project) by calling GetInMemoryDatabaseModel() which is one of the functions I have written as part of the common function library in the include file. This line obtains a reference to the in-memory database model (i.e. the content of your SSDT project) which is held by an instance of the TSqlModel class which is part of the Data-tier Application Framework (DACFx). Note that you should always use a using statement when dealing with a DACFx TSqlModel class as this will automatically dispose of the model and release the resources used once your template has run.


The call to GetAllTablesInSchemaWithPrefixes() requests all the tables within the dbo schema which have either the prefix Fact or Dim. In the case of the AdventureWorksDW database, this function returns all 24 dimension and fact tables.

This is the first line of T-SQL where we dynamically create the name of the new table based on the name of the source table, but in the History schema.


Next we iterate through the list of columns in the source table which we obtain by a call to the DACFx function GetReferenced() and we then add them into the new table definition by calling the GetColumnDefinition() function which returns a string containing the T-SQL for the column definition.


We finish our T-SQL statement by adding a standard column to the table which records when the history table record was written and completing the CREATE TABLE statement by providing a FILEGROUP name upon which all the new history tables will be stored. And that is it! Pretty straight forward and easy to follow. But that is mostly because I have done all the hard work in the include file which contains my set of common functions which hide the complexity of DACFx. So when we save our new template or select Run Custom Tool from the Solution Explorer window, the HistoryTables T4 Template will generate a .sql file containing all the history tables. For example, here is part of the generated file:


Download the Sample Code for T-SQL

Rather than provide the code sample as part of this post, I have published the code on CodePlex so that I can document and extend the library of functions properly. So download the sample code from here: T4 Template to auto-generate T-SQL from SSDT database model using DacFx. To use these samples, you will need Visual Studio 2013 which has SQL Server Data Tools built-in, or Visual Studio 2012 with the latest version of SSDT which can be downloaded from here.

T4 Templates in colour

By default, T4 Templates are treated by Visual Studio as normal text files, with no syntax highlighting. If you don’t like this black and white world, you can download one of the many free editors. Two I have tried are:

However, be warned that both editors provide erratic syntax highlighting when the syntax is fundamentally wrong, so if your world goes black and white, check your syntax!

Creating your first T4 Template?

To add a T4 Template to a SSDT solution, select a location within your solution within Solution Explorer in simply click Add Item and select Text Template under Visual Studio Templates.


As you can see in the above dialog, you have two Text Template options. The first gives you pretty much a blank screen which is the best option to select when using my include file. The second option provides a sample T4 Template which uses the content of the DACPAC generated by SSDT on your last build (i.e. option 2 of three ways you could implement T4 Templates listed above). In either case, the first thing you need to do is delete everything and add the following to your template:


Certainly I would recommend having a play with the standard template, but unless you are a strong C# programmer, you will soon get lost in the DacFx model as it is loosely typed and does not give you any guidance as to whether the object is a Table, View, Index, Tigger or anything else as they’re all represented by the one TSqlObject class. Ultimately this makes it hard to navigate the database model in a natural way. In particular I struggled for hours before I figured out how to obtain the details of the foreign key relationships for a table; and even then some of the information is missing (i.e. the name of the source column in the relationship).

Automatically regenerated your code prior to build?

By default, T4 templates do not get regenerated the code prior to a build. Microsoft have documented that auto-regeneration can be configured on their page outlining Design-Time Code Generation by using T4 Text Templates which requires the installation of the Modeling SDK for Microsoft Visual Studio 2013 to work. However, in my experience the transformation fails with the following error:

------ Build started: Project: AdventureWorksDW2014, Configuration: Debug Any CPU ------
Transforming template T4_Templates\
C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v12.0\TextTemplating\Microsoft.TextTemplating.targets(396,5): Error: : A processor named 'SqlModelDirectiveProcessor' could not be found for the directive named 'SqlModelDirective'. The transformation will not be run. . Line=3, Column=4
Done building project "AdventureWorksDW2014.sqlproj" -- FAILED.
========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped ==========

Searching the forums, I find evidence to suggest that T4 templates using DacFx will not run from the command-line as there is some unique code run which the directive processor requires to access the database model.  The same problem seems to occur when you select Transform All T4 Templates from the Build menu in Visual Studio. However, this method claims all is well, yet does not actually transform the templates.

Use SQL Server Data Tools – No excuses

Although SQL Server Data Tools (SSDT) was originally released with SQL Server 2012, it also supports SQL Server versions down to SQL Server 2005, so if you are not already using SQL Server Data Tools, then I would strongly encourage you to do so. I key strength of the new tool is that it allows more effective use of source code control and makes it far easier to implement continuous integration as noted by Keith Schreiner in his blog Auto-deploy and version your SQL Server database with SSDT.

Upgrading is straight forward and lots of ‘project’ artefacts can be removed as outlined here. Given it supports all SQL Server versions back to 2005, moving to the latest desktop development tools is much easier than transitioning your servers and ultimately will allow you to upgrade your code base and servers more efficiently when the time comes. Note that SQL Server Data Tools (SSDT) is built into Visual Studio 2013, so no separate install required. However, always ensure you have the latest version of SQL Server Data Tools for Visual Studio 2012.

T4 Templates - Community Resources

Here are a few interesting resources out there which you will find useful when using T4 Templates to generate T-SQL code.

This blog entry forms part of the backdrop for my presentation at SQL Saturday Cambridge on 12th September 2015 where I will be talking about continuous integration and the data warehouse. Of course, T4 templates will be covered amongst numerous other techniques.

Posted by DrJohn with no comments

Dynamic Partition Creation in SSAS multidimensional using the SSAS Partition Manager

<<Updated blog entry to reflect the new release of SSAS Partition Manager>>

There are plenty of blog entries out there that outline how to dynamically create partitions in SSAS multidimensional databases. However, all of them suffer the same drawback: they always hard-code some part of the logic into the programming language in the form of hard-coded names or hard-coded partition queries. None of them provide a generic solution that you could just pick up and apply to any project. Perhaps this is a reflection of the usual implementation language of choice: SSIS. Let's face it, SSIS is famous for been brittle; packages break when moved to a new environment. They are rarely reusable without modification. Generating SSIS packages from BIML has solved some of these problem, but even BIML does not handle C# script tasks well; it is rather like painting a room through a keyhole!

Like many business intelligence consultants, I take example scripts from project to project, each time extending their functionality a little more or adapting them to meet new requirements. For many years, I have used C# and AMO to create partitions and process cubes. Indeed, I have presented on this at SQLBits.  I do find the AMO object model extremely intuitive and C# much easier to code than doing the equivalent partition creation logic in an SSIS package.

After several iterations, I now have an entirely generic piece of C# code which will work in any situation.  The only configuration required is to change the content of a set of tables and edit two views so they generate the correct MDX member keys to match the date dimension in your cube. Fortunately, this is very simple and very much in the realm of the average business intelligence consultant as it is pure T-SQL.

Once configured the SSAS Partition Manager will add partitions to all cubes in all databases listed in the SsasPartitions view, so it will work out of the box even in multi-cube, multi-database environments.  The application will also delete partitions that are no longer required, thus removing redundant data from the cube.

To illustrate, I have chosen to partition each measure group in the AdventureWorks sample cube by different criteria.  So the Internet Customers measure group is partitioned by year, the Internet Sales partition by quarter, Internet Orders and Reseller Orders partitioned by month, the Reseller Orders partitioned by week.  Finally, the Sales Order measure group is partitioned by a hybrid scheme whereby years 2014 onwards have monthly partitions and prior years are partitioned by quarter.  Of course, all this partitioning is completely over the top for the tiny amount of data held by the AdventureWorks sample cube, but does illustrate the power of the SSAS Partition Manager.

All this is encapsulated in the SsasPartitionDefinitions view which allows you to review your configuration (shown below).


Note that the PartitionStartDate is set to 2011-01-01 for all partition schemes, but this could easily be changed by editing the SsasMeasureGroup table.

The PartitionSliceIsRange column is a powerful new feature of my latest implementation and discussed below.

Partition Elimination

Correctly setting the partition slice property of each partition helps the storage engine eliminate the partitions that do not contain relevant data.  Referred to as partition elimination, this greatly increases the performance of MDX queries as not as much data needs to be read by the SSAS storage engine. This is one of the main reasons why cubes should be partitioned.  However, this does rely on the end-user using the dimension upon which the partitioning is based in their MDX queries.  Partitioning by the date dimension is the typical approach.  But be warned, cubes often contain several date dimensions all playing different roles (e.g. order date, delivery date, ship date etc.), so be careful to base your partitioning strategy on the right date dimension.  I have known situations where the cube designer inadvertently picked the wrong date dimension and so the storage engine could never employ partition elimination.  Big mistake!  Huge!

The problems with Auto-Slice

The auto-slice functionality in SSAS does not always get it right.  During processing, SSAS internally identifies the range of data that is contained in each partition by using the Min and Max DataIDs to set the partition slice.  Unfortunately, DataIDs are assigned during dimension processing as new members are encountered and we have no explicit control over how they are allocated, so because SSAS just looks at the minimum and maximum value of the DataID in the partition, you can end up reading partitions that don’t contain relevant data.  Thus it is always best practice to explicitly assign a value to the partition slice property of each partition.

Setting a valid Partition Slice

You can set the partition slice property of a partition to be a single MDX member, or a set of members.  However, MDX ranges operators (i.e. :) are not allowed.  So, for example, the following two statements work fine:


{[Date].[Calendar].[Date].&[20140101],[Date].[Calendar].[Date].&[20140102],[Date].[Calendar].[Date].&[20140103],[Date].[Calendar].[Date].&[20140104],[Date].[Calendar].[Date].&[20140105],-- etc. --,[Date].[Calendar].[Date].&[20140131]}

Whereas the following range statement will cause the partition processing to fail:

{[Date].[Calendar].[Date].&[20140101] : [Date].[Calendar].[Date].&[20140131]}

Of course the above MDX statements were generated from the Adventure Works cube date dimension.

If you get your partition slice wrong, you will get an error such as:  "Errors in the OLAP storage engine: The restrictions imposed on partition slice where violated".  Note that you will ONLY get such errors if the partition contains over 4096 rows which is the IndexBuildThreshold defined in msmdsrv.ini file.  In the Adventure Works database, partitions rarely get above 5000 rows, so the partition slice setting is ignored.  In reality, the Adventure Works database has such low data volumes there is no need to partition the cube, but then it is just a sample database!

Partition Definition Tables

For many years now, I have used a set of SQL tables to hold the definition of each partitioning scheme required in the cube.  A single view then combines the content of these tables with the date dimension to create a list of partitions that need to be created in the cube.  This partition definition view even generates the MDX required to set the partition slice property correctly.  Thus the SSAS Partition Manager program is entirely generic; it simply connects to the SSAS database and compares the partitions it finds in each measure group with the list of desired partitions in the partition definition view: SsasPartitions

Instead of holding one row per partition in a static table, we get the partition definition view to combine the underlying partition definition tables to generate the list of partitions we need.  A CROSS JOIN to the date dimension simply fills in the gaps.  The view is entirely dynamic, giving you all the partitions you need for today.  Tomorrow the view will add on any new partitions as they are required by the partitioning scheme: monthly, quarterly, yearly.

SsasPartitions view

This view is read by the SSAS Partition Manager program which compares the partitions in the cube with the ones listed by the view.  If the partition does not exist in the cube, then it is created.  Note that the C# program does not update existing partitions.  It simply creates new ones or deletes redundant partitions.

The clever thing about this view is not only how it uses date dimension to generate a list of all partitions up to the present day, but also uses it again to generate the list of keys for MDX ranges.  Note the content of the PartitionSlice column in the case of the Internet Orders measure group. This was generated because the PartitionSliceIsRange flag is set to True (1).


Note that the view is entirely dynamic, giving you all the partitions you need for today.  Tomorrow the view will add on any new partitions as they are required by the partitioning scheme: monthly, quarterly, yearly.  This is because the SsasPartitions view only generates partitions between the PartitionStartDate and today's date.  Unfortunately the AdventureWorks date dimension table ends in 2014, so we never get any new partitions been created as we roll over to the new week, month, quarter or year.  Clearly this would not be the case in a true production cube!

Once the SSAS Partition Manager has been run against the AdventureWorks cube, this is what you see in SSMS:


I provide the T-SQL for these database objects as a SQL Server Data Tools (SSDT) project called SsasPartitionDefinition which is part of the download for the SSAS Partition Manager hosted on Codeplex.  Note that I include DimDate from the AdventureWorksDW2014 database just so my SSDT project compiles correctly.

The Populate_Partition_Definitions.sql file populates the three partition definition tables with the partitioned measure groups from the AdventureWorks cube.  This can easily be adapted to your own cube(s).

Note that this table structure supports many measure groups in many cubes in many databases, so with one run of the SSAS Partition Manager you can create partitions in hundreds of cubes, all with the correct partition slice set!

Configuring the SSAS Partition Manager to work with your project

To get yourself up and running with the SSAS Partition Manager this is all you have to do:

  1. Edit the SsasPartitonManager.exe.Config file to change the database connection string and identify the SsasServerType (i.e. DEV, UAT or PROD).
  2. Change the SsasDateDimWrapper view to work with your own date dimension table or equivalent
  3. Change the SsasPartitionPeriods view so that it contains the correct MDX members for your date dimension. You can also add your own partitioning schemes if you need something special.
  4. Change your OLAP cube project to have template partitions with the where clause set to WHERE 1=0
  5. Finally you may need to change the SsasPartitions view if you have some special requirements for the way the PartitionWhereClause is generated

All this is explained in detail on the following page: Configuring the SSAS Partiton Manager

Full validation logging

The first step taken by the SSAS Partition Manager is to validate your configuration.  Thus it will check that all the measure groups exist in the relevant cubes in the relevant databases.  It even checks the measure groups have a template partition with its where clause set to WHERE 1=0.   All this is logged along with the creation/deletion of each partition.  So check the log files to ensure your initial configuration is correct.

Download from Codeplex

The SSAS Partition Manager is available for download from Codeplex. I would be glad to have your feedback on the project via the project’s discussions tab.

SSAS Partition Manager for both SSAS Tabular Models and SSAS Multidimensional Cubes

SsasPartitionManagerLogoToday I am proud to announce the release of SSAS Partition Manager that can dynamically create partitions in both SSAS Tabular models and SSAS Multidimensional cubes using a wide variety of partitioning schemes (yearly, monthly, weekly etc.).  

What is more, for OLAP cubes it correctly sets the MDX in the PartitionSlice property of the partition so you don't have to rely on SQL Server Analysis Services' auto-slice functionality which has known bugs and limitations.

SSAS Partition Manager is a production ready application which is very easy to configure and can be easily integrated into your deployment scripts and regular data warehouse processing jobs. Indeed, a full set of unit and integration tests has been prepared for the SSAS Partition Manager to ensure the quality of the code.

Deploy and Partition Your Tabular Model or Multidimensional Cube

SSAS Partition Manager provides the ability to both deploy a database (either Tabular or Multidimensional), add the partitions and process the newly deployed database all with one simple command.

Using command-line options you can:

  • Deploy an OLAP cube or Tabular model
  • Create partitions using a wide variety of partitioning schemes
  • Process the newly deployed OLAP or Tabular database using ProcessFull
  • Create partitions in all databases on the server and then process them all using ProcessFull
See SSAS Partition Manager Command-Line Options for further information.

Supports multiple Partitioning Schemes

Each measure group can have its own partitioning scheme and out of the box it supports the following partitioning schemes.
  • Yearly
  • Quarterly
  • Monthly
  • Weekly
  • Daily
  • Hybrid
  • Matrix
Hybrid partitioning schemes are where some years use one scheme and other years use another. For example, previous years could be partitioned by quarter whereas the current year is partitioned by day. Matrix partitioning schemes are where you partition by date and some other dimension.

Easy to configure

The SSAS Partition Manager is an entirely generic piece of C# code which will work in any situation. With a tiny amount of configuration, you can start using the SSAS Partition Manager with your multidimensional cube and/or tabular models very quickly. You should never need to modify the C# code. All configuration is done in T-SQL, so it is very much in the realm of the average business intelligence consultant. No specialist skills required!

The key to understanding the SSAS Partition Manager is to know that it creates partitions based on the content of a view in the SQL database called SsasPartitions which tells it what type of partitions to create and how many. When it connects to the cube or model, it expects to find a template partition which has its where clause set to WHERE 1=0 which it clones to form the new partition. That's it! The SSAS Partition Manager does the rest.

Program Logic

The SSAS Partition Manager starts by reading from its config file the connection string to the SQL Server database and a flag which indicates which server environment to connect to (i.e. DEV, UAT or PROD). It then connects to the SQL Server database and queries the SsasServer table for the name of the SSAS server using the server environment flag. Thus, DEV would connect to your localhost, UAT to your UAT server etc. etc.

The program then connects to the SSAS server using the Analysis Management Objects (AMO) library Microsoft.AnalysisServices.
The SSAS Partition Manager then queries the SsasPartitions view to find out what partitions are needed. This view lists all the partitions in all the measure groups in all the cubes/models in all the databases on the SSAS server. Thus the SSAS Partition Manager can handle a multi-cube/model, multi-database environment very easily.

The SSAS Partition Manager will add partitions to all cubes/models in all databases listed in the SsasPartitions view, so it will work out of the box even in multi-cube, multi-database environments. The SSAS Partition Manager will also delete partitions which are not listed in the SsasPartitions view with the exception of any template partition (i.e. a partition with the word template in its name). Thus you can implement a rolling-partition scheme whereby old data is removed from the cube automatically.

The program logic is comprehensively tested by a full set of unit and integration tests before each release to ensure the quality of the code, so you can rest assured it won't go wrong on your server.

Validation Phase

The SSAS Partition Manager has a validation phase which checks your configuration is correct. So for example, if you misspell the name of a measure group, a warning message will be written to the log. The validation covers every aspect of the configuration, including checking your template partition contains WHERE 1=0, so just check the log file to see if everything is correctly set up in your environment.

Database Objects to support the SSAS Partition Manager

The SSAS Partition Manager relies on the presence of a few key database objects. You can choose which database you put these in yourself. The main thing is that you customize the SsasDateDimWrapper view to work with your own date dimension or equivalent. All this is explained in Configuring the SSAS Partition Manager.

Replacement for OlapPartitionManager

SSAS Partition Manager replaces the OlapPartitionManager which I discussed in previous posts as it is more generic and caters for both Tabular and Multidimensional databases.

So read the documentation and go to the download page and start using it today!

SSAS: Automating OLAP cube deployment, dynamic partitioning and processing

Imagine been able to deploy your OLAP database, dynamically partition the cube and then process the new database all in one step from an .asDatabase file with minimal configuration.  Well now you can!  Today’s release of the SSAS Partition Manager provides a set of features which allow you to do just that.  What is more, for the first time you can easily integrate the build and deployment of OLAP databases into your continuous integration tasks so you can write regression tests that check the figures in your cube.

OLAP cube deployment is usually done using the Analysis Services Deployment Wizard. However, the wizard only deploys the partitions that were part of the original cube design; it will not dynamically add the new partitions required by your partitioning scheme.

The SSAS Partition Manager

I introduced the SSAS Partition Manager in my previous post: Dynamic Partition Creation in SSAS multidimensional using the SSAS Partition Manager generic C# AMO app.  The SSAS Partition Manager allows each measure group to have its own partitioning scheme and out of the box it supports the following partitioning schemes.

  • Yearly
  • Quarterly
  • Monthly
  • Weekly
  • Daily
  • Hybrid
  • Matrix

Hybrid partitioning schemes are where some years use one scheme and other years use another. For example, previous years could be partitioned by quarter whereas the current year is partitioned by day. Matrix partitioning schemes are where you partition by date and some other dimension.  Note that the last two options require some configuration on your part which is all explained on Configuring the SSAS Partition Manager.

Easy to configure

All of this power is easy to configure by changing two SQL views and the content of three tables - well within the realms of the average business intelligence consultant!

Deploying your cube

Today’s release takes the project a step further by allowing you to deploy a fresh copy of your OLAP database, partition all of the cubes and then process the database using one simple command-line such as:

SSASPartitionManager.exe /d AdventureWorks /a "C:\Dev\Samples\AdventureWorks.asdatabase" /p ProcessDeployed

The above command-line will deploy a cube called AdventureWorks using the AdventureWorks.asdatabase OLAP database definition file onto the SSAS server listed in the OlapServer table.  The SSAS Partition Manager will then dynamically add all the partitions listed in the OlapPartitions view.  Finally it will process the new OLAP database.  That’s it!  Dead simple!  Exactly how to set everything up is all explained on Configuring the SSAS Partition Manager.

There is no need for you to delve into the C# code behind the SSAS Partition Manager as all the functionality is easily configurable as explained on Configuring the SSAS Partition Manager.  However, if you do wish to delve a little deeper, you will notice that the new DeployDatabase method is simply a new implementation of some code I released as part of this blog post: SSIS: Deploying OLAP cubes using C# script tasks and AMO

Example command-lines

Deploy a new OLAP database and add partitions:

SSASPartitionManager.exe /d AdventureWorks /a "C:\Dev\Samples\AdventureWorks.asdatabase"

Deploy a new OLAP database, add partitions and process the newly deployed OLAP database:

SSASPartitionManager.exe /d AdventureWorks /a "C:\Dev\Samples\AdventureWorks.asdatabase" /p ProcessDeployed

Deploy a new OLAP database, override the standard connection string to the Data Mart, add partitions and process the newly deployed OLAP database:

SSASPartitionManager.exe /d AdventureWorks /a "C:\Dev\Samples\AdventureWorks.asdatabase" /c "Provider=SQLNCLI11.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW2014" /p ProcessDeployed

Perform a full process on all OLAP databases found in the OlapPartitions view:

SSASPartitionManager.exe /p ProcessAll

All of the arguments are explained on SSAS Partition Manager Command-Line Options

So download your copy and start using the SSAS Partition Manager today!

Posted by DrJohn with 1 comment(s)

SSAS: Utility to check you have the correct data types and sizes in your cube definition

This blog describes a tool I developed which allows you to compare the data types and data sizes found in the cube’s data source view with the data types/sizes of the corresponding dimensional attribute. 

Why is this important?  Well when creating named queries in a cube’s data source view, it is often necessary to use the SQL CAST or CONVERT operation to change the data type to something more appropriate for SSAS.  This is particularly important when your cube is based on an Oracle data source or using custom SQL queries rather than views in the relational database.   The problem with BIDS is that if you change the underlying SQL query, then the size of the data type in the dimension does not update automatically.  This then causes problems during deployment whereby processing the dimension fails because the data in the relational database is wider than that allowed by the dimensional attribute.

In particular, if you use some string manipulation functions provided by SQL Server or Oracle in your queries, you may find that the 10 character string you expect suddenly turns into an 8,000 character monster.  For example, the SQL Server function REPLACE returns column with a width of 8,000 characters.  So if you use this function in the named query in your DSV, you will get a column width of 8,000 characters.  Although the Oracle REPLACE function is far more intelligent, the generated column size could still be way bigger than the maximum length of the data actually in the field.

Now this may not be a problem when prototyping, but in your production cubes you really should clean up this kind of thing as these massive strings will add to processing times and storage space. Similarly, you do not want to forget to change the size of the dimension attribute if your database columns increase in size.

Introducing CheckCubeDataTypes Utiltity

The CheckCubeDataTypes application extracts all the data types and data sizes for all attributes in the cube and compares them to the data types and data sizes in the cube’s data source view.  It then generates an Excel CSV file which contains all this metadata along with a flag indicating if there is a mismatch between the DSV and the dimensional attribute.  Note that the app not only checks all the attribute keys but also the name and value columns for each attribute.
Another benefit of having the metadata held in a CSV text file format is that you can place the file under source code control.  This allows you to compare the metadata of the previous cube release with your new release to highlight problems introduced by new development.

You can download the C# source code from here:

A typical example of the output Excel CSV file is shown below - note that the last column shows a data size mismatch by TRUE appearing in the column

Posted by DrJohn with 2 comment(s)
Filed under: ,

SSAS: Utility to export SQL code from your cube's Data Source View (DSV)

When you are working on a cube, particularly in a multi-person team, it is sometimes necessary to review what changes that have been done to the SQL queries in the cube's data source view (DSV). This can be a problem as the SQL editor in the DSV is not the best interface to review code. Now of course you can cut and paste the SQL into SSMS, but you have to do each query one-by-one. What is worse your DBA is unlikely to have BIDS installed, so you will have to manually export all the SQL yourself and send him the files. To make it easy to get hold of the SQL in a Data Source View, I developed a C# utility which connects to an OLAP database and uses Analysis Services Management Objects (AMO) to obtain and export all the SQL to a series of files. The added benefit of this approach is that these SQL files can be placed under source code control which means the DBA can easily compare one version with another.

The Trick

When I came to implement this utility, I quickly found that the AMO API does not give direct access to anything useful about the tables in the data source view. Iterating through the DSVs and tables is easy, but getting to the SQL proved to be much harder. My Google searches returned little of value, so I took a look at the idea of using the XmlDom to open the DSV’s XML and obtaining the SQL from that. This is when the breakthrough happened. Inspecting the DSV’s XML I saw the things I was interested in were called

  • TableType
  • DbTableName
  • FriendlyName
  • QueryDefinition

Searching Google for FriendlyName returned this page: Programming AMO Fundamental Objects which hinted at the fact that I could use something called ExtendedProperties to obtain these XML attributes. This simplified my code tremendously to make the implementation almost trivial. So here is my code with appropriate comments. The full solution can be downloaded from here:


using System;
using System.Data;
using System.IO;
using Microsoft.AnalysisServices;

... class code removed for clarity

// connect to the OLAP server
Server olapServer = new Server();
if (olapServer != null)
// connected to server ok, so obtain reference to the OLAP database
Database olapDatabase = olapServer.Databases.FindByName(config.olapDatabaseName);
if (olapDatabase != null)
Console.WriteLine(string.Format("Succesfully connected to '{0}' on '{1}'",
// export SQL from each data source view (usually only one, but can be many!)
foreach (DataSourceView dsv in olapDatabase.DataSourceViews)
Console.WriteLine(string.Format("Exporting SQL from DSV '{0}'", dsv.Name));
// for each table in the DSV, export the SQL in a file
foreach (DataTable dt in dsv.Schema.Tables)

Console.WriteLine(string.Format("Exporting SQL from table '{0}'", dt.TableName));

// get name of the table in the DSV
// use the FriendlyName as the user inputs this and therefore has control of it
string queryName = dt.ExtendedProperties["FriendlyName"].ToString().Replace(" ", "_");
string sqlFilePath = Path.Combine(targetDir.FullName, queryName + ".sql");

// delete the sql file if it exists
... file deletion code removed for clarity

// write out the SQL to a file
if (dt.ExtendedProperties["TableType"].ToString() == "View")

File.WriteAllText(sqlFilePath, dt.ExtendedProperties["QueryDefinition"].ToString());

if (dt.ExtendedProperties["TableType"].ToString() == "Table")
File.WriteAllText(sqlFilePath, dt.ExtendedProperties["DbTableName"].ToString());




Console.WriteLine(string.Format("Successfully written out SQL scripts to '{0}'", targetDir.FullName));




Of course, if you are following industry best practice, you should be basing your cube on a series of views. This will mean that this utility will be of limited practical value unless of course you are inheriting a project and want to check if someone did the implementation correctly.

Posted by DrJohn with 1 comment(s)
Filed under: ,

Building a Dynamic OLAP Environment at #SQLbits8

On Saturday I attended the SQLBits conference in sunny Brighton. Great conference, well organized with lots of great speakers presenting high quality content.

For me the highlight was meeting many of the senior guys from the Redmond SQL Server product team including Richard Tkachuk, Mark Souza and Ross Mistry.

Marco Russo presented a great session called Analysis Services Advanced Best Practice. Ok so I have read his book and implement most of the best practices already, but it was great to hear it all from the author himself!

I received very positive feedback on my own session Building a Dynamic OLAP Environment which I had discussed in a previous post. Sometime soon, the slide deck will be made available on the SQLBits web site along with the video of my presentation. However, if you are eager to get your hands on it now, it can be downloaded from here:

All in all, a great conference! I am looking forward to the next already.

Posted by DrJohn with no comments
Filed under: ,

The most dangerous SQL Script in the world!

In my last blog entry, I outlined how to automate SQL Server database builds from concatenated SQL Scripts. However, I did not mention how I ensure the database is clean before I rebuild it. Clearly a simple DROP/CREATE DATABASE command would suffice; but you may not have permission to execute such commands, especially in a corporate environment controlled by a centralised DBA team. However, you should at least have database owner permissions on the development database so you can actually do your job! Then you can employ my universal "drop all" script which will clear down your database before you run your SQL Scripts to rebuild all the database objects.

Why start with a clean database?

During the development process, it is all too easy to leave old objects hanging around in the database which can have unforeseen consequences. For example, when you rename a table you may forget to delete the old table and change all the related views to use the new table. Clearly this will mean an end-user querying the views will get the wrong data and your reputation will take a nose dive as a result!

Starting with a clean, empty database and then building all your database objects using SQL Scripts using the technique outlined in my previous blog means you know exactly what you have in your database. The database can then be repopulated using SSIS and bingo; you have a data mart "to go".

My universal "drop all" SQL Script

To ensure you start with a clean database run my universal "drop all" script which you can download from here:

By using the database catalog views, the script finds and drops all of the following database objects:

  1. Foreign key relationships
  2. Stored procedures
  3. Triggers
  4. Database triggers
  6. Tables
  7. Functions
  8. Partition schemes
  9. Partition functions
  10. XML Schema Collections
  11. Schemas
  12. Types
  13. Service broker services
  14. Service broker queues
  15. Service broker contracts
  16. Service broker message types
  17. SQLCLR assemblies

There are two optional sections to the script: drop users and drop roles. You may use these at your peril, particularly as you may well remove your own permissions!

Note that the script has a verbose mode which displays the SQL commands it is executing. This can be switched on by setting @debug=1.

Running this script against one of the system databases is certainly not recommended! So I advise you to keep a USE database statement at the top of the file.

Good luck and be careful!!

Posted by DrJohn with no comments
Filed under:
More Posts Next page »