Archives

I'm Giving Two Biml Presentations at SQL Saturday Houston!

Varigence_Football
If you’re in the Houston area this weekend, come see me present at SQL Saturday Houston #408! I’m going to head down Friday afternoon, and will be giving the following presentations.

Writing Your First BimlScript

In this highly interactive presentation, I’ll guide you through the process of creating your first BimlScript. During the process we’ll cover the basics of Biml syntax, language options for generating BimlScript, recommended methods of storing and using metadata to drive your BimlScipts, and a lot more. As an added bonus, you’ll leave the presentation with several BimlScripts you can adapt to use in your own environments.

This is talk has been updated since the last time I gave this presentation.

SSIS Performance Tuning with BimlScript

You know that Biml can automate ETL loads, but your data warehouse is several terabytes with a tight load window. In this demo focused presentation, we’ll discuss how to use BimlScript to accomplish the following:
– Dynamically create multiple SISS packages for a data warehouse ETL load, each following a different best practice design pattern.
– Automatically execute each package multiple times and record the results for analysis.
– Determine which package version should be used in production.
The only way to be sure that your SSIS packages are performing optimally is to test them against multiple patterns. Make your life easier and let BimlScript do the work for you.

Free Stuff

I’m going to have a bunch of Biml schwag with me, courtesy of my friends at Varigence. I’ll be bringing shirts, miniature footballs, laptop stickers, etc.

Anyway, it’ll be a great time, and I can’t wait to see some of you there!

I'm Presenting on Biml at Austin SQL Saturday

I’m looking forward to seeing #SQLFamily this weekend down in Austin. I’m going to head down Friday afternoon, and will be giving a newly revised version of one of my favorite presentations.

Writing Your First BimlScript

In this highly interactive presentation, I’ll guide you through the process of creating your first BimlScript. During the process we’ll cover the basics of Biml syntax, language options for generating BimlScript, recommended methods of storing and using metadata to drive your BimlScipts, and a lot more. As an added bonus, you’ll leave the presentation with several BimlScripts you can adapt to use in your own environments.

I’m going to have a bunch of Biml schwag with me, courtesy of my friends at Varigence. In fact, since this is Superbowl weekend, I’m going to be tossing some Varigence footballs around during my talk.
*** Note: Insert mandatory joke about deflated balls here.
Varigence_Football

Anyway, it’ll be a great time, and I can’t wait to see some of you there!

Presenting on Biml Tonight at MSBIC

If you are in the DFW area, come down to the MSBIC Meeting tonight at the Microsoft Building in Irving. The meeting starts at 5pm CST. I’ll be giving an updated version of the following presentation.

Writing Your First BimlScript

In this highly interactive presentation, I’ll guide you through the process of creating your first BimlScript. During the process we’ll cover the basics of Biml syntax, language options for generating BimlScript, recommended methods of storing and using metadata to drive your BimlScipts, and a lot more. As an added bonus, you’ll leave the presentation with several BimlScripts you can adapt to use in your own environments.

Hadoop Series

Also, the great Bryan Smith will be giving another presentation in his fascinating Hadoop series. Details can be found on the MSBIC Website.

I hope to see some of you there!

Teaching a Full Day of Biml Goodness For Tulsa School of Dev

Calling all Business Intelligence/Data Warehouse folks within driving distance of Tulsa, Oklahoma. A week from tomorrow, Friday 5/16, I’ll be presenting a full day of Biml Training at the Tulsa School of Dev, and for the low, low cost of….. FREE! Wait, did I read that right? Does that say Free? Yes, my friend the entire event is Free of charge. The content will be an updated version of the precon I gave at SQL Saturday Phoenix.

I’ve divided the day into four sessions, but I highly recommend that you attend all of them because each one builds on it’s predecessor. My Abstract is listed below:

Automate Data Warehouse Development with Biml

In this series of sessions, you’ll learn how to use BimlScript to automate your data warehouse development using Agile methodology. You’ll also receive many code samples to use in your own environments. Come see why Biml is changing the way we build data warehouses.
Session 1 Focus:

  • How Biml makes Agile data warehouse development a reality.
  • Understand the basic syntax and rules of Biml.

Session 2:

  • The important role of meta data, how to store and consume it to drive your Biml automation framework.
  • How to choose the right scripting method and language for you.

Session 3:

  • Using BimlScript and metadata to dynamically create tables, views, and the SQL Server Integration Services packages to load them.

Session 4:

  • How to spot re-usable patterns which are ideal for using Biml.
  • How to write advanced BimlScript code to create drastically improve your productivity.

Anyone who’s watched me present on Biml, knows how pumped I am about the technology, and the amazing things you can do with it. Hope to see some of you up in Tulsa!

I'm Presenting at SQL Saturday Houston

Hey folks, this is just a quick post to urge people to attend SQL Saturday Houston this weekend (May 10th). The lineup of speakers is impressive, and I’ll be there as well. I’ll be giving a new presentation on Biml at the event. The abstract is below.

Writing Your First BimlScript

In this highly interactive presentation, I’ll guide you through the process of creating your first BimlScript. During the process we’ll cover the basics of Biml syntax, language options for generating BimlScript, recommended methods of storing and using metadata to drive your BimlScipts, and a lot more. As an added bonus, you’ll leave the presentation with several BimlScripts you can adapt to use in your own environments.

I can’t wait to get down there and spend time with my SQL Family (#SQLFamily). I hope to see some of you there!

Presenting Tonight for the SSIG in Greenville, SC

Bimlprecon
The fine folks at Varigence were kind enough to invite me to come and spend a couple of days with them at their headquarters in Greenville, NC. I’m excited to discuss Biml strategy with them, and how to best leverage and extend my BimlScript frameworks. To be honest, I can’t wait to spend some quality time with them to compare notes and pick up some new ideas.

I’m also going to present something new tonight for the SQL Server Innovators Guild (SSIG).

A CRUD-dy Alternative to Slowly Changing Dimension Processing

Slowly Changing Dimensions (SCD) are the foundation of Data Warehousing. Many of us use T-SQL Merge to process SCDs because the pattern is well known and it performs reasonably well. However, many experts in the SQL Server Community recommend that we stop using it, due to many outstanding bugs, but have not recommended a suitable T-SQL alternative.

In this session, I’ll show you how I use standard CRUD, mostly Inserts and Updates, to process Slowly Changing Dimensions. My current design pattern yields high performance, great flexibility, and easily maintainable code. As an added bonus, you’ll leave this presentation with the design pattern and code samples that you can quickly put to work in your environments.

Wednesday night I’m flying back home to Michigan for a few days to spend time with friends and family. It’s been too long, and I need to recharge my batteries.

Upcoming Biml Precon at SQL Saturday Phoenix

Bimlprecon
The good folks putting on SQL Saturday Phoenix have asked me to give a full day Precon seminar on Friday March 28, 2014. This will be my first Precon focused on Biml (Business Intelligence Markup Language), and I’m really pumped about it.

Fast Track your Business Intelligence Projects with Biml – Precon

You’re a business intelligence developer or manager trying to provide quality data in a timely, cost-effective manner. Perhaps you’ve considered Agile methodology, but found that the details of ETL, the plumbing if you will, makes iterative development all but impossible. In just one day, you’ll learn how to leverage Business Intelligence Markup Language (Biml) to super charge your ETL efforts, alleviating the pain and tedium of data warehouse development.
You will Learn:

  • How Biml makes Agile data warehouse development a reality.
  • Understand the basic syntax and rules of Biml, the important role of meta data, and how to choose the right scripting language for you.
  • Using BimlScript to dynamically create tables, views, and the SSIS packages to load them.
  • How to change your development mindset and spot re-usable patterns which are ideal for using Biml.
  • How to leverage BimlScript for ETL performance tuning.

As an added bonus, you’ll leave the precon with many BimlScripts and code samples you can adapt to use in your own environments. The entire seminar is geared toward getting you up to speed on BimlScript so you can stop wasting your time repeating the same old patterns. Come see why Biml is changing the way we build data warehouses.

Tickets can be purchased here. If you order by 2/28 the price is only $99 and is $125 after that. Keep in mind that there is a small processing fee as well.

SQL Saturday Phoenix Presentations

If you can’t attend the Precon, I’ll also be giving two presentations on Saturday which are free of charge (of course). If you’re in the area, come see me present:

Biml – The Next Step in Agile Data Warehousing

Business Intelligence Markup Language is literally changing the way we think about Data Warehouse design and ETL. In this interactive, demo focused presentation you’ll see how you can rapidly iterate through the Data Warehouse Development Cycle and provide ROI almost immediately. Learn how you can use Biml Script to automatically create staging tables, SSIS Packages, and other necessary objects during the modeling process.

Data Warehouse Mistakes You Can’t Afford to Make

Many data professionals understand the basics of Data Warehouse design, including Dimension and Fact Tables, slowly changing Dimensions, and the use of meaningless surrogate keys. However, it isn’t until you’ve created a dimensional model and put it into production, that you realize just how much of an impact seemingly trivial mistakes can make. They can hobble performance, allow inaccuracy, and perhaps worst of all, inhibit adoption and usage of the new system. Learn how to avoid many common mistakes, from someone who’s made them and then found ways to correct them.

More details about SQL Saturday Phoenix #279 can be found here. Hope to see a bunch of you there!

My Biml Metadata Strategy

Now that we’ve discussed various common metadata strategies used in creating BimlScript, I’m going to explain exactly how the metadata in my sample BimlScript framework functions. Basically, I’m going to start to pull back the curtain and explain some of the “magic” involved in my Biml – The Next Step in Agile Data Warehousing presentation.

As I mentioned in a previous article, I use two different forms of metadata in this example, SQL Server Extended Properties and Biml Annotation Tags. This metadata applies at both the table and column levels. While many examples and demonstrations work from the source forward, I’m a dimensional modeler so I tend to work from the data warehouse backward. My basic metadata process consists of the following steps.

  1. Metadata is defined and kept in a dimension model created with modeling software. Much of this metadata is specified automatically and requires very little effort from the architect/modeler.
  2. The modeling software generates the DDL necessary to convert the metadata into SQL Server extended properties.
  3. BimlScripts, which I’ll share in an upcoming article, convert the extended properties into Biml Annotation Tags. The other BimlScripts in the framework works from those tags. The import scripts will work fine with BidsHelper and do not require a Mist license.

There are several reasons I am choosing this multiple step process.

  1. Complex data warehouse projects typically require modeling software.
  2. I needed to decouple that software from my framework wherever possible so I wasn’t locked into using one product.
  3. Extended properties are already widely used for documentation.
  4. Biml Annotation Tags make writing BimlScript a lot easier, and the code is easier understand and maintain as well.
  5. If more people standardize on Biml Annotation Tags for their metadata, shared BimlScript snippets become much easier to adapt to multiple projects.
  6. This solution provides maximum flexibility since the rest of my code uses Biml Tags. For example, I could forego the use of a dedicated modeling and handle simpler projects entirely in Mist, or even manually with BidsHelper.

The modeling software I’m using for this demo is ModelRight. I don’t work for them, and don’t profit in any way if you purchase their software. That being said, I think it’s a good value compared to the other modeling products on the market. Anyway, the specific modeling software used isn’t integral to this solution. Any modeling tool which allows you to create custom metadata and generate the corresponding SQL Server extended properties DDL code will work. In fact, I have a friend who is using my framework the same way with ERStudio right now. Before we dive into the metadata details, let’s briefly discuss what we are going to load.

ModelRight Dimensional Model

ModelRight Dimensional Model

In the simple BimlScript framework example that I’m sharing, we’ll load the 4 tables shown above.

  • DimCustomer – This will be a Dimension with only Type 1 columns.
  • DimProduct – This Dimension has both Type 1 and Type 2 columns.
  • DimProductCategory – This is a Junk Dimension and also a Role Playing Dimension. In order to keep this example simple, I’ve created a contrived example and used DimProductCategory as PrimaryProductCategory and SecondaryProductCategory. Again, this is not a real world example, but it makes for a simple demonstration.
  • FactSalesOrderDetails – A Fact table with foreign keys to each of the three Dimensions listed above.

As I mentioned in the previous metadata post, I tend to favor Convention over Configuration. In order to produce a framework that was powerful and flexible, yet at the same time easy to understand, I analyzed exactly what my BimlScript code would need to “know” in order to load Dimension and Fact tables. I distilled the metadata requirements down to only three table level items and four column level items. Let’s start with what one would need to know at the table level. Notice that I do abbreviate my metadata titles which makes writing BimlScript faster, and the users never see this information anyway.

ModelRight Table  User Defined Properties

ModelRight Table User Defined Properties

Table Level Metadata
  1. TableRole – This property contains the type or role of the table and therefore includes values such as Dimension, DimJunk (Junk Dimension), Fact, StaticLkp (Lookup), and Custom.
  2. LkpNoMatch – This stands for “Lookup No Match” and this defines the type of behavior necessary to handle any SSIS Lookup against this table which fails to find a match. The current choices are Unkown, Inferred, and None. I’ll go into more detail in a future article when I explain how the Fact table BimlScript works.
  3. IsScripted – This setting determines if my BimlScript will automatically execute on this particular table or not. For example, I may have already created a custom SSIS package to load a particular table and I don’t want my BimlScript over-writing it.
ModelRight Column User Defined Properties

ModelRight Column User Defined Properties

Column Level Metadata
  1. ColumnRole – This column, similar to the TableRole above, classifies the functionality of each column in the data warehouse. Common values are BK (Business Key), SK (Surrogate Key), Type 1, Type 2, etc.
  2. IsViewColumn – I typically create views which overlay the Dimension and Fact tables in my data warehouse. This column, which accepts a simple “Y” or “N” allows the designer to indicate whether this column should be included in any views. This setting has the same effect when I use BimlScript to create Role Playing Dimensions.
  3. IsLkpColumn – This indicates whether the column value is the result of a SSIS Surrogate Key Lookup and the allowed values are “Y” or “N”. Again, I’ll provide more details on this when I cover Fact table loading.
  4. LkpRolePlayingPrefix – When creating a Role Playing Dimension, I prefix the names of all of the columns, including the Surrogate Key with a word(s) that represents the Dimension. So, if I created a Role Playing Dimension (view) on a table called DimDate which represents a “ShipDate”, then each column would be prefixed by the word “Ship”. The word ship is the LkpRolePlayingPrefix. If the value for this piece of metadata is NULL, or is missing entirely for a column, then no prefix is applied. Again, this will make more sense later.

It’s kind of amazing so much can be accomplished with so little metadata, much of which is automatically created by my modeling tool. In the next several articles I’ll use it to create staging tables, Role Playing Dimensions and other views, and the SSIS packages necessary to load Junk Dimensions, Slowly Changing Dimensions, and Fact tables. Keep in mind that this is only one of several BimlScript frameworks I’ve developed, and is the simplest to use and understand. My more elaborate BimlScript frameworks require more extensive metadata.

In the next article, I’ll walk through the steps to creating a simple, functional BimlScript which loads the Junk Dimension above, DimProductCategory. The SQL Server DDL and Biml file will be available for download in that article. However, to end this one, I thought I would share a snippet of code from each step listed above. Notice that each snippet includes some metadata at both table and column level. This is a portion of the SQL Server extended property DDL produced automatically by ModelRight.

EXEC sp_addextendedproperty @name = 'TableRole', @value = 'DimJunk',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'DimProductCategory';
GO
 
EXEC sp_addextendedproperty @name = 'IsScripted', @value = 'Y',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'DimProductCategory';
GO
 
EXEC sp_addextendedproperty @name = 'ColumnRole', @value = 'SK',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'DimProductCategory',
	@level2type = 'Column', @level2name = 'ProductCategoryKey';
GO
 
EXEC sp_addextendedproperty @name = 'IsViewColumn', @value = 'Y',
	@level0type = 'Schema', @level0name = 'dbo',
	@level1type = 'Table', @level1name = 'DimProductCategory',
	@level2type = 'Column', @level2name = 'ProductCategoryKey';
GO

My BimlScript automatically imports the database DDL and converts it to Biml Annotation Tags like the following:

        <Dimension Name="DimProductCategory" SchemaName="DataWarehouse.dbo" PackageSubpath="ImportTables">
            <Annotations>
                <Annotation Tag="IsScripted">Y</Annotation>
                <Annotation Tag="LkpNoMatch">Unknown</Annotation>
                <Annotation Tag="TableRole">DimJunk</Annotation>
            </Annotations>
            <Columns>
                <Column Name="ProductCategoryKey" DataType="Int16" ScdType="Key">
                    <Annotations>
                        <Annotation Tag="ColumnRole">SK</Annotation>
                        <Annotation Tag="IsViewColumn">Y</Annotation>
                    </Annotations>
                </Column>

Strategies and Languages for Generating Biml

What are the best methods for generating Biml and what language should you use? Before I dive into this, let me begin with a disclaimer. I applaud anyone working with Biml to improve their productivity and eliminate needless, repetitive work. One of the primary advantages of Biml is its flexibility, which allows people to generate it many different ways to solve problems. I’m going to briefly discuss some concepts involved in the process and common ways I see it being generated. I’m also going to make recommendations that I’ve found work well in most circumstances. However, remember that this is only my opinion, and your mileage may vary. Let’s start by discussing two basic strategies for creating Biml, which I refer to as Scripting Biml vs. BimlScript.

Scripting Biml?

What exactly do I mean by “Scripting Biml?” Biml is simply an XML Language and therefore can be produced with many different tools and languages. When I talk about “Scripting Biml,” I’m referring to using some kind of tool or language to generate static Biml from outside of the “text” itself. This could take the form of XML language generation or simple string manipulation. I myself started down this route because string manipulation was familiar and comfortable to me. However, I quickly found that this method is very limiting and becomes unwieldy when attempting any complex scripting. I’ve listed several “Scripting Biml” examples, and included links for further research as well as a brief explanation of the methods/strategies being leveraged.

PowerShell

At PASS Summit 2013, Allen White (Blog/Twitter) presented Automate Your ETL Infrastructure with SSIS and PowerShell, which is available for streaming at that link. Allen demonstrates how to use PowerShell and Server Management Objects (SMO) to generate Biml. Be forewarned, unless you’re really into PowerShell, this video may not be for the faint of heart.

.Net Language – C#

One of my clients created an open source project called BimlGen, which is available on GitHub. BimlGen imports SQL Server database tables into Biml, and converts SQL Server Extended Properties into Biml Annotation Tags. Admittedly, it needs work, but it’s free and open sourced. I’m hoping that folks in the community will work on it to improve and extend its functionality. Simply enter the Server and Database Names, change the output folder if you like, and click Generate. The output folder will open and you should find a single Biml file which contains your Biml assets. BimlGen_screen

T-SQL

Another friend of mine, Garrett Edmondson (Blog), has a video on his blog where he uses T-SQL string concatenation to generate simple SSIS Packages.

BimlScript

BimlScript is created by embedding C# or VB.Net code within the Biml itself. Once you grasp the basics of BimlScript, which admittedly is not a trivial undertaking, your productivity will skyrocket. BimlScript is analogous to HTML, which is also a markup language, and ASP.Net. BimlScript allows the programmer to embed nuggets of C# or VB.Net code inside Biml to dynamically generate Biml assets, much the same way that ASP.Net dynamically generates web pages. These nuggets often include LINQ (Language Integrated Query) which is compatible with either C# or VB.Net. If all of this is new to you, don’t worry, I’ll explain all of this in more detail in a later post.

Should I use C# or VB.Net?

Let me preface this by saying that I started coding BASIC long ago on a TRS-80 machine, and I’ve been coding in BASIC ever since. However, when I started down the BimlScript path, I drank the C# Kool-Aid for the following reasons:

  • The vast majority of the BimlScript samples available are written in C#. This fact alone was enough to get me to learn C#.
  • C# with LINQ just seems cleaner once you get over C#’s affinity for semi-colons and curly braces. However, those C# curly braces tend to stand out better than VB statements embedded inside Biml.
  • Mist doesn’t support Intellisense for VB.Net yet, but that functionality will be added soon.
  • Arguably, C# programming skills are in more demand and worth more money than VB.Net.

That being said, if I had a team of skilled VB.Net programmers, I would not invest the resources to teach them C# just to work with BimlScript.

Types of Biml Scripts

There are three basic types of BimlScripts which support both C# and VB.Net.

  1. Expandable – This is the most common type and is the only type supported by BidsHelper. These types of scripts are executed “manually” by the user.
  2. Live – If you choose to purchase Mist, Biml Scripts can be automatically run in the background, primarily for convenience. One of the nice things about Mist is I can use the same script as either Expandable or Live at my choosing.
  3. Transformers – These scripts change existing objects like SSIS Packages according to code logic and metadata. This is a Mist only option as well.

The vast majority of the scripts I share will be expandable and will use C#/LINQ. In the next post, I’ll explain exactly how I use metadata to control my BimlScript framework sample.

Biml Metadata Options and Considerations

Simply put, metadata is data about data. Probably the most common way that SQL Server Professionals use metadata is when we query SQL Server System Views. In this article we’ll discuss using metadata to drive BimlScript automation. Before I start listing common metadata sources and methods, I need to briefly contrast two basic metadata philosophies I see being used. In some BimlScript frameworks, the metadata is highly detailed, as this typically allows the most flexible, configurable functionality. However, maintaining that detailed metadata can be tedious and time consuming. I subscribe to the philosophy of Convention over configuration. I prefer to code BimlScript in such a way as to expect that default values and behaviors (conventions) will be used and supplied. This leads to BimlScript that is much cleaner, easier to read, and maintain. At the same time, I try to provide mechanisms to allow as much flexibility as possible. For example, my BimlScript code follows default patterns I’ve identified and only deviates if specific metadata values are found.

There are several BimlScript metadata options/sources that I’ve noticed being used. Below I’ve listed the most common and I’ve included hyperlinks to blog posts or code samples where possible. I’ve noticed that more people are presenting on Biml than are blogging, but I’m not sure why.

SQL Server Metadata Tables

Simple Column Level Metadata Table

Simple Column Level Metadata Table

What you’ll often see in demonstrations are BimlScripts being controlled by metadata stored in dedicated SQL Server tables. Unfortunately, I couldn’t find any examples on the interwebs, so I provided the sample above. Information in the ColType field (abbreviated to fit on PowerPoint slides) indicates the functional role of said column. A column designated as BK would be treated as a Business Key, LKP triggers SSIS Lookup Transform code generation, etc. I’ve seen people use separate tables to house the metadata at the column level for Facts and Dimensions. I’ve also seen Table level metadata kept in one table and Column level metadata kept in a separate table. These metadata tables often look like Source to Target Mapping Documents. Creating and using SQL Server tables is second nature to most of us so this method is often used. These tables can be backed up with the rest of the database and also used to generate documentation for business purposes.

SQL Server Database Schema (DDL)

This takes several forms. For example, Samuel Vanga (Blog/Twitter) shows how to generate simple SSIS Packages dynamically using query results from the sys.tables Catalog View.

Some metadata strategies rely on Table and Column Names, though I couldn’t find examples on the web. For example, BimlScript can look for table names that begin with “Dim” or “Fact,” and take appropriate action. Also, I’ve seen people specify that any Column Name which ends in “Key” represents a Surrogate Key, or a column name that ends in “_BK” represents a Business (or Alternate) Key. I tend to avoid this practice because I’m a consultant and I work on many data warehouse projects. Therefore, I can’t dictate things like naming conventions. Also, I wouldn’t want business users to see column names like “CustomerCode_BK” on their reports.

SQL Server Extended Properties

Extended Properties have been around since SQL Server 2000 and people have been using them to document their database objects for some time. As a source for BimlScript metadata, Extended Properties have several advantages such as:

  • They’re supported on most SQL Server objects.
  • Can be automatically generated and maintained by third party software.
  • Added and retrieved with relatively simple T-SQL code.
  • They’re “hidden” from regular users.
  • They’re backed up with the rest of the database.

I personally tend use SQL Server Extended Properties in conjunction with the next one, which is….

Biml Object Model – Annotation Tags

Like SQL Server Extended Properties, most (if not all) Biml objects such as tables, views, columns, etc. can be documented via annotations. There are several types of Annotations, in this article we’re going to focus on the “Tag” type. Below is sample of Biml code with Annotations that drive ETL automation.

 <Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Dimensions>
        <Dimension Name="DimCustomer" SchemaName="DataWarehouse.dbo" PackageSubpath="ImportTables">
            <Annotations>
                <Annotation Tag="TableRole">Dimension</Annotation>
                <Annotation Tag="LkpNoMatch">Unknown</Annotation>
                <Annotation Tag="IsScripted">Y</Annotation>
            </Annotations>
            <Columns>
                <Column Name="CustomerKey" ScdType="Key">
                    <Annotations>
                        <Annotation Tag="ColumnRole">SK</Annotation>
                        <Annotation Tag="IsViewColumn">Y</Annotation>
                    </Annotations>
                </Column>

Notice that I’ve specified a Dimension Table named DimCustomer which I’ve designated with a TableRole of Dimension. You’ll also notice that the first column, “CustomerKey” has a ColumnRole of “SK” which stands for Surrogate Key. These designations are only meaningful to the BimlScript you’re using. I’ll go into the nuts and bolts of how you refer to these annotation tags with BimlScript early next week.

What Metadata is Required?

As with so many other technical questions, the answer is “It Depends.” Do you lean more towards Configuration or Convention? As I mentioned above, I prefer Convention which should be obvious in the above snippet. Notice how any references to the source(s) seem to be missing? The BimlScript Framework I’ve recently demonstrated, which is available online, operates solely from the metadata in three table level annotations and three column level annotations. From that little bit of metadata, I “automatically” generate staging tables, overlaying views on the data warehouse including Role Playing Dimensions, Slowly Changing Dimension loads, Junk Dimension loads, and Fact table loads. Neat huh?

In the next article, I’ll take on the thorny topic of choosing a method and/or programming languages to generate Biml.

Page 1 of 3212345...102030...Last »