Another BISM Normalizer Use Case

As I have said for (almost) 3 years, the use cases for BISM Normalizer are more than just helping migrate from Power Pivot to SSAS. Apart from merging code bases, and deployment of large, partitioned tabular models, etc., I probably have not emphasized the ease of deployment through dev, test, prod environments from Visual Studio. Let’s be honest; while they may not admit it, I suspect a large proportion of projects deploy from Visual Studio using right click > Deploy in Solution Explorer.

BISM Normalizer allows piecemeal deployment of individual features through environments from Visual Studio, which is valuable for many projects. Right click > Deploy in Solution Explorer obviously doesn’t achieve this. Apart from being an all-or-nothing deployment technique, it doesn’t take the state of each environment into account.

For example, BISM Normalizer makes it possible to pick limited particular features that have been signed off in a user-acceptance-testing environment and deploy to production – even if the SSAS objects required have been modified in the lower environments.

Benefit: easy deployment that is more responsive to the customer.

BISM Normalizer: Version 1.3.13 Released!

Download it from the BISM Normalizer Visual Studio Gallery page.

Enhancements in Version 1.3.13.1

  • Expiry date extended to December 31st 2014. It is unclear what will happen to BISM Normalizer after this time.
  • Tested with SQL 2012 SP2.

Update 8/22/2014: fixes in 1.3.13.6

  • Handles ‘]’ characters in measure names. Read Visual Studio Gallery page Q&A section for details.

SSAS Locking: CommitTimeout and ForceCommitTimeout

There are already plenty of good posts out there on this topic:

As mentioned by Andrew Calvett, it is possible to set the CommitTimeout for a Process command.  Here is how to do it.

<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
  <Command>

    <Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
      <Type>ProcessFull</Type>
      <Object>
        <DatabaseID>AdventureWorks</DatabaseID>
        <DimensionID>Date_d2c7ec3d-c72c-435d-bd43-8283714cc2dd</DimensionID>
      </Object>
    </Process>

  </Command>
  <Properties>
    <PropertyList>
       <CommitTimeout>20000</CommitTimeout>
   </PropertyList>
  </Properties>
</Execute>

 

Setting CommitTimeout to 20 seconds (20000 milliseconds) means it will kick in before the server-level default ForceCommitTimeout of 30 seconds. To try this out, run the following query, which takes about 50 seconds on my laptop.  As soon as the query starts running, execute the process command.  It should rollback the process command and allow the query to run to completion.

DEFINE
MEASURE 'Date'[Bad Measure] =
   COUNTROWS(GENERATE(ALL('Internet Sales'), VALUES('Date'[Day Of Year])))
EVALUATE
ADDCOLUMNS(
   VALUES('Date'[Calendar Year])
   ,"Bad Measure", [Bad Measure]
)

 

Change CommitTimeout to 40 seconds (40000 milliseconds) and the default ForceCommitTimeout of 30 seconds will kick in instead.  The query will fail – instead of the Process command – “because of locking conflicts”.

BISM Normalizer: Version 1.3.12 Released!

Download it from the BISM Normalizer Visual Studio Gallery page.

Enhancements in 1.3.12.3

  • Support for Visual Studio 2013 and SQL Server 2014. To run Visual Studio 2010/2012/2013 on the same machine, point them at the same local Addin folder in Visual Studio options.
  • Using a new (very simple) InstallShield installer. This will hopefully resolve the issue with the installation path pointing at a remote Addin folder based on target machine settings. I am unable to test this.

Update 4/4/2014: fixes in 1.3.12.4

  • Fix for BISM Normalizer window re-initializes when loses focus (Visual Studio 2012 and 2013 only).

Video of BISM Normalizer

Revised video of BISM Normalizer.

BISM Normalizer: Version 1.3.11 Released!

Download it from the BISM Normalizer Visual Studio Gallery page.

Enhancements in Version 1.3.11.1

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).

BISM Normalizer Options

BISM Normalizer is 2 Years Old!

BISM Normalizer is 2 years old today. I launched it on Christmas Day 2011 (way before SQL Server 2012 hit RTM). It’s arrival was marked by a wise man writing a blog post.

Having done various presentations about BISM Normalizer at the PASS Community Summit, SQLBits and other events, I gradually became more aggressive in selling its benefits – especially challenging the SSAS Deployment Wizard.

I have considered opening up the source code on Codeplex. I also had discussions about selling the source code (the executable is obfuscated), but they didn’t work out. I don’t know the future of BISM Normalizer, but the most likely outcome is I continue to give it away for free on the VS Gallery as I have done till now. The benefits of giving it away (for me) are that it raises my profile both as a consultant and in the SQL community.

So consider BISM Normalizer a Christmas gift of enterprise code management for Analysis Services, facilitated deployment, and promotion of a “single version of the truth” for business definitions covered by BI models.

Merry Christmas – or Happy Holidays – to you and your family!

Follow

Get every new post delivered to your Inbox.