IN2BI, Microsoft Business Intelligence
data, stories & insights
rss | email | twitter

Create a LightSwitch Application to Maintain Metadata

Correct metadata is essential for every automation solution. When I started working with BIML I used one table that describes the source to target table mapping. (see the “Creating a Meta Data Driven SSIS Solution with BIML” series)

And that works fine in most simple scenarios like importing staging tables or when you are able to move business rules to a view. Key in these simple scenarios is that the columns in your target table have the same name and the same data type.

Extending the metadata model

In more recent assignments I added a column mapping table and used T-SQL to simplify the BIML script. With this approach I created more robust SSIS packages that provided extra possibilities, like:

  • Incremental load patterns
  • Converting data types
  • Surrogate key lookups
  • Expressions to calculate new values

A problem with this approach however is maintaining these column mappings. So I decided to take Visual Studio LightSwitch for a spin and create a simple application to fulfill my maintenance needs.

LightSwitch

Visual Studio LightSwitch is a rapid application development (RAD) tool that is used to help write data-centric line of business (LOB) applications. An import prerequisite of LightSwitch is that you need to have a data model or construct one using the LigthSwitch GUI. Based on this data model you can easily define a set of screens and your application is done. So: No coding necessary. (although coding is possible to extend the standard functionality)

Data Model

For my data model I decided to add two additional tables:

  • ETLJob
  • Connection

From a BIML perspective the ETLJob table is primarily used to create the master package with Execute Package and Execute SQL tasks.  An ETLJob can be either:

  • A generated package (with one or more table mappings)
  • A manual package or a
  • SQL Statement

So I ended up with the following model:

image

The SQL DDL Statements to create this model:

CREATE SCHEMA meta
GO

CREATE TABLE meta.Connection
(
      ConnectionID INT IDENTITY NOT NULL PRIMARY KEY
    , ConnectionName VARCHAR(64) NOT NULL
    , ConnectionType VARCHAR(20) NOT NULL
    , ConnectionString VARCHAR(256) NOT NULL
    , CreateInProject BIT NOT NULL DEFAULT(0)
    , DelayValidation BIT NOT NULL DEFAULT(1)
)
CREATE TABLE meta.ETLJob
(
      ETLJobID INT IDENTITY NOT NULL PRIMARY KEY
    , ETLJobName VARCHAR(64) NOT NULL
    , ETLJobType VARCHAR(20) NOT NULL
    , ETLJobGroup VARCHAR(64)
    , SQLConnection INT
        REFERENCES meta.Connection(ConnectionID)
    , SQLStatement VARCHAR(512)
)
CREATE TABLE meta.TableMapping
(
      TableMappingID INT IDENTITY NOT NULL PRIMARY KEY
    , ETLJOB INT NOT NULL
        REFERENCES meta.ETLJob(ETLJobID)
    , TableName VARCHAR(64)
    , TableType VARCHAR(20)
    , TableConnection INT NOT NULL
        REFERENCES meta.Connection(ConnectionID)
    , TableSchema VARCHAR(64)
    , SourceConnection INT
        REFERENCES meta.Connection(ConnectionID)
    , SourceObject VARCHAR(256)
)
CREATE TABLE meta.ColumnMapping
(
      ColumnMappingID INT IDENTITY NOT NULL PRIMARY KEY
    , TableMapping INT NOT NULL
        REFERENCES meta.TableMapping(TableMappingID)
    , ColumnName VARCHAR(64) NOT NULL
    , ColumnType VARCHAR(20) NOT NULL
    , ColumnDataType VARCHAR(20) NOT NULL
    , SourceColumnName VARCHAR(64)
    , SourceColumnDataType VARCHAR(20)
    , Calculation VARCHAR(256)
    , LookupConnection INT 
        REFERENCES meta.Connection(ConnectionID)
    , LookupObject VARCHAR(256)
    , InputColumnName VARCHAR(64)
    , InputColumnDataType VARCHAR(20)
    , OutputColumnName VARCHAR(64)
)

 

Importing the data model in LightSwitch

To import this data model you only need 6 steps:

  1. Create a new database. (I named it MetaBase) and execute the  afore mentioned DDL Statements to create the objects and relations.
  2. In Visual Studio create a New Project. Select LightSwitch as template and choose LightSwitch Application (Visual C#). Give the project a name (I used Metabase) and click OK.

    image
  3. In the next screen click Attach to External Data Source.

    image
  4. In the Attach Data Source Wizard form select Database and click next.
    image
  5. In the Connection Properties window enter the server name\instance and Select the database you created in step 1. Click OK.

    image 
  6. Check Tables and use the default name in Specify the name of the data source in the Choose your Database Objects step. Click Finish.

    image 

You have imported the data model into LightSwitch.

 

Changing the data model

When you look at the LightSwitch designer in your data model you will notice LightSwitch made some small name changes, describing the relations. Do not try to correct these. Instead change the display name in the properties window. As I did for the source connection in this screenshot.

image

 

Creating the screens

Creating screens is even easier than creating the data model.

  1. Right-Click Screens in Solution Explorer and choose Add Screen ..

    image

  2. In the Add New Screen dialog select Details Screen as template. Select MetaBaseData.ETLJob as Screen Data and check ETLJob TableMapping. Click OK.

    image

  3. After I created the screen I made some small changes in the designer: Moved some fields up or down by dragging and dropping and changed the number of Lines in the SQL Statement field from 1 to 3.

    image

I repeated steps 1 and 2 to create additional screens:

Template Screen Data
Search Data Screen Connections
Search Data Screen ETLJobs
Search Data Screen TableMappings
New Data Screen Connections
New Data Screen ETLJobs 2)
New Data Screen TableMappings 1)
Details Screen Connections
Details Screen TableMappings 1)

1) Add ColumnMappings under Additional data to include
2) Add TableMappings under Additional data to include

 

Navigation, Shell and Theme

Right-Click Screens en select Edit Screen navigation from the context menu. Change the screen navigation to the following image:

image

Choose General Properties and change the Shell to LightSwitch Standard Shell and the Theme to LightSwitch Blue Theme.

image

 

Conclusion

With Visual Studio LightSwitch you can easily create an application to maintain the meta data for your BIML solution. You create a normalized data model. Import it in LightSwitch and start adding screens. And if you like BIML you’ll probably like LightSwitch as well: Both make it easier to program your solution.

An additional advantage of storing the meta data in separate tables and creating an application to maintain that data is that you have a great overview of your data lineage and up to date documentation. Here are some screenshots of the application with data.

image
Search ETLJob screen showing the packages and SQL Statements of our solution.

image
An ETL Job detail screen with the associated table mappings.

image
A Table Mapping detail screen with the associated column mappings.

» Similar Posts

  1. DWH Deck 2.0: How to change or add templates
  2. Four Approaches to Data Warehousing
  3. 8 Practical BIML Tips

» Trackbacks & Pingbacks

    No trackbacks yet.

» Comments

    There are no comments.

Comments are closed