Month: June 2016

Biml, Microsoft Technologies, SSIS

BimlScript – Get to Know Your Control Nuggets

Meagan Longoria

This is post #2 of my BimlScript – Get to Know Your Code Nuggets series. To learn about text nuggets, see my first post.  

The next type of BimlScript code nugget I’d like to discuss is the control nugget. Control nuggets allow you to insert control logic to determine what Biml is generated and used to create your BI artifacts (packages, cubes, etc.).  Control nuggets can be used to:

  • Add conditional logic so that specified Biml fragments are only generated when certain criteria are met
  • Add loops to repeat Biml fragments multiple times
  • Define variables that will be used later
  • Access external data from databases, flat files or other sources that will be used in the generation of the final Biml code

Control nuggets start with <# and end with #>. Just like text nuggets, control nuggets can be a single line or multiple lines. And they can contain simple or complex logic.

There were actually control nuggets in the text nugget example from the previous post. The variable declarations at the top of the file (lines 4 – 10) are control nuggets.


<#* The variables below are control nuggets.
They will be discussed later but are needed for ths example.*#>
<#
var PackageName = "Stage_AW_SalesReason";
var SourceTable = "SalesReason";
var SourceSchema = "Sales";
var DestinationTable = "SalesReason";
var DestinationSchema = "Staging";
#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd"&gt;
<Connections>
<OleDbConnection Name="AW2014"
ConnectionString = "Data Source=.\SQL2014;Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;Provider=SQLNCLI11.1;"/>
<OleDbConnection Name="MyDataMart"
ConnectionString = "Data Source=.\SQL2014;Initial Catalog=AWBIML;Integrated Security=SSPI;Provider=SQLNCLI11.1;"/>
</Connections>
<Packages>
<Package Name="<#=PackageName#>" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">
<Tasks>
<ExecuteSQL Name="SQL Truncate_<#=DestinationTable#>" ConnectionName="AW2014">
<DirectInput>Truncate Table [<#=DestinationSchema.ToUpper()#>].[<#=DestinationTable.ToUpper()#>];</DirectInput>
</ExecuteSQL>
<Dataflow Name="DFT Stage_<#=DestinationTable#>">
<Transformations>
<OleDbSource Name="OLE_SRC <#=SourceTable#>" ConnectionName="AW2014" >
<DirectInput>SELECT * FROM [<#=SourceSchema.ToUpper()#>].[<#=SourceTable.ToUpper()#>];
</DirectInput>
</OleDbSource>
<OleDbDestination Name="OLE_SRC <#=DestinationSchema#><#=DestinationTable#>" ConnectionName="MyDataMart">
<ExternalTableOutput Table="<#=DestinationSchema#>.<#=DestinationTable#>"></ExternalTableOutput>
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Packages>
</Biml>

view raw

TextNuggetReplaceNames.biml

hosted with ❤ by GitHub

Below is another example file that uses code nuggets. I like to separate my design patterns. project connections, and package generation into separate files. The BimlScript shown below is from a slightly altered “caller file”, the file that I would execute to create the SSIS packages that should follow a specified design pattern. This caller file is generating Type 1 slowly changing dimensions.


<#*This file retrieves variable values from a database
and passes them to another Biml file file that contains
the design pattern for a type 1 SCD*#>
<#*The items directly below this comment are directives*#>
<#@ template language="C#" hostspecific="true" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.IO" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd"&gt;
<!–
<#
string AuditConnectionString = "Data Source=.\\SQL2014;Initial Catalog=AWBIML;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";
string SrcTableQuery = @"
SELECT
[PackageName]
,[DestinationSchemaName]
,[DestinationTableName]
,[DestinationConnection]
,[DataFlowSourceName]
,[SourceConnection]
,[DataFlowQuery]
,[UpdateSchemaName]
,[UpdateTableName]
,[UpdateConnection]
,[UpdateSQLStatement]
,[AuditConnection]
,[AuditSchema]
FROM [BIML].[DimensionPackageVariables]
where PackageType ='Dim1'
;
";
DataTable dt = null;
dt = ExternalDataAccess.GetDataTable(AuditConnectionString, SrcTableQuery);
#>
–>
<#@ include file="ProjectConnections.biml" #>
<Packages>
<# foreach (DataRow dr in dt.Rows) { #>
<#=CallBimlScript("Dim1.biml", dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(), dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString(), dr[11].ToString(), dr[12].ToString())#>
<# } #>
</Packages>
</Biml>

view raw

Dim1Caller.biml

hosted with ❤ by GitHub

In my Biml framework, I store the data I need in order to generate my packages in SQL tables. You can see the control nugget in lines 14 – 40 retrieving data from my database and storing it in a data table for later use.

On line 46, there is a control nugget containing a for each loop. For each row in the data table, it calls a BimlScript file that creates a package that uses my Type 1 SCD design pattern with the variables from data table. Notice that the end curly brace for my loop is in a separate control nugget.

There are many different ways to use control nuggets that aren’t covered here.  From these two examples you can start to see how I might combine text nuggets and control nuggets to automate my SSIS package creation and employ consistent design patterns. But there are still a few missing pieces that will be filled in when I cover the remaining code nugget types.

Biml, Microsoft Technologies, SSIS

BimlScript – Get to Know Your Code Nuggets

Meagan Longoria

gold nuggetsIn BimlScript, we embed nuggets of C# or VB code into our Biml (XML) in order to replace variables and automate the creation of our BI artifacts (databases, tables, SSIS packages, SSAS cubes, etc.). Code nuggets are a major ingredient in the magic sauce that is meta-data driven SSIS development using BimlScript.

There are 5 different types of code nuggets in BimlScript:

  • Text nuggets
  • Control nuggets
  • Class nuggets
  • Comments
  • Directives

Over the next several posts I’ll cover each type of code nugget and provide examples of their use.

Text Nuggets

Text nuggets evaluate the expression they contain and then replace the text nugget with the string representation of the value of the expression. I use them often to switch out names of packages, tasks, and components as well as source and destination tables in SSIS development when creating packages based upon a design pattern.

Text nuggets start with <#= and end with #>. Notice there is an equals sign at the beginning of the text nugget but not at the end.

Text nuggets are very useful. You can include complex business logic in the expressions. And the expression result can be any data type. The BimlScript compiler will automatically convert it to text before replacing the code nugget with the result. Like all code nuggets, text nuggets can be a single line or multiple lines.

Let’s take a look at some examples.

Note: The Biml script below has variables, connections, and a package all in one file for ease of demonstration. Normally I would split these out into separate files. 


<#* The variables below are control nuggets.
They will be discussed later but are needed for ths example.*#>
<#
var PackageName = "Stage_AW_SalesReason";
var SourceTable = "SalesReason";
var SourceSchema = "Sales";
var DestinationTable = "SalesReason";
var DestinationSchema = "Staging";
#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd"&gt;
<Connections>
<OleDbConnection Name="AW2014"
ConnectionString = "Data Source=.\SQL2014;Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;Provider=SQLNCLI11.1;"/>
<OleDbConnection Name="MyDataMart"
ConnectionString = "Data Source=.\SQL2014;Initial Catalog=AWBIML;Integrated Security=SSPI;Provider=SQLNCLI11.1;"/>
</Connections>
<Packages>
<Package Name="<#=PackageName#>" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">
<Tasks>
<ExecuteSQL Name="SQL Truncate_<#=DestinationTable#>" ConnectionName="AW2014">
<DirectInput>Truncate Table [<#=DestinationSchema.ToUpper()#>].[<#=DestinationTable.ToUpper()#>];</DirectInput>
</ExecuteSQL>
<Dataflow Name="DFT Stage_<#=DestinationTable#>">
<Transformations>
<OleDbSource Name="OLE_SRC <#=SourceTable#>" ConnectionName="AW2014" >
<DirectInput>SELECT * FROM [<#=SourceSchema.ToUpper()#>].[<#=SourceTable.ToUpper()#>];
</DirectInput>
</OleDbSource>
<OleDbDestination Name="OLE_SRC <#=DestinationSchema#><#=DestinationTable#>" ConnectionName="MyDataMart">
<ExternalTableOutput Table="<#=DestinationSchema#>.<#=DestinationTable#>"></ExternalTableOutput>
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Packages>
</Biml>

view raw

TextNuggetReplaceNames.biml

hosted with ❤ by GitHub

This BimlScript creates an SSIS package that truncates and reloads data in a single table.

Notice that you can use the text nuggets alone, together, or with literal text.

The <#=PackageName#> text nugget simply replaces the value for the name of the package with the current value of the PackageName variable I set at the top of the file.

The DataFlow Task name is a combination of text and a code nugget.

<Dataflow Name="DFT Stage_<#=DestinationTable#>">

This comes in handy if you would like to enforce certain naming standards. My personal preference is to use naming conventions suggested by Jamie Thomson, so I build them into my Biml and then use text nuggets as shown above to change the rest of the name to reflect the nature of the current task.

The truncate table statement in the Execute SQL Task contains text and two code nuggets that reference a variable and use a function to convert the variable value into all uppercase text:

Truncate Table [<#=DestinationSchema.ToUpper()#>].[<#=DestinationTable.ToUpper()#>];

Hopefully you can see how text nuggets are useful when creating multiple packages at the same time. They allow you to switch out the values that change for each package and create consistent naming standards.

To get to know other types of code nuggets, see Get To Know Your Control Nuggets.

PASS Summit, Personal

My PASS Summit Abstract Feedback

Meagan Longoria

I wasn’t selected to speak at the PASS Summit this year. One of my talks was selected as an alternate, but I’m not holding out hope it will move up. Update: My session got bumped from alternate to scheduled. See here for more information. 

While I am a bit disappointed, I’m ok with it.  I delivered a general session and a lightning talk at PASS Summit 2014, then took a year off and didn’t submit anything in 2015. But I was on the abstract review committee last year and this year.  It’s interesting to see how abstracts are ranked and feedback is provided. I have a few quick thoughts on not being chosen:

  1. Congrats to everyone who was chosen! I am happy for you, and I hope you enjoy the experience.
  2. My career and my confidence as a speaker are just fine. If my perception of my career/speaking success rested solely/mostly on being selected to a single conference, I would probably need to re-evaluate my goals and priorities. I have learned a lot this year, and I’ve had some fun speaking opportunities so I’m satisfied with my accomplishments in the first half of 2016.
  3. Although my attendance at PASS Summit 2016 is TBD, part of me is happy that if I do get to attend, I will do so without having to worry about speaking. It’s nice to enjoy the company of others and stay out late without stressing over last-minute tweaks to demos or being tired while speaking.

I think it’s helpful for me to see others’ abstracts that were submitted and the feedback they received, so I’m sharing mine. It can be difficult to tell sometimes if you are missing something or if it was just luck/circumstances (there happened to be an abnormal amount of good abstracts on your same topic, etc.). While I can’t talk about the program committee or the criteria we used for judging abstracts, I will point out that if you look at the feedback you will see some common themes that may provide guidance for future abstract submissions.

I’ve listed the abstracts I submitted below along with the feedback they received as well as my thoughts.

Help! Someone Got Slowly Changing Dimensions in my Tabular Model! (Not Chosen)

Category: General Session
Track: BI Platform Architecture, Development & Administration
Topic: SSAS – Advanced Tips and Tricks
Level: 300
Session Abstract: Many developers shy away from slowly changing dimensions that capture history because they seem complicated, but they can be useful and worth the effort. We often need to see what an entity looked like at the time of an event rather than its current attributes; e.g., a customer’s marital status, address, and age at the time of purchase is useful for analyzing buying patterns. Having this historical view makes our analytics more accurate and useful. Many people can figure out how to add slowly changing dimensions to our SQL Server database. But what do we do when we get to our SSAS Tabular Model? It’s not quite as easy as trading the surrogate key for the natural key in your formulas. We’ll examine the mechanics of Type 2/6 slowly changing dimensions implemented in SQL Server. Then we’ll discuss good practices for adding slowly changing dimensions to the tabular model. Finally, we’ll review common DAX formulas and see how we should alter them to accommodate slowly changing dimensions.
Prerequisites: Basic familiarity with the idea of slowly changing dimensions, understanding of Tabular SSAS) and common DAX calculations.
Goals:

  • Define and explain the types of slowly changing dimensions
  • Provide tips on how to optimize loading of a type 2 or 6 slowly changing dimension
  • Demonstrate common calculations and how to change them to make them work with a type 2 or 6 slowly changing dimension
Comments
  • Sounds like it could be a good session but the abstract seemed all over the place. Hard to follow.
  • abstract seems to ramble. grammatical anomalies. punctuation misuse.
  • Fantastic topic, and extremely well-constructed abstract!
  • Well written abstract, sold me in easily. Seems to be on level. Clear goals.
  • Nice abstract with clear goals and outlines.
  • Abstract OK. Learning goals a bit roughly defined – could be more precise in this narrow topic.
  • Good abstract. Could be an interesting session to attendees.

My thoughts: There are some conflicting reviews, which is to be expected from a diverse group of reviewers. One of the reviewers seems to be extremely picky about grammar. I have reviewed the abstract, and I still do not see any grammatical issues. I am rather picky about grammar myself, but I think the goal of reviewing grammar in abstracts is to ensure that the abstract will be easily understood by attendees. As long as the abstract sounds professional and appropriately communicates what the session is about, tiny grammar issues and preferences don’t matter.

How to Care for Your Type 2 Slowly Changing Dimension (Not Chosen)

Category: Lightning Talk
Track: BI Platform Architecture, Development & Administration
Topic: SSIS – Building and Deploying Solutions
Level: 200
Session Abstract: Your type 2 slowly changing dimension (SCD2) needs love and proper care. Don’t over complicate your relationship with your SCD2, but also don’t let it feel unappreciated. Be straightforward and set ETL expectations appropriately. Be accepting if your type 2 slowly changing dimension decides he wants to be a type 6. This talk will help you understand your SCD2 and how to provide for him using SQL Server Data Tools and SSIS.
Prerequisites: High-level grasp of type 2 dimensions used to capture effective dated historical attributes. SSIS dev expertise to understand design patterns.
Goals:

  • Define a type 2 slowly changing dimension and how it works
  • Show example table DDL for a Type 2 slowly changing dimension
  • Show an example SSIS package with a good design pattern for loading a type 2 slowly changing dimension
Comments
  • can this really be covered in 10 minutes?
  • Misalignment in abstract, learning goals and level
  • Cute abstract. Title matches the abstract well; goals well defined and aligned. Would be a good lightning talk; level might be better suited for level 100
  • Really good abstract for a lightning talk. Sounds like a topic that a good amount of people and sounds like something I would enjoy attending.
  • Abstract is well written and easy to understand.
  • Good topic but not sure about whether the subject matter can effectively be presented in the time allotted.
  • Very focused topic – great for a lightning talk. Very good prerequisites and goals. The abstract is entertaining yet clear.
  • Well written abstract, on level
  • Well written and to the point.

My thoughts: I had fun writing this abstract, and I look forward to delivering this talk somewhere else. There seems to be some debate about whether slowly changing dimensions are a 100-level topic for SSIS. I think they are not. Whoever wrote that probably underestimates their own knowledge and has forgotten what SSIS basics at the 100 level are like. I think it would be a challenge to deliver in 10 minutes, and that is very valid feedback. I would have liked to try it though. Lightning talks are tough. They require a lot of editing and practice to pack useful, interesting, and coherent info into 10 minutes.

Overcoming Chartaphobia with Power BI (Alternate)

Category: General Session
Track: BI Information Delivery
Topic: Power BI
Level: 100
Session Abstract: Do reports in your organization consist mostly of giant tables of data? Perhaps you have gotten as far as adding KPI indicators or conditional highlighting to the tables. Maybe you have charts, but they are hideous and distracting. Although tables of data presented as reports are commonly found in many organizations, they may be doing you and your users a disservice. We’ll discuss why cognitive psychological studies tell us that graphs and pictures are more effective at communicating trends and comparisons and how to prepare to create good data visualizations. Then we’ll explore how to employ purposeful data viz designs to help users achieve their goal of making informed decisions, using a fun and useful Power BI dashboard. You’ll leave with guidance on how to take boring or unreadable tables of data and turn them into useful and visually appealing reports.
Prerequisites: Interest in data viz and/or Power BI
Goals:

  • Understand the right questions to ask when preparing to create a data viz solution
  • Explain 4 tips for effective data viz design
  • Demonstrate effective data viz design in a Power BI report and dashboard
Comments
  • Well written abstract and concise goals. I’m not sure if this will be of great interest
  • Don’t care for the data “viz” usage. This should be spelled out to visualization.
  • Whilst I get why the title might have chartaphobia in it – it still didn’t sound right. It’s not an actual word. The abstract is good, it does tell me that I may have charts but they’re “hideous and disgusting”. This didn’t sound nice and might turn some audience members away. The topic of Power BI is a good topic, the delivery just needs some work for a 100 level – explain earlier in the abstract how Power BI will make the charts better or exist at all.
  • Abstract: The level would be 200 Objective: I would like to attend this session.
  • I would’ve liked the goals to be a little more thorough.

My thoughts: I can’t imagine anyone being confused by the use of the word viz, but it can be a good rule of thumb not to include abbreviations or at least explain them. The comment about “chartaphobia” not being a real word made me laugh, but perhaps it doesn’t work well with an international audience. I would have put it in quotes, but I knew Orator tool we use doesn’t display those characters correctly. We all use different tactics to create catchy titles. I didn’t coin that phrase, but I think we all understand it isn’t a “real” word. I give this talk at SQL Saturdays and user groups and it goes over very well, so I’m not worried about the content of the session.

Building an Effective ETL Framework in SQL Server (Not Chosen)

Category: General Session
Track: BI Platform Architecture, Development & Administration
Topic: BIML
Level: 300
Session Abstract: An effective ETL framework helps you start and complete SSIS projects faster, employ reusable design patterns, and be consistent across packages and projects, lowering the learning curve for junior developers and your support team. Biml (Business Intelligence Markup Language) automates your SSIS design patterns and eliminates the manual repetition of solving familiar problems and making the same update over and over in similar packages. In this session, we’ll discuss and identify SSIS design patterns, learn how to create and automate them using BIML, and then see a demo of a package execution framework built using BIML.
Prerequisites: Basic proficiency in SSIS. Ability to read/understand XML and C# is helpful but not at all required.
Goals:

  • Provide a basic overview of BimlScript, how it works, and syntax for creating SSIS packages
  • Understand the usefulness of SSIS design patterns, identify common patterns, and practice abstracting details from a package to understand the pattern
  • Create a master package with an easily extensible execution framework, using BIML
Comments
  • Sounds like a good session.
  • Basic and 300 don’t coincide. Need to decide is it an advanced topic or not.
  • nice abstract
  • Good topic. More detail in the abstract about what will be covered would be helpful – include more of what was listed in the goals. Based on the abstract, the level seems too high, but it seems right based on the goals.
  • Seems like a lvl 200 session. Not the most interesting BIML abstract I’ve read.
  • Not sure I can identify the level 300 material in the abstract. Would recommend as level 200. Great story line and an important topic to teach; design patterns.
  • Well-developed outline and goals with details. The title may be clearer by adding BIML in it.
  • Good but average abstract. Could be an interesting session to some attendees.

My thoughts: This wasn’t my most inspired abstract writing. I agree it probably was more of a 200-level session than 300. I marked it as such because this session requires you to put all the pieces together and understand architecture more than just how to write Biml to make it do stuff.  In previous years there was no Biml track, so this year’s chosen abstracts will help me gauge how to write for this topic next year. I’m sure this session will continue to evolve as I deliver it more, and maybe I’ll come up with a better title and description.

Who needs SSAS when you have SQL? (Not Chosen)

Category: General Session
Track: BI Platform Architecture, Development & Administration
Topic: SSAS – Building and Deploying Solutions
Level: 200
Session Abstract: Analysis Services may seem foreign or unnecessary for SQL and .NET developers. However, it can offer many advantages for reporting and data exploration, even with the SQL engine’s latest indexing and in-memory analytics capabilities. In this session, we will cover useful features of SSAS and discuss conditions where it is beneficial. Next we’ll compare the two types of Analysis Services databases (Multidimensional and Tabular) and identify requirements that should influence your decision of which type is right for your solution. Then we will explore common ways to retrieve data and browse your SSAS database, such as Power BI and Excel, reporting tools like SSRS, and custom .NET applications.
Prerequisites: Basic familiarity with data warehousing, interest in learning more about SSAS
Goals:

  • Identify the conditions in which SSAS is preferable to using just the SQL Engine
  • Explain the differences between SSAS multidimensional and tabular
  • Demonstrate various ways of browsing and querying an SSAS model
Comments
  • Title a bit misleading. Was expecting a SSAS bashing, but pleasantly surprised with the meat of the abstract. Learning goals in line with level and abstract.
  • concise engaging abstract
  • Demo percentage seems low for such a topic but, overall, looks like a good session.
  • Good abstract. Sounds like a good session.
  • The idea presented in the introduction would be a good session, but it seems like the abstract strays from that path and into a general discussion of how to use SSAS. The abstract is well-constructed otherwise, and it effectively conveys the goals stated. The title seems to present the opposite viewpoint of the introduction to the abstract.
  • Well outlined abstract but if it had more demo, it would sound more interesting and persuasive.
  • Interesting and descriptive abstract. Seems to match level and topic.

My thoughts: This was some of the most helpful feedback I received. I don’t remember exactly what percent I said was demo, but the session could probably be redesigned to include more demos. And I have given this session before and received feedback that they felt the title was misleading. I will consider renaming the session in the future.