THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is a Data Philosopher at Enterprise Data & Analytics, an SSIS Trainer, Consultant, developer of the Data Integration Lifecycle Management (DILM) Suite, a Business Intelligence Markup Language (Biml) developer and BimlHero; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server Integration Services Design Patterns, and the Stairway to Integration Services. Keep up with Andy, join his mailing list!

The Basics of Biml – Populating the Biml Relational Hierarchy

In this post, I’m going to demonstrate how to build the objects Business Intelligence Markup Language (Biml) requires before creating anything – the Biml Relational Hierarchy. The Biml Relational Hierarchy provides the foundation for all relational interaction between packages, cubes, dimensions, facts, and T-SQL.

It’s important to note that Biml is useful for generating SSIS and SSAS, but Biml can generate any text – which includes .Net code (I’ve used Biml to generate C#) – that is based on a database schema.

To play along at home grab the 14-day free trial of Mist here and download the World Wide Importers sample database here.

The Biml Relational Hierarchy

BimlRelationalHierarchyMist

Once you create a new project in Mist (mine is called “BimlBasics”), the Logical View displays an empty Relational hierarchy. Note that the relational hierarchy is flat. There are good reasons for representing the hierarchy thus, but they are beyond the scope of this post (but trust me, it’s a good and smart thing). One thing I will share in this post: The objects beneath the Relational node reside in the “RootNode” object in Biml. Reeves Smith [@SQLReeves] explains RootNode in this (excellent) article: Stairway to Biml Level 5 - Biml Language Elements at SQL Server Central.

The “logical flatness” of these objects in RootNode make them easier to access and manipulate programmatically. One way to illustrate the logical flatness of the RootNode’s relational hierarchy is shown here:

BimlRelationalHierarchyFlat

But, these objects – connections, databases, schemas, and tables (and columns) – are really part of a related hierarchy. Another way to think of the RootNode’s relational hierarchy is shown here:

BimlRelationalHierarchyTiered

“But Andy, there’s no “Columns” node in the flattened presentation.” True. The columns are there, though; they’re inside the Tables as we will see in a bit.

Populating the Relational Hierarchy

There are two ways to populate the relational hierarchy:

  • Graphically
  • Programmatically

In this post, I am going to cover graphically populating the relational hierarchy. Scott Currie [Varigence] provided a remarkable example of programmatically populating the relational hierarchy in his BimlScript.com article Biml Basics for Relational DBs.

Populating the relational hierarchy graphically is simple; just follow the steps as they are listed from top to bottom in the Logical View’s Relational node in the Mist IDE. Begin with Connections.

Connection

Right-click the Connections node, hover over Add Connection, and then click OLE DB:

BimlRelational_Connection_1

The “OleDbConnection1” window displays. You can enter a connection string value in the Connection String textbox or build a connection string. To build a connection string, first select a Provider from the dropdown:

 

BimlRelational_Connection_2

Enter a database instance in the Server textbox:

BimlRelational_Connection_4

Select an authentication method and enter credentials (if needed):

BimlRelational_Connection_5

The Database Name dropdown is empty until you click the Update link:

BimlRelational_Connection_6

After clicking the update link, the Database Name dropdown is populated with a list of databases:

BimlRelational_Connection_7

You can test the connection by clicking the Test button:

BimlRelational_Connection_8

If the test is successful, you will see the following dialog:

BimlRelational_Connection_9

You can rename the Connection in Logical View by right-clicking the connection and clicking Rename:

BimlRelational_Connection_10

I renamed my connection “WorldWideImporters”:

BimlRelational_Connection_11

Logical View now reflects a configured Database. Let’s configure a Database.

Database

To add a database to the relational hierarchy, right-click Databases in Logical View, and then click Add Database:

BimlRelational_Database_1

When the Database Properties window displays, Select the WorldWideImporters connection from the Connection property dropdown:

BimlRelational_Database_2

Edit the Name property of the Database (I named mine WorldWideImporters). When you navigate away from the Name property textbox you are prompted to confirm you want to also rename the Database object’s Biml file:

BimlRelational_Database_3

Click Yes.

Logical View now reflects a configure Connection and Database:

BimlRelational_Database_4

Let’s next configure a schema.

Schema

To add a schema to the relational hierarchy, right-click Schemas in Logical View, and then click Add Schema:

BimlRelational_Schema_1

When the Schema Properties page displays, configure the Database property from the dropdown:

BimlRelational_Schema_2

Provide the name of the Schema in the Name property textbox (I chose the Application schema). When you navigate away from the Name property textbox, you are prompted to confirm you want to also rename the Schema object’s Biml file:

BimlRelational_Schema_3

Click Yes.

Logical View now reflects a connection, database, and schema:

BimlRelational_Schema_4

Next, let’s import a table:

Table

There a couple ways to add a table to the relational hierarchy. If the table exists in the relational database, you can save time by importing the table. Right-click Tables in Logical View and then click Import Tables:

BimlRelational_Table_1

When the Import Tables dialog displays, select the connection (WorldWideImporters) from the Source Connection dropdown:

BimlRelational_Table_3

Note the Connection Established indicator changes from red to green when you successfully connect.

Select the Project Database from the dropdown (WorldWideImporters):

BimlRelational_Table_4

the Importable Assets treeview displays the available schemas, tables, and views in the WorldWideImporters database. I’m going to import a single table named “People”:

BimlRelational_Table_5

Note that I could have skipped creating the Application schema in the Biml relation hierarchy as it is imported as part of the table import process. In this case, I choose to overwrite the schema object I created earlier with the imported version:

BimlRelational_Table_6

The Logical View now displays a completed Biml relational hierarchy that includes a connection, database, schema, and table:

BimlRelational_Table_7

Columns?

I can hear you thinking, “Where are the columns, Andy?” I’m glad you asked. If you double-click the Application.People table, the graphical viewer will display the table designer, which displays many table objects including columns:

BimlRelation_Column_1

Conclusion

Congratulations! You’ve just populated a Biml relational hierarchy.

:{>

Learn More:

Biml Academy
Stairway to Biml
Stairway to Integration Services
Varigence.com
BimlScript.com
SQL Server Central

Need help or training implementing a Biml solution?
Contact Enterprise Data & Analytics today!

Published Tuesday, September 06, 2016 1:19 PM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement