Archives

Biml Syntax Basics and Rules

Biml is a human readable XML language, created by a company called Varigence, which compiles into SQL Server objects such as tables, views, SSIS packages, and SSAS cubes. In this post, we’ll cover the basics of the Biml language so you can start coding as fast as possible. Since, I’ve always found that it’s easier to learn new things by example, I will use one of the scripts from my Biml – The Next Step in Agile Data Warehousing presentation.

Biml is not proprietary and anyone can create software which generates and/or consumes it. There are many ways to generate Biml, but most people currently use one of two methods. For those who don’t want to pay for a dedicated product, Biml (and BimlScript) can be written in the Business Intelligence Development Studio (BIDS) or SQL Server Data Tools (SSDT) and compiled with a free program called BidsHelper. Most ETL Developers have at least heard of BidsHelper, and I’ve been using it for years. Instructions on installing BidsHelper and creating your first simple Biml file can be found here.

The other popular method is to use Mist, which is a product of Varigence. Let me preface what I’m about to say with a disclaimer.

I do not work for Varigence and am not financially compensated by them. I don’t benefit in any way if you purchase Mist or choose to use BidsHelper instead.

That being said, I find Mist to be invaluable for serious BimlScript development. Most of my scripts are created using Mist, but they can all be edited, compiled, and executed with BidsHelper. In the future, if I provide a script which cannot be run without Mist, I will clearly point that out. The basic development process when using BidsHelper varies from Mist in several ways. One way is file naming and grouping. If using Mist to create or import tables, connections, and other objects, a separate file is usually created for each. In Mist, BimlScripts can be manually executed or they can be “live.” If they are live, the objects are sort of created virtually and updated in real time. Since Mist keeps track of these live objects, it doesn’t matter if each Connection, Database, Schema, etc. is in a separate file.

In BidsHelper, we tend to group similar Biml objects together because there is no live option when using that program. Therefore, any files containing objects must be pre-selected before execution. So, if your connections, databases, and schemas were spread across 8 files, you’d have to constantly pre-select them whenever you ran other BimlScripts. Obviously, the fewer Biml files we need to highlight, the faster we can work. To that end, I and others typically start by creating a standard file called Environment.biml. This file contains my Connections, Databases, and Schemas.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<!--See More BimlScripts at Made2Mentor.com -->
    <Connections>
        <OleDbConnection Name="connDW" ConnectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DataWarehouse;Data Source=DevServer" />
        <OleDbConnection Name="connSource" ConnectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=m2m601c61;Data Source=DevServer" />
    </Connections>
    <Databases>
        <Database Name="DataWarehouse" ConnectionName="connDW" />
        <Database Name="M2MSource" ConnectionName="connSource" />
    </Databases>
    <Schemas>
        <Schema Name="dbo" DatabaseName="M2MSource" />
        <Schema Name="dbo" DatabaseName="DataWarehouse" />
        <Schema Name="interim" DatabaseName="DataWarehouse" />
        <Schema Name="star" DatabaseName="DataWarehouse" />
    </Schemas>
</Biml>

Note, that while I use the same Environment file in both BidsHelper and Mist, when using Mist the Project View shows one file, while the Logical View properly segregates them.

Mist

Mist

Root Element

Let’s break down the basic parts and rules of a Biml XML file. The first line is called the Root element and references the XSD file. This is the XML Schema Definition file which contains the rules of the Biml declarative XML language. Under the root elements are plural collections of elements/objects such as Connections, Databases, Schemas, Packages, etc. Each plural collection will have one or more individual objects nested inside, which you can see in the above script. Also, each plural collection and each nested Biml object must have a closing tag.

Object Attributes/Properties

Each type of Biml object has different properties that pertain to it, only some of which are required. In my example, you’ll notice that I’ve defined two databases, each of which has two sources, along with several schemas. One of the schemas I’ve defined is named “star.” That’s a little inside joke because I recently worked on a project where all of their dimension and fact tables were placed inside a “star” schema.

All Biml objects including the plural collections are case sensitive. Varigence uses Pascal Case in which words are concatenated and each new word begins with a capital letter, like OleDbConnection. Biml attribute values (properties) must be placed inside quotes and are case sensitive as well. Therefore, a schema named “Star” is not the same as one named “star”.

Biml Comment Syntax

Biml Comments are created using the syntax I’ve show above. Keep in mind that XML Comments can span more than one line. The following is perfectly valid.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<!--See More 
	BimlScripts 
	at Made2Mentor.com  -->    
</Biml>

Object Naming and Scope

Notice the way that objects are named in the above example. Obviously schema names cannot be completely unique otherwise you could only have one database in your Biml project with the schema “dbo.” However, objects must be unique within their scope. In this case, my schema names must be unique within the Database scope.

Illegal XML Characters and the use of CDATA

Remember that Biml is simply XML and as such has certain characters that have special meaning are considered illegal. Some common characters which can cause trouble are the comparison characters which are often part of T-SQL or SSIS Expression statements. If you’d like you can replace the following characters with their corresponding pre-defined Entity Reference:
illegal chars

For example, an Execute SQL Task with a Direct Input query like the following will result in an error.

Select  Fields From  SomeTable Where SomeField < 5

This can be re-written as:

Select  Fields From  SomeTable Where SomeField &lt; 5

However, I find that this makes code difficult to read and follow, so I typically enclose this kind of code inside a CDATA section which tells XML not to parse it. CDATA can span multiple lines as well. Since most of my Biml is generated, including the CDATA section is trivial.

<![CDATA[ Select  Fields From  SomeTable Where SomeField < 5 ]]>  

White-space Issues

White-space can be inserted in your code to make your code easier to read and the compiler ignores it. However, white-space is NOT ignored in your property values. Consider part of the script above.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Databases>
        <Database Name="DataWarehouse" ConnectionName="connDW" />
    </Databases>
</Biml>

I can add extra spaces or carriage returns to the Biml objects themselves or between properties and the compiler ignores them. So, the following would work:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Databases       >
        <Database Name="DataWarehouse"        ConnectionName="connDW" />

             </Databases>
</Biml>

I realize that the code I just posted is hard to read, but you get the point. Conversely, white-space is NOT ignored inside of proprty values. So, the following will throw an error:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Databases>
        <Database Name="DataWarehouse" ConnectionName="connDW      " />
    </Databases>
</Biml>

Could not resolve reference to ‘connDW ‘ of type ‘DBConnection’. ‘ConnectionName=”connDW “‘ is invalid.

What’s Next?

Stay tuned because in upcoming articles we’ll cover the following:

  • A tutorial on BimlScript syntax and how to create them.
  • Additional Code Samples from my Biml – The Next Step in Agile Data Warehousing presentation, along with an explanation of how each code works.
  • Everything you need to know about Biml datatypes.

Any questions?

Related posts:

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>