23 March 2016

What is Biml

Business Intelligence Markup Language (Biml) is a programming language for creating data warehouse, business intelligence, and other data solutions. It uses XML in combination with small nuggets of C# or VB code to automatically create huge amounts of SQL scripts, SSIS packages, SSAS cubes, and other objects that your team previously had to build manually - usually with slow, tedious, error-prone dragging and dropping. Perhaps the best part is that there is nothing to install on your server - Biml works entirely with your existing data infrastructure.

Here is an illustration of how the Biml workflow operates:

A simple BIML script:

<Biml xmlns=”http://schemas.varigence.com/biml.xsd“>

        <Packages>

                <Package Name=”Hello World” ConstraintMode=”Linear”>

                </Package>

        </Packages>

</Biml>

The script above creates a package called Hello Word

HOW CAN I HAVE BIML

Install latest BIDS Helper on your VS version tool of choice and there you go. You can also try out MIST from Varigence which is an IDE for managing all of your packages. The subscription after trial for one licence is about $250/license/month so probably not the ideal choice for a tool that sits on top of SSIS.

WHAT CAN I ACHIEVE USING BIML

- Automate repetitive tasks

For instance, automate creation of DTSx to extract data from one or several different sources for an ODS. Dynamically change the behaviour from each package using C#. Below is how you would create a BIML extraction from 1 single source / all tables on a schema.

Environment.biml

<Biml xmlns=http://schemas.varigence.com/biml.xsd>

    <Connections>

        <OleDbConnection Name=“Source” ConnectionString=“Provider=SQLNCLI11;Server=SOURCE_SERVER;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;” />

        <OleDbConnection Name=“Target” ConnectionString=“Provider=SQLNCLI11;Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;” />

    </Connections>

</Biml>

LoadPackages.biml

<# var sourceConnection = RootNode.DbConnections[“Source”]; #>

<# var importResults = sourceConnection.ImportDB(); #>

<Biml xmlns=http://schemas.varigence.com/biml.xsd>

    <Packages>

        <# foreach (var table in importResults.TableNodes) { #>

        <Package Name=“Copy <#=table.Name#>” ConstraintMode=“Parallel”>

            <Tasks>

                <Dataflow Name=“DFT_CopyData”>

                    <Transformations>

                        <OleDbSource Name=“Retrieve Rows” ConnectionName=“Source”>

                            <DirectInput>SELECT * FROM <#=table.Name#></DirectInput>

                        </OleDbSource>

                        <OleDbDestination Name=“Load Rows” ConnectionName=“Target”>

                            <ExternalTableOutput Table=“<#=table.Name#>” />

                        </OleDbDestination>

                    </Transformations>

                </Dataflow>

            </Tasks>

        </Package>

        <# } #>

    </Packages>

</Biml>

In the example above it’s created one DTSx per extraction but you can easily rewrite it to have just one DTSX to extract the several tables, like this:

<# var sourceConnection = RootNode.DbConnections[“Source”]; #>

<# var importResults = sourceConnection.ImportDB(); #>

<Biml xmlns=http://schemas.varigence.com/biml.xsd>

    <Packages>

        <Package Name=“Copy All” ConstraintMode=“Parallel”>

            <Tasks>

             <# foreach (var table in importResults.TableNodes) { #>

                <Dataflow Name=“DFT_Copy <#=table.Name#> Data”>

                    <Transformations>

                        <OleDbSource Name=“Retrieve Rows” ConnectionName=“Source”>

                            <DirectInput>SELECT * FROM <#=table.Name#></DirectInput>

                        </OleDbSource>

                        <OleDbDestination Name=“Load Rows” ConnectionName=“Target”>

                            <ExternalTableOutput Table=“<#=table.Name#>” />

                        </OleDbDestination>

                    </Transformations>

                </Dataflow>

                <# } #>

            </Tasks>

        </Package>

    </Packages>

</Biml>

And the result is:

- Create and modify company template DTSx with custom error handling passing along a biml script.

Every company has it’s own way to do things when extracting/transforming/loading. Error handling, variables, log tasks all differ from company to company or even, in the same company, from customer to customer.

Whenever you want to start using a new template, just change it on your company template repository and when someone starts a new script all she has to do is to generate the SSIS package using that template.

- No more edits to refresh meta-data

With BIML, if there’s a meta-data change to the source/target, all you have to do is to regenerate the package

CONCLUSION

BIML is much more powerful than what is reflected here since everything you can add in Visual Studio can be done in BIML with the added dynamics that can be achieved by integrating C#. For instance you could have an entire installation developed in BIML. However, that would involve a complete re-structuring of all the processes/mind-set for a BI ETL project. In a more practical view BIML can be used, without a long learning curve, to automate much of the repetitive work in extractions, for instance.

With MIST (Proprietary from Varigence) you could step up that integration, since you can, for instance, reverse engineer packages already deployed and make global transformations on BIML projects, as well as centrally manage your entire project. That would open much more possibilities to fully develop pure BIML solutions.

.

.

.

.

     Rui Custódio
      Consultant
Blog