BIML + Datavault

BIML (Business Intelligence Markup Language)

BIML is a Markup Language created by Variance which will help you to automatize the creation of many assets in your BI solutions, like SSIS packages or Cube (SSAS) definitions. For better definition visit Wikipedia http://en.wikipedia.org/wiki/Business_Intelligence_Markup_Language

As you may know, all the metadata of your MS BI suite is based on XML. If you take any cube or dtsx package and open it with Notepad you’ll notice that everything inside is XML. BIML is a tool to autogenerate this XML code through .Net or even SQL. You can find samples and all the possible tags to use on http://bimlscript.com/.

Based on that, I created on SQL Stored Procedure a what I called “PackageGenerator”. This SP will ask for an IDPackage and this ID will lead to a series of simple configuration tables that will define your DTSX Source, Target, fields, transformations, etc. In order to Autogenerate the package into your VS Solution with just one right click -> Generate.

Datavalut + BIML

As I explained before Datavault is a flexible  DWH following a rigid standards that will allow you to “guess” how a new entity will be build (more info check my previous post http://www.businessintelligence-solutions.com/dv-datavault-approach-dwh/). For example, we know that the business key will be on a Hub, and we know that all the hubs start with H_[EntityName]. Then if you have any link you know that it will be allocated in a Link and all the links (one or more) will be structured like this L_[EntityName]_L1,L_[EntityName]_L2… and same for Satellites S_[EntityName]_S1,S_[EntityName]_S2,…

Then you also know from the very beginning that all your tables will have a standardized structure and fields as… ValidFrom, ValidTo (always in all the tables and allowing you to do a SCD2 by default), RecordSource (to know from where is coming the data) and LoadDateTime (to know when it was inserted).

Then each object has a link to the hub always H_[EntityName]_SK (int) and your hub will always containt (apart of this unique SK) the business key called H_[EntityName]_BK.

With all these rules in place, you can create a framework that will allow you (hopefully with a UI created on top of that) to create all your BIML packages using this SP that will be feed by all these tables:

Then now the possibilities are open to almost everything. For example, in less than a week a prepare a SP that will call (thanks to these tables or even to the system tables that you can query) your DW. You just need to define which ones are going to be Dimensions, which ones Fact tables and your SP will recreate the whole DW (drop and create). It will link your fact tables (through constraints) to your dimensions, it will create all the tables with the same rules (ID_Dim_, or ID_Fact_…). And of course, it will create automatically all the dtsx to bring the data from your DV to your dimensional modelling in seconds and also the views that will wrap all these H, S and L into one Dim or Fact (depending what you said in your tables), besides I have a table that will define SCD1 or SCD2 and your system will create the dtsx doing an update or doing a SCD (http://www.businessintelligence-solutions.com/practice-scd-ssis/).

The weak point of DV is that usually a normal third normal form database that has around 30 tables in DV it will become more than 120+, but using BIML on top of your DV it will create the 120+ packages (dtsx) in seconds and your DW will change as soon as you really need it without any problem.

Good luck!
Albert


Did you like this? Share it:

Leave a Reply

*