Building Your ETL Framework with BIML Meagan Longoria SQL Saturday #396 – Dallas BI Slides will be on my blog and on the SQL Saturday site Feel free to share questions and comments throughout the session Begin at the Beginning Meagan Longoria Business Intelligence Consultant at BlueGranite Organizer of SQL Saturday Kansas City (Oct 3, 2015) Who Are You? Blog: http://datasavvy.wordpress.com/ Twitter: @mmarie LinkedIn: www.linkedin.com/in/meaganlongoria/ 8 years in BI 7 years working with SSIS Business Intelligence Markup Language Domain specific language for describing business intelligence objects What on Earth is BIML? XML that you can write to: Build packages faster Ensure consistency Free – comes with BIDS Helper Also available in Mist Allows you to extend BIML with C# or VB.NET “Like ASP for BIML” - Bill Fellows You can use BIMLScript to: BIMLScript Import database table schemas (quickly create/update your dev environment to look like prod) Replace static values with expressions Include text from another BIML file or text file Turn tedious, repetitive work into reusable scripts BIML is still useful without BIMLScript, but much more powerful with it 2008 - BIML was born 2008 - Varigence was founded by Scott Currie A Little Background 2009 - BIMLScript was created 2011 - BIML compiler added to BIDS Helper BIML describes SSIS packages, databases, schemas, tables, columns SSAS cubes, facts, dimensions (Mist only) How It Works BIMLCompiler BIML/BIMLScript (Mist/BIDS Helper) DTSX packages All generated artifacts appear to be hand built Packages can be deployed and run on unmodified SQL Server (no need to install anything on the server running the packages) Free with BIDS Helper BIML is Awesome Benefits: Reduce amount of time it takes to develop an SSIS project Help you recover from Drag-and-drop-itis Ensure a consistent design pattern Stop solving the same problems over and over and move on to something new and interesting https://bidshelper.codeplex.com/ Choose the correct install for your version of SSDT/SQL Server BIDS Helper Current version is 1.7 Now properly supports SSIS 2014/Visual Studio 2013 Several BIML Updates, some breaking changes IDE for authoring BIML code Build via GUI or type code Mist Includes text editors with syntax highlighting, intellisense and quick-info displays, source control, and multi-monitor support Import existing databases and SSIS packages Free 14-day trial: reverse engineer 5 packages Perpetual or subscription licenses available BIML Basics <Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Packages> <Package Name="MyBlankPackage"> </Package> </Packages> </Biml> Demo Created a blank package Created connections Created a staging package My Learning Process Reverse engineer packages using Mist Create BIML library for common patterns (DB table staging, flat file import, fact , Type 1 SCD, Type 2 SCD) Use the BIMLScript include 3 schemas: Audit, Stage, Prod (whatever the project/subject area is called, ex: HR) Audit schema contains two tables: My SSIS Framework PackageControl: Package execution details for easy querying and restartability PackageDependency: lists packages in groups and orders them for execution Staging tables include all columns from source table for thin tables, select columns for wide tables, + audit fields Views used to transform data and do lookups for dims and facts Master packages for staging, dims, facts use package dependency for order and concurrency and package control for restartability Just BIML, no script Team members with little SSIS experience Trying to ensure a consistent design pattern Needed to finish the project quickly SQL Server 2012, project deployment model My First BIML Project 12 dims, 4 facts, 18 staging tables, 4 connections Used template BIML to generate packages Saved pattern in BIML using AdventureWorks Sent instructions to junior members on which items to change (connection, source query, destination table, etc.) Had them generate the package without saving the BIML changes BIML files are included with each new SSIS project for source control My BIML Library You decide whether you source control BIML, SSIS packages, or both. The right answer depends on your work processes. My recommendation: Although you could describe tables in BIML, it is best to continue use of database projects to contain your table schemas, views, stored procedures Boost It With BIMLScript Include CallBIMLScript Get ready to bang your head a few times. It’s fine. You won’t break anything. Lessons Learned Learn to abstract package definitions and use properties/parameters in BIML script to fully automate your package development. BIML makes SSIS more accessible for .NET devs who occaisonally to dabble in SSIS. Varigence BIML forums: https://www.varigence.com/Forums?forumName=Biml BIMLScript.com http://bimlscript.com/Develop/Resources Stairway to BIML: http://www.sqlservercentral.com/articles/BIML/100552/ Resources for Further Learning BIDS Helper documentation: https://bidshelper.codeplex.com/documentation http://geekswithblogs.net/darrengosbell/archive/2015/04/24/bid s-helper-1.7.0-released.aspx https://varigence.com/Documentation/Samples/Biml/ http://billfellows.blogspot.com/ Feel free to contact me with questions or feedback. Final Questions and Comments Meagan Longoria Blog: datasavvy.wordpress.com Twitter: @mmarie LinkedIn: www.linkedin.com/in/meaganlongoria/ Company Website: http://www.blue-granite.com/
© Copyright 2024