BISM Normalizer: Version 3.2 Released!

Download it from the Visual Studio Gallery.

Enhancements in Version 3.2

Command-line interface supports Visual Studio projects as source/target

Despite Visual Studio not being loaded (or required) in command-line mode, projects can now act as the source/target for comparisons (in addition to databases).

This is useful for automated builds, which get the latest version of files from source control.

The project path is stored in the BSMN file. It can be changed by editing the file.

<ComparisonInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ConnectionInfoSource>
    <UseProject>true</UseProject>
    <ServerName>localhost</ServerName>
    <DatabaseName>Tabular1200_wade_21527ea8-74fd-4bb6-b2e2-3d1cd3f70063</DatabaseName>
    <ProjectName>Tabular1200</ProjectName>
    <ProjectFile>C:\Users\wade\Documents\Visual Studio 2015\Projects\TabularProject1\TabularProject1.smproj</ProjectFile>
  </ConnectionInfoSource>
  ...


Support for Visual Studio configuration manager

Default values for the target server and database are based on the selected source project’s deployment properties, and the current Visual Studio configuration. Selecting a different source project (or re-selecting the current one) resets the defaults.

VSConfigurations

BISM Normalizer: Version 3.1 Released!

Download it from the Visual Studio Gallery.

Enhancements in Version 3.1

Command-line execution

Run BISM Normalizer from the command line passing the BSMN file as an argument. This allows integration with automated builds.

Syntax
BismNormalizer.exe BsmnFile [/Log:LogFile] [/Script:ScriptFile]
   [/Skip:{MissingInSource | MissingInTarget | DifferentDefinitions}]

 

Arguments

BsmnFile

Full path to the .bsmn file.

/Log:LogFile

All messages are output to LogFile. If the log file already exists, the contents will be replaced.

/Script:ScriptFile

Does not perform actual update to target database; instead, a deployment script is generated and stored to ScriptFile.

/Skip:{MissingInSource | MissingInTarget | DifferentDefinitions}

Skip all objects that are missing in source, missing in target, or with different definitions. This is in addition to the skip actions already defined in the BSMN file.

It is possible to pass a comma-separated list of multiple skip options. For example, “/Skip:MissingInSource,DifferentDefinitions” will skip all objects that are missing in source and those with different definitions.

Automated merging of branches

The /Skip argument can be used for automated merging of branches. For example, by setting /Skip:MissingInSource, it is possible to create/update new/modified objects in a primary branch, without deleting existing ones.

Examples

The following example updates the target database, logging progress and error messages for later review.

BismNormalizer.exe TabularCompare1.bsmn /Log:log.txt

 

The following example does not update the target database. Instead, a script is generated and progress is logged.

BismNormalizer.exe TabularCompare1.bsmn /Log:log.txt /Script:script.xmla

 

The following example updates the target database and progress is logged. None of the objects missing in source are deleted.

BismNormalizer.exe TabularCompare1.bsmn /Log:log.txt /Skip:MissingInSource

 

Passwords and processing

BISM Normalizer in command-line mode does not automatically set passwords for impersonated accounts. They need to be set separately, either manually or securely as part of a build. Processing also needs to be set up separately if required.

Executable location

BISM Normalizer currently only supports standard VSIX deployment from the Visual Studio Gallery. BismNormalizer.exe is located in the extension directory, which looks like “C:\Users\XXX\AppData\Local\Microsoft\VisualStudio\14.0\Extensions\XXX\”, and is shown in the log file produced by the VSIX installer (link available on the installation complete dialog). The executables can be copied to another location using XCOPY deployment.


Translations

To enable this feature, ensure the include cultures check box is checked.

Cultures.png

Comparing cultures works similarly to comparing perspectives. Both have a merge option. This allows existing translations in the target culture to be retained. See this post for more info on merging perspectives and translations.

The JSON order of translations in the source/target text boxes is honored from the BIM file/database definition. In some cases they may have different order, but still considered equal since the JSON order doesn’t affect translation functionality.

Merge Perspectives/Translations in BISM Normalizer

When merging models, it may be useful to update selections for a perspective without losing existing selections. For example, if merging a Power Pivot migrated prototype into a corporate model, we normally don’t want to lose existing selections.

The same applies to translations for a culture.

Merge perspectives

Consider the following comparison taken with the merge perspectives option checked.

Perspective comparison

Note that the differences in the target object definition are highlighted in grey instead of the normal red. This is to indicate that they will not be removed.

After applying the update (assuming FactRate is still in the target database) and re-running the comparison, the following definitions are displayed.

After update

For the purpose of the comparison, the definitions are considered the same because an update would have no effect.

However, if the merge perspectives option is unchecked and the comparison is re-run, the definitions will be be considered different as shown below.

Different definitions

A subsequent update will remove selections for the FactRate table.

This works basically the same way for cultures. When the merge translations checkbox is checked, existing translations will not be removed. Different translations for an object property that is both in the source and target cultures will be overwritten.

BISM Normalizer: Version 3.0 Released for SSAS 2016!!!

This is a major new version of BISM Normalizer. It can be downloaded from a different Visual Studio Gallery page. It will run for free till June 30th 2016.

Visual Studio 2010/2012/2013 (SQL Server 2012/2014) are not supported by BISM Normalizer 3. For these, you can still use BISM Normalizer 2.  See the Purchase page for more info on version compatibility.

Enhancements in Version 3.0

Support for SQL Server Analysis Services 2016 and Visual Studio 2015

SSAS compatibility levels 1200, 1103 and 1100 are all supported.

JSON definitions with improved difference highlighting

Diff

Processing on database deployment

Processing

Like SSDT, processing options include “Default”, “Do Not Process” and “Full”.  There is also an option to process only the tables affected by the comparison (Create and Update), to avoid unnecessary processing.

ProcessingOptions

When deploying to a database (not project), BISM Normalizer will check connections set up for impersonation, and prompt for username and password (like deployment from SSDT).

Impersonation Credentials

Support for DirectQuery models with compatibility level 1200

Both source and target models must have the DirectQuery Mode property set to On for successful comparison.

Switch source and target in Connections dialog

Switch Connections

Improved scripting for JSON and XMLA

BISM Normalizer will use the appropriate editor if available. If not found, the option to save to a file is provided instead. Note: the JSON editor is not installed as part of a SSDT-only installation with the Visual Studio Integrated Shell.

Improved Scripting

Improved Scripting Xmla

Code behind BSMN file

View or modify contents of BSMN files.

View Code

Associate BSMN file types with Visual Studio

Open BSMN files from Windows Explorer with Visual Studio as the default application. Also display the BISM Normalizer icon in Solution Explorer. To enable this, right click a BSMN file in Solution Explorer and select the Install Solution Explorer Icon context menu. Note: administrator permissions are required to even see the menu option. This can’t be set up automatically with standard VSIX deployment from the Visual Studio Gallery because of the admin requirement.

Install Solution Explorer Icon

BISM Normalizer: Version 2.0 Released!

Enhancements in Version 2.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.
  • Support for Visual Studio 2010, 2012 & 2103.
  • 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.

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

Video of BISM Normalizer

Video of BISM Normalizer 1.

Follow

Get every new post delivered to your Inbox.