Dennis and Jim's Data Engineering Blog: C#

Showing posts with label C#. Show all posts

Tuesday, May 26, 2015

Uploading a JSON Feed into Azure Blob Storage using an ASP.NET Web API

Been diving into Microsoft's cloud offering and wanted to share a little exercise I went through to get a JSON feed into Azure storage. This assumes that Azure storage is already set up for your account . The wizards make this pretty easy to execute.

For this example I used a public JSON feed from the National Centre for Text Mining in the UK (thus spellchecker yelling at me for "Centre"). They have a RESTful API that you can submit acronyms to, and it will return words that may fit those acronyms in a JSON format that looks something like this:

(Example for ABC)

[
   {
      "sf":"ABC",
      "lfs":[
         {
            "lf":"ATP-binding cassette",
            "freq":1437,
            "since":1990,
            "vars":[
               {
                  "lf":"ATP-binding cassette",
                  "freq":1057,
                  "since":1990
               }
            ]
         }
      ]
   }
]

To easily and repeatably get this data into Azure Storage, I'm going to create my own RESTful Web API in .net to assist me. To do this I'm going to create a new ASP.NET Web Application in Visual Studio:

Figure 1. ASP.NET Project

When prompted, choose Web API:

Figure 2. Template Selection Screen

This will have created our project and we can get started. First this we want to do is get the configurations right so that we can connect to our Azure Storage. In the Azure storage area select which storage container you want the JSON feed to be stored in:

Figure 3. Azure Storage Page

Then click on Manage Access Keys in the menu at the bottom of the screen:

Figure 4. Manage Access Keys

Here you will need to grab your storage account name and primary access key for our project. We are going to need these values for our Web.config in our Visual Studio solution. Open up web config and add values in <appSettings>

The second place we need to edit is <connectionStrings>

<connectionStrings> <add name="StorageConnectionString" connectionString=

 "DefaultEndpointsProtocol=https;AccountName=jymbo;AccountKey=wWlWqJoG+sZou5m/..." />

</connectionStrings>



To be able to write to cloud storage we'll need to add some namespaces to our project. For 

this we can use NuGet. Right click on the project and click Manage NuGet Packages:





Figure 5. Manage NuGet Packages 



Do a search for Windows Azure Storage, click install to add to your project.




Figure 6. Windows Azure Storage



We can finally start coding! Now we need to add a controller to our project that can accept inputs to

our API:

Figure 7. Add Controller

Choose Web API 2 Controller - Empty:

Figure 8. Controller Type

For this example I named my controller BlobController:

Figure 9. Controller Name

In BlobController.cs paste this code:


using System;

using System.Net;

using System.Web.Http;

using Microsoft.WindowsAzure;

using Microsoft.WindowsAzure.Storage;

using Microsoft.WindowsAzure.Storage.Auth;

using Microsoft.WindowsAzure.Storage.Blob;

using System.IO;



namespace JSONBlobStorage.Controllers

{

    public class BlobController : ApiController

    {

        CloudBlockBlob blockBlob;



        string wUrl = "http://www.nactem.ac.uk/software/acromine/dictionary.py?sf=";

        string jsonString;

        // GET: api/Blob/5

        public string Get([FromUri]string acronym)

        {

            ConnectToAzure(acronym);



            LoadJSONTOBlobStorage(acronym);



            return "File containing JSON for acronym "+acronym+" has been uploaded";

        }



        private void ConnectToAzure(string acronym)

        {



            // Retrieve storage account from connection string.

            CloudStorageAccount storageAccount = CloudStorageAccount.Parse(

            CloudConfigurationManager.GetSetting("StorageConnectionString"));



            // Create the blob client.

            CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();



            // Retrieve a reference to a container. 

            CloudBlobContainer container = blobClient.GetContainerReference("jymbo");



            // Create the container if it doesn't already exist.

            container.CreateIfNotExists();




            // Retrieve reference to a blob named "Acronym*".

            blockBlob = container.GetBlockBlobReference("acronym/"+acronym+".txt");

        }



        private void LoadJSONTOBlobStorage(string acronym)

        {

            HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl+acronym);




            HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();



            //Test the connection

            if (httpWResp.StatusCode == HttpStatusCode.OK)

            {

                Stream responseStream = httpWResp.GetResponseStream();



                //Set jsonString using a stream reader

                using (StreamReader reader = new StreamReader(responseStream))

                {

                    jsonString =reader.ReadToEnd();

                }

                //Place top level array in an object a

                jsonString = "{\"a\":" + jsonString.TrimEnd('\r', '\n') + "}";



                //Upload JSON to blob storage

               blockBlob.UploadText(jsonString);



            }



        }

    }

}
     I did not add any exception handling, as this was only for demonstration purposes. We have a Get

that will accept an acronym parameter that we want to pass to the acronym API, a ConnectToAzure

method to will authenticate us to Azure blob storage and a LoadJSONTOBlobStorage method that will

pull the JSON from the RESTful API and upload it to blob storage. You'll notice that I read the stream

into a string and concatenate the JSON with { a: .. }. This is to wrap the top level array we receive in

 an object so our JSON will be easier to manage when we deserialize:


{  

   "a":[  

      {  

         "sf":"ABC",

         "lfs":[  

            {  

               "lf":"ATP-binding cassette",

               "freq":1437,

               "since":1990,

               "vars":[  

                  {  

                     "lf":"ATP-binding cassette",

                     "freq":1057,

                     "since":1990

                  }

               ]

            }

         ]

      }

   ]

}

If I did not need to edit the JSON before storage, I could have called blockBlob.UploadFromStream(responseStream);

 , passed the stream and got the same results. I got the code for the Azure authentication here. This example is

using C#, but there are a couple other examples using other languages in that post.

     Now we need to publish this project to Azure. Right click the project and choose Publish:

Figure 10. Publish

On the Publish Web screen select Microsoft Azure Web Apps:

Figure 11. Publish Web Screen

This will prompt you to log into your Azure account. When prompted for existing or new, click the New button:

Figure 12. Existing Web App Screen

Then enter a name for your web app and click Create:

Figure 13. Choose App Name Screen

You will then go through a series of screens to validate your entries, here you can just click the Publish

button to create your site. Your Output window should be giving you a status of what is being pushed up to

 Azure. When completed you should see something like this:

Figure 14. Output Window

Once completed your site should launch in your browser. Now we're ready to load some JSON to Azure Storage.

The API takes a single parameter for the acronym. Once we pass this parameter the Web API will call the RESTful

 web service, return our JSON and upload it to Azure Storage.

     To test it out we can call http://jsonblobstorageexample.azurewebsites.net/api/Blob?acronym=abc. Which

should upload a JSON file for acronym data for ABC and produce the following ugly message:


<string xmlns="http://schemas.microsoft.com/2003/10/Serialization/">

File containing JSON for acronym abc has been uploaded

</string>




If we look in our Azure storage account we should now see that file:





Figure 15. Azure Storage

We can use our server explorer in Visual Studio to download the file and verify the contents:

Figure 16. Visual Studio Server Explorer

Hit the Save As Button and open in a local directory:

Figure 17. Download File from Blob Storage

Figure 18. abc.txt Contents

     Keep in mind that whenever you pass abc it will overwrite the existing abc.txt file in blob storage. This

example could always be expanded on to pass a file name parameter to get around this if desired. If you

pass parameter xyz for acronym, it will create a blob called acronym/xyz.txt and so on.

Saturday, February 14, 2015

Expanding on Embarcadero's ER/Studio Data Architect HTML Reporting for Data Lineage

I've been using ER/Studio Data Architect for a number of years now for data modeling. There are pluses and minuses to it, like everything, but overall I think its a good tool for what I need it to do. With that said, there is one area I would like to see them go in a different direction...data lineage. They have a tab where you can add data sources and create data lineage diagrams. Which is good..but I think their data lineage diagrams are too clunky, time consuming to build, hard to maintain and difficult to understand. Even on their product page for this you can see how busy it is. Users want data lineage to easily see data ancestry, gauge impact analysis and view transformations along the way. I think these diagrams are ill suited for all of these. However; there is one saving grace with this product in this respect and that is the data lineage feature at the column level in a column's definition. Because of this we can hijack the metadata entered here for a new HTML report that, I feel, will better illustrate data lineage.

To accomplish this, we're going to need to make use of elements of their HTML report, provided with the product, as well as making use of their application's API. But before getting into the weeds, let's take the 1000ft view and look at the data model example I'm going to use to help explain how all this works. This ER/Studio model represents the databases involved in supporting a data warehouse architecture. You can see we have our staging database, data warehouse and data mart:

Figure 1. Data Model Tab of ER/Studio

On the Data Lineage tab we have a sample database that will be used as a source of data for the data warehouse databases:

Figure 2. Data Lineage Tab of ER/Studio

In the source system database we have a table called person that were going to pull data from in this example. It has 4 columns which include a numeric identifier and some descriptions for a person:

Figure 3. Person Table from Source Database

The first destination for the data in this table will be the Staging database in the stage.Person table. In order to associate these 2 tables for lineage purposes, we need to allow The Source database to be available for lineage to the Staging database. On the Data Model tab right click The Staging database in the tree view and click on Data Movement Properties. This will bring up a form that will allow you to pick available sources for the Staging database. Click on the add button and select the SourceSystem database:

Figure 4. Data Movement Sources/Targets for Staging

This will make SourceSystem table columns available to be used as a source by tables in the Staging database. In Staging, let's double click on the stage.Person table, then double click the PersonID column. On the bottom of this form you should see a Data Lineage tab(You may need to scroll to the right to see it). On that tab click the edit button to expose the Edit Source Mappings form for the column. This is where we can choose the source(s) for the PersonID column in stage.Person:

Figure 5. Edit Source Mappings for stage.Person

     Here we check the PersonID column from the source database person table. You'll notice in the transformation logic text box I put the owner of the table. This is because in the objects offered by the ER/Studio API, the one for data lineage does not contain this attribute. Because we can have multiple tables with the same name in the same database, but with different schemas, we need to give the application a way to uniquely identify this source column. For the transformation description, since we are just doing a straight copy from source, I put "None" for transformations performed. Click OK. This will bring you back to the Data Lineage tab. You'll notice the information on the Direct Sources sub-tab will now be filled in. In addition to this tab you should also see tabs labeled "Direct Targets" and "Secondary Targets". These you can ignore. Embarcadero implicitly expects you to fill in children for the column. For this solution this is not necessary, we will make use of recursion to derive ancestors and children for a particular column. All we need to worry about is entering a column's direct ancestor(s).

   This process can be repeated for the DataWarehouse, sourcing the Staging database for it's sources:

Figure 6. Person Data Vault Model

Here we decompose the person table, using a data vault modeling approach, to create a person hub with associated satellites. For each of these tables we repeat the steps we did for the stage.Person table. Here is an example using the LastName column in the satellite.PersonLastName table:

Figure 7. Edit Source Mappings for satellite.PersonLastName

Just like the last example we check the column we're sourcing, enter the source column schema name and in this example we have a rtrim transformation happening on the column.

The last stop for data will be a conformed dimension in our data mart for person:

Figure 8. Person Dimension in DataMart Database

You'll notice a new column has appeared that was not there before, FullName. This is to demonstrate what happens when a column has more than 1 source. On the Edit Source Mapping for this column we have to do something slightly different to record lineage:

Figure 9. Edit Source Mappings for dims.DimPerson.FullName

The first thing that should stand out is the Transformation Logic text box. Here we have a comma separated list of schemas for the tables selected in the order they are added as sources. We have 3 tables, thus we enter the schemas they belong to in order they appear:

Figure 10. dims.DimPerson Table Editor

Now the hard part is over. We did our data entry and are ready to start getting into how to publish our data lineage. But first, we need to produce an HTML report for this ER/Studio model, because our data lineage reporting solution needs elements produced by this. By right clicking on any of the databases on the Data Model tab we can get a menu that will allow us to produce reports:

Figure 11. Database Menu

This will bring up a wizard that will guide you through generating reports for your model, make sure to select HTML report for your report type:

Figure 12. ER/Studio Data Architect Report Wizard

When you complete the wizard and the reports are generated you should see this in your target directory:

Figure 13. HTML Report Root Folder

In the C:\DataWarehouseReport directory, create a new subdirectory called DataLineage. This will be our home for our data lineage HTML report. In this directory create 3 files index.htm, top.htm and tree.htm with the following code:

index.htm:

</frameset>

top.htm:

<html>

</td>

<B><FONT SIZE=5 FACE="Arial" color="darkblue">ER/Studio Data Architect HTML Data Lineage Report</FONT></B></TD>

</tr>

</table>

</td>

</tr>

</table>

</body>

</html>

tree.htm:

<html>

<head>

<style>

BODY {background-color: white}

TD {font-size: 9pt;font-family: Arial;text-decoration: none;white-space: nowrap;}

A {text-decoration: none;color: black}

</style>

</head>

</html>

Finally, we build the program that will pull the metadata out of ER/Studio Data Architect and create our data lineage report. For this I created a C# program using the .net 3.5 library since the dll provided by Embarcadero for COM will not work in any higher .net version. I built a C# console application for this, but it can just as easily be refactored for a form based project as well. In your project we need to make sure we add the dll so we have access to the COM objects necessary to pull this off. In visual studio go to Project-->Add Reference-->COM. Make sure ER/Studio Type Library is there:

Figure 14. Reference Manager

If not, THIS WILL NOT WORK. Embarcadero tech support can help you get this installed if it wasn't installed with ER/Studio. This program requires 2 arguments, The file path to the dm1 file for your ER/Studio diagram and the file path for where you want to push the data lineage HTML report to:

Figure 15. Command Line Arguments

Here is the code for the program:

using System;

using System.Collections.Generic;

using System.Text;

using ERStudio;

using System.IO;

using System.Linq;

using System.Text.RegularExpressions;

namespace DataLineage

{

class DataLineage

{

//Erstudio application object

private ERStudio.Application _app;

//Erstudio diagram object

private ERStudio.Diagram _diag;

//The file path to the erstudio diagram (local path)

private string _outPutFilePath;

//Class to hold attributes of column children

private class Child

{

public int key;

public int child;

public string childName;

public string transformDesc;

}

//Collection that contains column ancestors

private Dictionary<int, ERStudio.AttributeObj> _Ancestors = new Dictionary<int, AttributeObj>();

//Collection that contain column children

private Lookup<int, Child> _Children;

//Initial List of children needed to load lookup

private List<Child> _Child = new List<Child>();

static void Main(string[] args)

{

//The file path to the erstudio diagram (local path)

string diagramPath = args[0];

//The file path to the .js file for output

string filePath = args[1];

//Pass arguments

DataLineage my_app = new DataLineage(diagramPath, filePath);

}

/// <summary>

/// This handles the main program control flow

/// </summary>

/// <param name="diagramPath">The file path to the erstudio diagram (local path)</param>

/// <param name="filePath">he file path to the .js file for output</param>

public DataLineage(string diagramPath, string filePath)

{

this._app = OpenApp();

this._diag = OpenDiagram(diagramPath);

this._outPutFilePath = filePath;

//Loads erstudio objects into memory

LoadERStudioObjects();

//Prints out the .js file

PrintJavascriptNodes();

//Closes ErStudio

CloseApp();

}

/// <summary>

/// Loads columns from the ERStudio model into C# dictionary

/// </summary>

private void LoadERStudioObjects()

{

Console.WriteLine("Loading ERStudio objects into memory");

//Loop through models

foreach (ERStudio.Model md in _diag.Models())

{

if (md.Name.ToString() != "Logical")

{

Console.WriteLine("Loading " + md.Name + " tables");

//Loop through tables

foreach (ERStudio.Entity ent in md.Entities())

{

Console.WriteLine("Loading " + md.Name + "." + ent.TableName + " attributes ");

//Load the columns into a collection if they have lineage

foreach (ERStudio.AttributeObj attr in ent.Attributes())

{

//Load Ancestors

if(Convert.ToInt32(attr.DataMovementColumnLinks().Count) > 0)

{

_Ancestors.Add((md.Name + "." + ent.Owner +"." + ent.TableName + "." + attr.ColumnName).ToLower().GetHashCode(), attr);

int i = 0;

string[] transformationLogic = Regex.Replace(attr.SourceDirectTransformationLogic.ToString(), @"\r\n?|\n", " ").Split(',');

string transformationDesc = Regex.Replace(attr.SourceDirectTransformationDescription.ToString(), @"\r\n?|\n", " ").Replace(@"""", @"\""").Trim();

//Load Children

foreach (DataMovementColumnLink dcl in attr.DataMovementColumnLinks())

{

Child cd = new Child();

string owner = "";

//Protection from wrong schema or no schema entry or no source column entry

if (transformationLogic.Count() >= (1) && dcl.SrcTrgtColumnName !=null && dcl.SrcTrgtColumnName.ToString()!="")

{

try

{

owner = transformationLogic[i].Trim();

}

catch(Exception e)

{

Console.WriteLine("cant write ancestor");

}

//Ancestor hash

cd.key = (dcl.SrcTrgtModelName + "." + owner + "." + dcl.SrcTrgtTableName + "." + dcl.SrcTrgtColumnName).ToLower().GetHashCode();

//Child hash

cd.child = (dcl.PhysicalModelName + "." + ent.Owner + "." + dcl.PhysicalTableName + "." + dcl.PhysicalColumnName).ToLower().GetHashCode();

//Child Name

cd.childName = dcl.PhysicalModelName + "."+ ent.Owner + "." + dcl.PhysicalTableName + "." + dcl.PhysicalColumnName;

//Child transformation description

cd.transformDesc = transformationDesc;

_Child.Add(cd);

i++;

}

//Load children into a lookup for faster searches

_Children = (Lookup<int, Child>)_Child.ToLookup(c => c.key);

}

/// <summary>

/// This method prints out the javascript nodes

/// </summary>

private void PrintJavascriptNodes()

{

//Create/overwrite existing file

if (File.Exists(_outPutFilePath))

{

File.Delete(_outPutFilePath);

}

//Write javascript to file

using (System.IO.StreamWriter file = new System.IO.StreamWriter(_outPutFilePath, true))

{

file.WriteLine(" ");

file.WriteLine(" ICONPATH = '../Support/'");

file.WriteLine(" USEICONS = 1");

file.WriteLine("{");

file.WriteLine("foldersTree = gFld('', '')");

file.WriteLine("foldersTree.iconSrc = ICONPATH + 'Gif/globe.gif'");

file.WriteLine("Diag_Node = insFld(foldersTree, gFld('Data Lineage', ''))");

file.WriteLine("Diag_Node.iconSrc = ICONPATH + 'Gif/data_lineage.gif'");

file.WriteLine("Diag_Node.iconSrcClosed = ICONPATH + 'Gif/data_lineage.gif'");

//Add database nodes to file

PrintDatabaseNode(file);

//Add data source nodes to file

PrintOtherSources(file);

file.WriteLine("}");

}

/// <summary>

/// Prints out to specified file the javascript nodes for databases

/// </summary>

/// <param name="file">The file to print to</param>

private void PrintDatabaseNode(System.IO.StreamWriter file)

{

foreach (ERStudio.Model md in _diag.Models())

{

if (md.Name.ToString() != "Logical")

{

Console.WriteLine("Writing " + md.Name + " node");

file.WriteLine("Model_Node_" + md.GUID.ToString() + " = insFld(Diag_Node, gFld(" + "\"" + md.Name + "\"" + ", \"\"))");

if (md.Dimensional.ToString() == "True")

{

file.WriteLine("Model_Node_" + md.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/dimensionalmodel.gif\"");

file.WriteLine("Model_Node_" + md.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/dimensionalmodel.gif\"");

}

else

{

file.WriteLine("Model_Node_" + md.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/physical.gif\"");

file.WriteLine("Model_Node_" + md.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/physical.gif\"");

}

PrintTableNode(file, md);

}

/// <summary>

/// Prints out javascript nodes for tables for a given database

/// </summary>

/// <param name="file">The file to print to</param>

/// <param name="md">The erstudio database model</param>

private void PrintTableNode(System.IO.StreamWriter file, ERStudio.Model md)

{

Console.WriteLine("Writing table javascript nodes for " + md.Name);

//Pull all tables that exist in Model md

List<ERStudio.Entity> results = new List<ERStudio.Entity>();

foreach (ERStudio.Entity e in md.Entities())

{

results.Add(e);

}

//Order the list by schema then by table name

IOrderedEnumerable<ERStudio.Entity> orderedResults = results.OrderBy(s => s.Owner).ThenBy(s => s.TableName);

foreach (ERStudio.Entity t in orderedResults)

{

file.WriteLine("Table_Node_" + t.GUID.ToString() + " = insFld(Model_Node_" + md.GUID.ToString() + ", gFld(" + "\"" + t.Owner + "." + t.TableName + "\"" + ", \"\"))");

switch (t.DimModelTableType.ToString())

{

case "DIMENSION":

file.WriteLine("Table_Node_" + t.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/dimension.gif\"");

file.WriteLine("Table_Node_" + t.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/dimension.gif\"");

break;

case "FACT":

file.WriteLine("Table_Node_" + t.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/fact.gif\"");

file.WriteLine("Table_Node_" + t.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/fact.gif\"");

break;

case "SNOWFLAKE":

file.WriteLine("Table_Node_" + t.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/snowflake.gif\"");

file.WriteLine("Table_Node_" + t.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/snowflake.gif\"");

break;

case "BRIDGE":

file.WriteLine("Table_Node_" + t.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/bridge.gif\"");

file.WriteLine("Table_Node_" + t.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/bridge.gif\"");

break;

case "NAVIGATION":

file.WriteLine("Table_Node_" + t.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/navigation.gif\"");

file.WriteLine("Table_Node_" + t.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/navigation.gif\"");

break;

case "UNDEFINED":

file.WriteLine("Table_Node_" + t.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/undefined.gif\"");

file.WriteLine("Table_Node_" + t.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/undefined.gif\"");

break;

default:

file.WriteLine("Table_Node_" + t.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/entity.gif\"");

file.WriteLine("Table_Node_" + t.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/entity.gif\"");

break;

}

PrintColumnNode(file, t, md.Name.ToString());

}

/// <summary>

/// Prints out the javascript nodes for other sources

/// </summary>

/// <param name="file">The file to print to</param>

private void PrintOtherSources(System.IO.StreamWriter file)

{

file.WriteLine("Other_Folder_Node = insFld(Diag_Node, gFld(" +"\"Other Sources\"" + ", \"\"))");

file.WriteLine("Other_Folder_Node.iconSrc = ICONPATH + \"Gif/other.gif\"");

file.WriteLine("Other_Folder_Node.iconSrcClosed = ICONPATH + \"Gif/other.gif\"");

//Loop through models

foreach (ERStudio.DataLineageSource ds in _diag.DataLineageSources())

{

Console.WriteLine("Writing " + ds.Name + " node");

file.WriteLine("Other_Node_" + ds.GUID.ToString() + " = insFld(Other_Folder_Node, gFld(" + "\"" + ds.Name + "\"" + ", \"\"))");

if (ds.Type.ToString() == "Flat File")

{

file.WriteLine("Other_Node_" + ds.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/flat_file.gif\"");

file.WriteLine("Other_Node_" + ds.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/flat_file.gif\"");

}

else

{

file.WriteLine("Other_Node_" + ds.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/relational.gif\"");

file.WriteLine("Other_Node_" + ds.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/relational.gif\"");

}

PrintOtherSourceTableNode(file, ds);

}

/// <summary>

/// Prints out javascript nodes for tables for a given database

/// </summary>

/// <param name="file">The file to print to</param>

/// <param name="ds">The erstudio database model</param>

private void PrintOtherSourceTableNode(System.IO.StreamWriter file, ERStudio.DataLineageSource ds)

{

Console.WriteLine("Writing table javascript nodes for " + ds.Name);

//Pull all tables that exist in DataLineageSource ds

List<ERStudio.DataLineageTable> results = new List<ERStudio.DataLineageTable>();

foreach (ERStudio.DataLineageTable e in ds.DataLineageTables())

{

results.Add(e);

}

//Order the list by schema then by table name

IOrderedEnumerable<ERStudio.DataLineageTable> orderedResults = results.OrderBy(s => s.Owner).ThenBy(s => s.Name);

foreach (ERStudio.DataLineageTable t in orderedResults)

{

file.WriteLine("OtherTable_Node_" + t.GUID.ToString() + " = insFld(Other_Node_" + ds.GUID.ToString() + ", gFld(" + "\"" + t.Owner + "." + t.Name + "\"" + ", \"\"))");

file.WriteLine("OtherTable_Node_" + t.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/entity.gif\"");

file.WriteLine("OtherTable_Node_" + t.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/entity.gif\"");

PrintOtherColumnNode(file, t, ds.Name.ToString());

}

/// <summary>

/// Prints out javascript nodes for columns for a given table

/// </summary>

/// <param name="file">The file to print to</param>

/// <param name="t">The erstudio table</param>

/// <param name="modelName">The name of the database for the table</param>

private void PrintColumnNode(System.IO.StreamWriter file, ERStudio.Entity t, string modelName)

{

Console.WriteLine("Writing " + t.TableName.ToString() + " column javascript nodes");

//Pull all columns that exist in Table t

List<ERStudio.AttributeObj> results = new List<ERStudio.AttributeObj>();

foreach (ERStudio.AttributeObj a in t.Attributes())

{

results.Add(a);

}

//Order the list by column name

IOrderedEnumerable<AttributeObj> orderedResults = results.OrderBy(s => s.ColumnName);

foreach (AttributeObj c in orderedResults)

{

int columnHash = (modelName + "." + c.GetParent().Owner + "."+ c.GetParent().TableName + "." + c.ColumnName).ToLower().GetHashCode();

file.WriteLine("Column_Node_" + c.GUID.ToString() + " = insFld(Table_Node_" + t.GUID.ToString() + ", gFld(" + "\"" + c.ColumnName +"\"" + ", \"\"))");

if (c.PrimaryKey.ToString() == "True")

{

if (c.ForeignKey.ToString() == "False")

{

file.WriteLine("Column_Node_" + c.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/pkattr.gif\"");

file.WriteLine("Column_Node_" + c.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/pkattr.gif\"");

}

else

{

file.WriteLine("Column_Node_" + c.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/pkfkattr.gif\"");

file.WriteLine("Column_Node_" + c.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/pkfkattr.gif\"");

}

else if (c.ForeignKey.ToString() == "True")

{

file.WriteLine("Column_Node_" + c.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/foreign.gif\"");

file.WriteLine("Column_Node_" + c.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/foreign.gif\"");

}

else

{

file.WriteLine("Column_Node_" + c.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/attr.gif\"");

file.WriteLine("Column_Node_" + c.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/attr.gif\"");

}

file.WriteLine("Ancestor_Folder_Node_" + c.GUID.ToString() + " = insFld(Column_Node_" + c.GUID.ToString() + ", gFld(" + "\"Ancestors\"" + ", \"\"))");

file.WriteLine("Ancestor_Folder_Node_" + c.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/bluefldr.gif\"");

file.WriteLine("Ancestor_Folder_Node_" + c.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/blueclsd.gif\"");

//Print ancestors to javascript file

PrintAncestors(file, c, "Ancestor_Folder_Node_" + c.GUID.ToString());

file.WriteLine("Child_Folder_Node_" + c.GUID.ToString() + " = insFld(Column_Node_" + c.GUID.ToString() + ", gFld(" + "\"Children\"" + ", \"\"))");

file.WriteLine("Child_Folder_Node_" + c.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/bluefldr.gif\"");

file.WriteLine("Child_Folder_Node_" + c.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/blueclsd.gif\"");

Console.WriteLine("Searching for children for " + c.ColumnName.ToString());

//Print children to javascript file

PrintChildren(file, columnHash, "Child_Folder_Node_" + c.GUID.ToString());

}

/// <summary>

/// Prints out javascript nodes for columns for a given other source table

/// </summary>

/// <param name="file">The file to print to</param>

/// <param name="t">The other source table</param>

/// <param name="modelName">The name of the database for the table</param>

private void PrintOtherColumnNode(System.IO.StreamWriter file, ERStudio.DataLineageTable t, string modelName)

{

Console.WriteLine("Writing " + t.Name.ToString() + " column javascript nodes");

//Pull all columns that exist in Table t

List<ERStudio.DataLineageColumn> results = new List<ERStudio.DataLineageColumn>();

foreach (ERStudio.DataLineageColumn a in t.DataLineageColumns())

{

results.Add(a);

}

//Order the list by column name

IOrderedEnumerable<DataLineageColumn> orderedResults = results.OrderBy(s => s.ColumnName);

foreach (DataLineageColumn c in orderedResults)

{

int columnHash = (modelName + "." + t.Owner + "." + t.Name +"." + c.ColumnName).ToLower().GetHashCode();

file.WriteLine("OtherColumn_Node_" + c.GUID.ToString() + " = insFld(OtherTable_Node_" + t.GUID.ToString() + ", gFld(" + "\"" + c.ColumnName + "\"" + ", \"\"))");

if (c.PrimaryKey.ToString() == "True")

{

file.WriteLine("OtherColumn_Node_" + c.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/pkattr.gif\"");

file.WriteLine("OtherColumn_Node_" + c.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/pkattr.gif\"");

}

else

{

file.WriteLine("OtherColumn_Node_" + c.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/attr.gif\"");

file.WriteLine("OtherColumn_Node_" + c.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/attr.gif\"");

}

file.WriteLine("OtherChild_Folder_Node_" + c.GUID.ToString() +" = insFld(OtherColumn_Node_" + c.GUID.ToString() + ", gFld(" + "\"Children\""+ ", \"\"))");

file.WriteLine("OtherChild_Folder_Node_" + c.GUID.ToString() +".iconSrc = ICONPATH + \"Gif/bluefldr.gif\"");

file.WriteLine("OtherChild_Folder_Node_" + c.GUID.ToString() +".iconSrcClosed = ICONPATH + \"Gif/blueclsd.gif\"");

Console.WriteLine("Searching for children for " + c.ColumnName.ToString());

//Print children to javascript file

PrintChildren(file, columnHash, "OtherChild_Folder_Node_" + c.GUID.ToString());

}

/// <summary>

/// Returns a dictionary of ancestor id's for passed collection

/// </summary>

/// <param name="links">Collection of ancestors</param>

/// <returns>Dictionary of ancestor ids</returns>

private Dictionary<int, object> GenerateAncestorList(DataMovementColumnLinks links)

{

Dictionary<int, object> ancestorMarker = new Dictionary<int,object>();

int j = 0;

foreach (DataMovementColumnLink ancestor in links)

{

ancestorMarker.Add(j, ancestor.GUID);

j++;

}

return ancestorMarker;

}

/// <summary>

/// Prints out ancestors for a given column

/// </summary>

/// <param name="file">The file to print to</param>

/// <param name="c">The erstudio column</param>

/// <param name="ancestorNodeGUID">The identifier for the parent ancestor node so we can nest</param>

private void PrintAncestors(System.IO.StreamWriter file, ERStudio.AttributeObj c, string ancestorNodeGUID)

{

string ancestorName;

Dictionary<int, object> ancestorMarker = GenerateAncestorList(c.DataMovementColumnLinks());

//If there are source column(s) documented

if (Convert.ToInt32(c.DataMovementColumnLinks().Count) > 0)

{

Console.WriteLine("Searching for ancestors for " + c.ColumnName.ToString());

string[] transformationLogic = Regex.Replace(c.SourceDirectTransformationLogic.ToString(), @"\r\n?|\n", " ").Split(',');

string transformationDesc = Regex.Replace(c.SourceDirectTransformationDescription.ToString(), @"\r\n?|\n", " ").Replace(@"""", @"\""").Trim();

int i = 0;

//Write transformation description

file.WriteLine("Transform_Ancestor_Node_" + c.GUID.ToString() + " = insFld(" + ancestorNodeGUID + ", gFld(" + "\"Transformation: " + transformationDesc + "\"" + ", \"\"))");

file.WriteLine("Transform_Ancestor_Node_" + c.GUID.ToString() + ".iconSrc = ICONPATH + \"Gif/Transformation.gif\"");

file.WriteLine("Transform_Ancestor_Node_" + c.GUID.ToString() + ".iconSrcClosed = ICONPATH + \"Gif/Transformation.gif\"");

//Loop through ancestry

while (i < c.DataMovementColumnLinks().Count)

{

//Return object based on id from dictionary

DataMovementColumnLink ancestor = c.DataMovementColumnLinks().get_Item(ancestorMarker[i]);

//Find current ancestor column object in list

ERStudio.AttributeObj result;

string owner = "";

//Protection from wrong schema or no schema entry

if (transformationLogic.Count() >= (i + 1))

{

owner = transformationLogic[i].Trim();

}

ancestorName = ancestor.SrcTrgtModelName.ToString() + "."+ owner + "." + ancestor.SrcTrgtTableName.ToString() + "." + ancestor.SrcTrgtColumnName.ToString();

Console.WriteLine("Found " + ancestorName);

file.WriteLine("Ancestor_Node_" + c.GUID.ToString() + i.ToString() + " = insFld(" + ancestorNodeGUID + ", gFld(" + "\"Ancestor Column: " + ancestorName + "\"" + ", \"\"))");

file.WriteLine("Ancestor_Node_" + c.GUID.ToString() + i.ToString() + ".iconSrc = ICONPATH + \"Gif/TransSelectInto.gif\"");

file.WriteLine("Ancestor_Node_" + c.GUID.ToString() + i.ToString() + ".iconSrcClosed = ICONPATH + \"Gif/TransSelectInto.gif\"");

if (_Ancestors.TryGetValue(ancestorName.ToLower().GetHashCode(), out result))

{

//If the ancestor of ancestor also has an ancestor

if(Convert.ToInt32(result.DataMovementColumnLinks().Count) > 0)

{

//Recursively find ancestor of current ancestor

PrintAncestors(file, result, "Ancestor_Node_" + c.GUID.ToString() + (i).ToString());

}

i++;

}

/// <summary>

/// Prints out children for a given column

/// </summary>

/// <param name="file">The file to print to</param>

/// <param name="columnHash">The hashvalue of the column name</param>

/// <param name="childNodeGUID">The identifier for the parent child node so we can nest</param>

private void PrintChildren(System.IO.StreamWriter file, int columnHash, string childNodeGUID)

{

int i = 0;

//If there are children column(s) documented

foreach (Child cd in _Children[columnHash])

{

//Write transformation description

file.WriteLine("Transform_Child_Node_" + ((columnHash.ToString()) + (i.ToString())).Replace("-", "z") + " = insFld(" + childNodeGUID + ", gFld(" + "\"Transformation: " + cd.transformDesc + "\"" +", \"\"))");

file.WriteLine("Transform_Child_Node_" + ((columnHash.ToString()) + (i.ToString())).Replace("-", "z") + ".iconSrc = ICONPATH + \"Gif/Transformation.gif\"");

file.WriteLine("Transform_Child_Node_" + ((columnHash.ToString()) + (i.ToString())).Replace("-", "z") + ".iconSrcClosed = ICONPATH + \"Gif/Transformation.gif\"");

file.WriteLine("Child_Node_" + ((columnHash.ToString()) + (i.ToString())).Replace("-", "z") + " = insFld(" + childNodeGUID + ", gFld(" +"\"Child Column: " + cd.childName + "\"" + ", \"\"))");

file.WriteLine("Child_Node_" + ((columnHash.ToString()) + (i.ToString())).Replace("-", "z") + ".iconSrc = ICONPATH + \"Gif/TransGroupBy.gif\"");

file.WriteLine("Child_Node_" + ((columnHash.ToString()) + (i.ToString())).Replace("-", "z") + ".iconSrcClosed = ICONPATH + \"Gif/TransGroupBy.gif\"");

//Recursively find children of current child

PrintChildren(file, cd.child, "Child_Node_" + ((columnHash.ToString()) + (i.ToString())).Replace("-", "z"));

i++;

}

/// <summary>

/// Closes the Erstudio application

/// </summary>

private void CloseApp()

{

//this.app.Quit();

_app.CloseDiagram(_diag.FileName);

_app.Quit();

}

/// <summary>

/// Opens the Erstudio application

/// </summary>

/// <returns>An instance of the Erstudio application</returns>

private ERStudio.Application OpenApp()

{

ERStudio.Application app = new ERStudio.Application();

//app.HideWindow();

return app;

}

/// <summary>

/// Opens a specific Erstudio diagram

/// </summary>

/// <param name="FileName">The file path to the diagram</param>

/// <returns>An Erstudio diagram object</returns>

private ERStudio.Diagram OpenDiagram(string FileName)

{

ERStudio.Diagram diag = _app.OpenFile(FileName);

return diag;

}

/// <summary>

/// Opens an Erstudio model

/// </summary>

/// <param name="ModelName">The name of the Model</param>

/// <returns>An Erstudio model object</returns>

private ERStudio.Model OpenModel(string ModelName)

{

ERStudio.Model mod = _diag.Models().Item[ModelName];

return mod;

}

An important thing to know is that ER/Studio Data Architect should be closed when kicking this off, or else you will get an exception. Keep in mind that sometimes it may stay in your task manager and the process may need to be killed. After the program finishes running you can open file:///C:/DataWarehouseReport/DataLineage/index.htm to view your data lineage report:

Figure 16. DataWarehouse Data Lineage

Here is a screenshot showing data lineage for the satellite.PersonFirstName.FirstName column. The tree view shows the line of ancestry for this column, all the way back to the source system, as well as all the children of this column for impact analysis. Its in a collapsible tree that's easy to navigate and easy to understand:

Figure 17. Foreign Key Lineage

You can also derive lineage from foreign keys as well and see the same lineage for that same column in the table where it is a primary key.

Figure 18. Data Mart Data Lineage

In this screenshot you can see the multiple ancestors for the dims.Person.FullName column, showing that you can access more than 1 ancestor in your report if present:

Figure 19. Staging Data Lineage

This screenshot shows the data lineage for Staging database, where it has 1 ancestor from the source system and a whole lot of children.

Figure 20. Data Source Data Lineage

The last screenshot shows the children of the source system. So when source system developers tell you they are making changes to the FirstName column in the Person table, you can quickly assess the impact in the tree view.

The one thing I would love Embarcadero to add to their API would be the SrcTrgtTableOwnerName to the DataMovementColumnLinkClass:

Figure 21. DataMovementColumnLinkClass

That would free up the Transformation Logic text box and the owner could be handled in code behind the scenes. Petition Embarcadero...they may do it, if enough people ask for it.