Video of BISM Normalizer
Video of BISM Normalizer 1.
BISM Normalizer: Version 1.3.11 Released!
Download it from the BISM Normalizer Visual Studio Gallery page.
Enhancements in Version 18.104.22.168
Support for tabular objects created by BIDS Helper.
- Actions are first-class objects listed in the differences grid.
- Display folders are attributes of other objects (tables with columns/hierarchies, measures) and visible in those objects’ definitions.
- Translations are attributes of other objects (tables with columns/hierarchies, measures, perspectives, actions) and visible in those objects’ definitions.
It is necessary to enable the features in the BISM Normalizer options dialog (accessible from the Connections dialog).
Deploy Tabular Models and Retain Partitions
Kasper de Jonge asked a question on Twitter that I would like to answer here to a) give it more exposure, and b) I pretty quickly ran out of 140 characters in my Twitter reply.
I initially tweeted: Problem when deploy tabular models to Dev server from SSDT & lose dynamic partitions? Deploy with BISM Normalizer & retain partitions
Kasper tweeted: Hey Christian, what do you mean by dynamic partitions?
And here is my response …
We often partition the large tables in a tabular model to speed up processing times (as I’m sure you know). For example, we might partition by month and then process only the most recent couple of months every night – which is a lot quicker than processing the whole table. Normally this is done in AMO code that is called from a SSIS package – so it can be managed by the ETL to increment/drop partitions, etc. The version of the model in source control does not contain the partition objects.
While developing in SSDT, we frequently deploy to a dev server (obviously not test/production). This is necessary because all the dev reports are pointing at the server – not the workspace database on the developer machine. If the dev ETL has built partitions, they are lost upon deployment from SSDT (using right-click deploy) – and they take a long time to rebuild/reprocess.
When creating a BISM Normalizer difference comparison, we have the option to “include partitions in table definitions”. If we leave this unchecked, partitions will not be considered when comparing tables. BISM Normalizer will treat such tables as equal and will not mess with them – thereby avoiding reprocessing.
The SSAS Deployment Wizard is the only other deployment method for tabular models that supports “retain partitions” functionality. However, you would not use it to deploy from SSDT to the dev server when making a change to a DAX calc or something. BIDS Helper‘s Deploy MDX Script feature serves a similar purpose for multidimensional.
For regular Test/Production deployments, we can use the deployment wizard. Alternatively, we can use BISM Normalizer to (more easily) create an xmla script for the release to apply on Test/Production.
Another use case is bug fixes and partial deployments. BISM Normalizer can create a script to apply on Test/Production – which updates only the calculation(s) that need to be fixed without a full release cycle – and does not mess with everything else that is already there. The deployment wizard only supports “all-or-nothing” deployments, which are not appropriate for bug fixes and partial deployments.
Why not BISM Normalizer work directly with PowerPivot workbooks?
Gerhard Brueckl asked a question on the BISM Normalizer page Q&A section that I would like to answer here to give it more exposure.
Gerhard wrote …
first of all – thanks for that awesome tool!
Is it somehow possible to compare a Server/Project to a PowerPivot Workbook and vice-versa?
Office 2013 introduced a new object model for PowerPivot where you can access the PowerPivot – is it possible to extend BISM Normalizer to make use of this?
My response …
Thanks for the feedback! Others have asked the same question including Grant Paisley.
I would love to have BISM Normalizer work directly with PowerPivot. Not only allow selection of an Excel file to act as a source/target model, but also have an Excel Addin version of BISM Normalizer to support non-Visual Studio users.
I agree there is a strong use case for this. PowerPivot enables users to easily pull data from uncommon sources – and often mash it up with corporate data. So inheriting (some or all of) another tabular model’s metadata would be useful to say the least.
PowerPivot now has the API you mentioned, but it would be much easier if Microsoft provided a single API for both Analysis Services and PowerPivot. The easiest way for me would be if Microsoft allowed access to the PowerPivot AMO interface. Instead, Microsoft has chosen to keep this interface private.
I built an object model on top of AMO for BISM Normalizer. It contains classes for all the objects that BISM Normalizer displays on the differences grid and abstracts calls to AMO. But even so, under the abstraction layer, BISM Normalizer basically just copies AMO objects from source to target. It does not need to rebuild them, which would be required to get them from AMO to DataModel OM in Excel 2013 and vice versa.
I raised this Connect item for this purpose. Feel free to vote for it.
BISM Normalizer: Business Case and Next Steps
The individual use cases for BISM Normalizer are documented on the Visual Studio Gallery page.
The traditional sweet spot for relational-database compare tools is scripting changes for deployment through development, test and production environments. BISM Normalizer can be used like this for Analysis Services tabular models. It is an easy way of scripting changes that supports selection/exclusion of objects for deployment. It also supports the “retain partitions” functionality to avoid reprocessing dynamically partitioned tables.
BISM Normalizer is also valuable in simply reusing object definitions between tabular models. In multidimensional, we can at least share dimensions between cubes – albeit in the same database. Multidimensional also supports “linked cubes” – which I have to say never worked well at all. In tabular, we have none of these options, but we can use BISM Normalizer to import parts of models into other models.
BISM Normalizer can standardize models in the enterprise. The advent of self-service BI makes this particularly relevant. As the in-memory database tipping point approaches and therefore both forms of tabular models (Analysis Services and PowerPivot) become more prevalent, it is inevitable that a plethora of models will emerge throughout the organization. While self-service BI adds huge value by accelerating delivery, it has to be monitored to avoid multiple versions of the truth – which can be expensive to manage and often results in reinventing the wheel. Traditional corporate BI is also prone to some level of chaos that can benefit from standardized business definitions throughout the enterprise.
Here is the business challenge: support agility promoted by self-service BI, while bridging the gap to corporate BI. Avoid self-service and corporate BI being isolated from one another – and therefore competing with each other. Instead, strike a balance that meets both requirements by complementing each other. BISM Normalizer makes this vision easier to achieve. Integration and standardization of models across the two camps is easy.
I built BISM Normalizer in my spare time as a hobby.
Special thanks to
These are without doubt the leading, best books on the market for Analysis Services Tabular. To have them both endorse BISM Normalizer is brilliant. Thanks again!