2 Minute Video on BISM Normalizer

BISM Normalizer is 3 Years Old!

I have given away BISM Normalizer for 3 years.  I launched it on Christmas Day 2011 (way before SQL Server 2012 hit RTM).

Check out the brand new BISM Normalizer website!

I’d like to say a big thank you to all who have used BISM Normalizer, provided feedback, and especially those who educated the SQL community through blog posts, publications, social media or otherwise.

I have decided to start charging license fees for BISM Normalizer.  This was not the plan from the beginning; my circumstances have changed.  Version 2.0.1 is a 20-day evaluation version, which can be purchased for $239.  This includes 1 year of support by e-mail, and updates/fixes to the major version.  See the Purchase page for more information.

How do I justify $239 per license?  Here are my price-determination factors:

  • Comparative products from bigger companies, such as database compare for relational databases, are more than twice the price for the base version, normally not including features such as source-control integration, which cost extra.
  • There is no competition in the SSAS database compare space (at least nothing that is usable). The relational-database compare products alluded to above, for example, compete in a crowded space.
  • BISM Normalizer is for corporate BI projects, not self-service BI users.  If it worked directly with Power Pivot, would target higher sales volume at a lower price point. It is geared primarily at companies and organizations, rather than individuals.
  • Not a big product-development company with marketing budget, sales team, brand name … Built up brand by giving it away for 3 years.

Given these factors, I hope you agree that $239 is a great price!   I hope you continue to gain value from my 3rd child, BISM Normalizer.

Merry Christmas – or Happy Holidays!

BISM Normalizer: Version 2.0 Released!

Easily the biggest release of BISM Normalizer since its launch almost 3 years ago.

Enhancements in Version 2.0.0.0

  • BISM Normalizer has been migrated to a VSPackage; it is no longer a VS add-in.
    • Add-ins are being deprecated in VS.  In the CTP of VS 2015, the Add-in Manager no longer exists.
    • VSPackages provide richer functionality than add-ins.  Microsoft project templates (e.g. C# projects) are built using VSPackages.
  • BISM Normalizer is now a custom editor.  This provides the following benefits.
    • Multiple comparisons are supported at the same time.
    • Comparisons can be saved to a .bsmn file.
      • Skip selections, connections and options are retained when reopened.  This means it is not necessary to make the same manual selections when deploying new versions of the same tabular model.
      • .bsmn files can be added to tabular projects using the VS New Item dialog.
      • In a future release (not 2.0), the .bsmn file will parameterize command-line deployments using BISM Normalizer.
  • VSIX deployment direct from the VS Gallery.
    • Notifications of new releases in the VS Notification tool window.
    • Upgrade to new versions from within VS in the Tools > Extensions and Updates window.
    • No longer necessary to uninstall BISM Normalizer every time upgrade to a new version.  It will upgrade as part of the new installation.
  • Numerous usability and look-and-feel enhancements.
    • Too many to document here.  Maybe another day when I have a few spare hours.

Notes

  • Remember to uninstall the previous addin version of BISM Normalizer from Control Panel > Programs and Features before installing the new version.  If you see the big old BISM Normalizer toolbar, you are using the old version.

Update 11/14/2014: enhancements/fixes in 2.0.0.5

  • Full support for VS 2010 & 2012 (in addition to 2013).  Also supports VS Community 2013.

Update 12/19/2014: enhancements/fixes in 2.0.1.0

  • 20-day evaluation version

Update 1/23/2015: enhancements/fixes in 2.0.1.1

  • Fix for creating database permission with the same (internal) name as an existing one.

Piecemeal Deployment of Tabular Models

BISM Normalizer allows piecemeal deployment of individual features through dev, test, prod environments. For example, we can 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.

Easy deployment that is more responsive to the customer – especially in an agile-development scenario.

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

Experiences & Tabular Tips

A project I worked on for some time went live months ago. My role as “tech lead” had a foot in many camps: solution architecture, business analysis, scrum master, data modeling, BI evangelism and BI development. We built a data warehouse of over half a terabyte with a handful of tables edging half a billion rows. It is a major component of the data-services layer for the organization. Thousands of applications/reports (primarily Excel/Access apps on network shares) that were tightly-coupled with systems of record were either replaced by a corporate BI & reporting solution, or rebuilt to consume data from reusable data services. We retired other competing “data layers” (legacy DW and a data-service + BI layer with a mix of technologies including multidimensional cubes). We also enhanced limited MDM capabilities resulting in greater business ownership of master data, rather than using the MDM repository as an unnecessary duplication of data. A data-governance model has been established to ensure growth and longevity of the solution.

As is the case with many enterprise organizations that have built a culture of encouraging (or even training) business users to query corporate systems of record directly, such a project required a fundamental change in business processes, and faced some resistance. It was not enough to tell stakeholders that a reusable data layer would provide a “single version of the truth”, promote consistent decisions across the organization, lower the total cost of ownership due to addressing inefficiencies where the same business calculations are being applied in hundreds of places, inconsistent terminology, etc. What forced the issue was that the biggest system of record in the organization (containing roughly 50% of the data anyone is interested in) was being replaced. Given that thousands of apps were tightly coupled with this SoR and therefore exposed, they would all break and the business was at risk of grinding to a halt. This was enough to convince key stakeholders of the benefits of a reusable data layer coupled with governance model, in part to protect them from this situation in the future. So we went live alongside this major SoR migration, replacing or integrating countless apps, cleaning up the architecture of the organization, introducing hundreds of users to business intelligence as a concept, and transforming the way data is accessed for decision making. I personally trained over 200 users on how to use the new BI layer to access corporate data with minimal technical skills. My motto throughout has been “clicky clicky draggy droppy” data access. I think it’s fair to say the project was a success.

Focusing on SSAS …

We started with multidimensional and had performance issues. Despite optimization, multidimensional struggled. We moved to tabular. I did not find it difficult to get 50 to 100 GB of memory on the servers, which was more than enough for tabular when limited to the data set required for BI. However, as pointed out by others in the blogosphere, the better performance of tabular did not come for free …

Personally, I did not find limited functionality in tabular to be a major issue. As I mentioned in my Multidimensional or Tabular post, most functionality can be delivered even if using a “workaround” (often DAX instead of decorating the metadata of the model in multidimensional). True show stoppers like unary operators and write back were not required.

The bigger issues were with the tabular development experience. The tabular-model designer inherited from Power Pivot is sluggish for models with lots of tables. The Excel-like DAX formula bar is, to put it politely, annoying. Without saying anything too controversial, MS corporate BI has played second fiddle lately.

Here are a couple of tips/techniques. This is not a comprehensive list.

Author measures in a query-authoring tool and copy/paste them into the tabular model

Authoring measures in the tabular model designer is not effective because the workspace database contains hardly any data. Unfortunately, my laptop doesn’t have 50 GB of memory. Setting the workspace database to point at the Dev instance is not an option because it would exacerbate the sluggish development experience, not to mention screwing up partitions on the Dev instance, etc. The workspace database is basically redundant in an enterprise development scenario.

If you prefer DAX queries, author measures in DAX Studio (querying the Dev instance of course) and copy/paste the measure definitions into the tabular model designer when you’re done.

If you prefer MDX queries, author measures in Management Studio and copy/paste the measure definitions into the tabular model designer when you’re done. You can author DAX measures in the WITH clause of an MDX query. I personally do this a lot because I find it less typing for an MDX SELECT statement than a DAX EVALUATE statement. Also I can refer to user hierarchies in the query (if not the measures).

For large models, use a subset in the tabular model designer

The tabular model designer inherited from Power Pivot may work well for a typical Power Pivot model. This is not always the case in SSAS tabular. The largest of the models I work with has about 80 tables, many with lots of columns. There are facts at various levels of granularity reducing the denormalization opportunity. Including internal measures not visible to users, there are over 800 measures (thankfully organized using display folders)! Due to business requirements to report measures side by side, I cannot split it into multiple tabular models.

Even in Manual Calculations mode (Model > Calculation Options > Manual Calculation), this model freezes the IDE for 10 to 15 seconds every time I do anything trivial. For significant modeling enhancements, this is unworkable. I simply don’t have the patience to sit there while the IDE is “Finishing …” all the time.

For significant enhancements, use BISM Normalizer to export a subset of the model to a temporary tabular project. Do the enhancements in there where there is a responsive development experience, and then BISM Normalizer the enhancements back to the main model when you’re done. This saves time and minimizes hair loss.

Place calculation logic at the appropriate level of the architecture

The tabular storage engine is clearly much faster than that of multidimensional. The same cannot be said for the formula engine.

This may sound obvious, but place calculation logic at the appropriate level of the architecture. Some calculations (e.g. traditional aggregated level calcs) are much better suited to a cube/tabular model than the ETL. In other cases there may be a considerable performance gain by performing a calculation in the ETL rather than the tabular model, thereby avoiding the formula engine.

This also promotes reusability of business logic because non-BI applications that submit SQL instead of MDX/DAX can leverage the same calculations.

Follow

Get every new post delivered to your Inbox.