IN2BI, Microsoft Business Intelligence
data, stories & insights
rss | email | twitter
$category.Body

DWH Deck 2.0

In  my presentation at the DWHAutomation conference in Amsterdam on 20.09.2012 I gave a demo of the new version of the DWH Deck: DWH Deck 2.0. Here are the slides of that presentation:

This new release is now available for interested parties.
(If you are interested send me an e-mail: marco@in2bi.nl)

The DWH Deck is a simple tool that allows you to build and maintain your data warehouse better and faster. The basic idea is best described by looking at the input and the output:

INPUT:

  • Meta data that describes the the source object
  • Templates that describe target table and load pattern
    (I have provided 9, but you can change these and add additional templates)

OUTPUT:

  • SQL Statements to create target tables and additional objects,
  • SQL Statements to create stored procedures to load the data
  • BIML Files to create SSIS Packages that will handle the data load
     

In these blog posts you’ll find additional information:

  • ma
    18
    apr 11

    DWH DECK Part 5: Staging

    Adapted for version 1.1 on 18/04/2011

    This is the fifth part of a series I'm writing on the DWH DECK. A tool that allows you to build and maintain your data warehouse better and faster. This series will act as documentation and will give you some insides into the way the program  works.

    Overview of this series

    Pros and cons
    There are several pros and cons for and against using a staging area to populate your data warehouse. Of course it's an extra step that could be abandoned but I decided for it based on the possibility to schedule your import of several sources on convenient times and to have a nice overview of the relevant data from different sources.
     
    In this part I'll describe the impact on the choices you made in the main window on the import into the staging area.

    Skipping tables and columns
    When you check the Skip Table or Skip Column checkbox that table or column is not going to be imported into your data warehouse.
     
    Delta column
    Unlike the data vault and data mart area where we will only import the new or changed rows (by checking source against target) the staging area doesn't have this mechanism. And all data from the source is loaded each time. For some very large tables this can seriously affect the performance. For these tables look for a ‘Delta column’ like LastModifiedDate. The ETL to import handles the tracking of the maximum value in this column an will only import 'new' data. Of course you have to be sure that if data changes in this table the Delta column is also updated!

    DataType, DWH Data type and DWHFormule
    The DWH Deck does some minor transformations based on the input in these textboxes.
    Consider the example of CategoryName:
     
     
    The column will be converted into the DWHDataType and the formulae will be applied in the view that is used to import the source data:
     
    CREATE VIEW [sales].[vwImport_Northwind_dbo_Categories]
    AS
    SELECT
      [CategoryID] = CONVERT( int, ISNULL([CategoryID],0))
    , [CategoryName] = CONVERT( varchar(64), LTRIM(RTRIM([CategoryName])))
    , [Description] = CONVERT( varchar(max), [Description])
    FROM [YOUR_SERVER].[Northwind].[dbo].[Categories] WITH (NOLOCK)
     
    The default values in DWHDataType, DWHFormulae and SkipColumn are based on the source columns data type and the values in the bridge table: dbo.DWHDataTypes like:

    • Unicode data type mapping to corresponding smaller data types (nvarchar to varchar)
    • Removing leading and trailing spaces (LTRIM(RTRIM(?)))
    • Replacing NULL Values for certain data types (ISNULL(?,0))
    • Converting dates to integer with format YYYMMDD

    If you don't like these default values change the entries in the table dbo.DWHDataTypes before you import the source information.

  • ma
    18
    apr 11

    DWH DECK Part 4: The Model Window

    Adapted for version 1.1 on 18/04/2011

    This is the fourth part of a series on the DWH DECK. A tool that will allow you to build and maintain your data warehouse better and faster. This series will act as documentation and will give you some insides into the way the program works.

    Overview of this series

    Generate
    When you are satisfied with the choices you made it's time to generate the model. Menu: Model.
    A new window will open: DWH Deck - Model. Choose Generate in the menu and the model will be generated.
     

    You can now expand the treeview and select one of the tables under Staging, Data Vault or Data Mart.

    For each table you can examine the columns in the treeview and the SQL statements:

    • to create the table (under the tab Table)
    • to create the view that is used to import the data (Under the tab View)
    • to create the stored procedure that is used to import the data (under the tab ETL)

    Publish
    When you are satisfied with the created model you can use menu option:Publish.
    The sql statements to create the tables, views and stored procedures will be executed.

    Run
    With the tables, views and stored procedures published in your data warehouse you can now run the ETL stored procedures. Menu option: Run.

    If you select one of the tables under Staging, Data Vault or Data Mart you can now see a short history of the imported data of the selected table (under the tab: Run History).
     

  • ma
    18
    apr 11

    DWH DECK Part 2: Creating your data warehouse

    Adapted for version 1.1 on 18/04/2011

    This is the second part of a series on the DWH DECK. A tool that will allow you to build and maintain your data warehouse better and faster. This series will act as documentation and will give you some insides into the way the program works.
    Overview of this series

    Before you can create your data warehouse you'll have to establish a connection to the server for your data warehouse: Menu Application - Connect.
    In the dialog Connect to Server enter the server name and choice the authentication mode you'll use. If you use SQL Server Authentication enter the user name and password.

    It's important that the authentication account that you will use has sufficient rights on the server and read rights to the source server(s) / database(s).

    Now let's create the data warehouse: Menu Application - New Data Warehouse

    Enter the name of your data warehouse and click create .. You can see the progress of the creation of databases and tables in the progress textbox.
     


    As you can see four databases are created:

    • YourDataWareHouse, which holds the meta data for your solution.
    • YourDataWareHouse_st, the staging database
    • YourDataWarehouse_dv, the data vault database
    • YouDataWareHouse_dm, the data mart database 
  • ma
    18
    apr 11

    DWH DECK Part 3: Importing Source Information

    Adapted for version 1.1 on 18/04/2011

    This is the third part of a series on the DWH DECK. A tool that will allow you to build and maintain your data warehouse better and faster. This series will act as documentation and will give you some insides into the way the program works.

    Overview of this series

    Using Business Areas
    In the DWH DECK we use business areas to group related information. (e.g. Finance, HRM, Sales)
    In the model you can filter by business area and in the databases they are implemented as SCHEMAS.
     
    Menu Application - New Business Area. Enter the name of the Business Area and click Add.

    Using Linked Servers
    After you have created the business areas you'll need you can start importing the source information.
    If the source database is on another sql server you have to use a linked server.
    (In management studio: under Server Objects, Right Click Linked Server and Select New Linked Server..
    or use sp_addlinkedserver http://msdn.microsoft.com/en-us/library/ms190479.aspx)
     
    Importing Source Information
    Menu Application - New Data Source

    New Source for DWH Deck

    Type the server name of the source server, select the source database and select the business area that's most relevant for this data source. Next select the tables from the source database that you need and click Create.

    The meta data of each table and each column in the table is now imported and a simple profile of each table and each column is done. This can take some time depending on the size of your source system. Progress is shown in the textbox. When finished: click Cancel.

    New Source Ready for DWH Deck
     
    Based on the metadata and profile information several choices are made. Expand the Source Database in the Source Tables Treeview and select a table. View the metadata and profile on table level:

    • Last modified (when was the table last modified)
    • Rows (The number of rows)
    • The primary- and foreign relations to other tables.

     

    Source Table View In DWH Deck

    Select a column in the source column list and view the metadata and profile information on column level:

    • Number of distinct values and % of total rows
    • Number of null values and % of total rows
    • The minimum and maximum value
    • The maximum length of the values in the column
    • The Data Type

    Source Column View In DWH Deck

     
    We'll discuss the choices and their impact in Part 4 to 6 when we discuss the staging area, the data vault area and the data mart database.

  • di
    12
    apr 11

    Support

    DWH Deck is committed to helping you get up-to-speed with the DWH Deck quickly, to ensure high quality implementations and swift issue resolving.

    Proof Of Concept

    This is a short term project in which IN2BI creates a first iteration of your data warehouse. The exact amount of time needed to create this first iteration depends on scope, available source data and clear requirements. Advantages include:

    • Tangible results in a short timeframe
    • Predictable costs
    • Knowledge transfer

    Workshop DWH Deck

    To give you an head start with data warehouse automation using the DWH Deck contact us for a 2 days intensive workshop. This is a mini Proof of Concept with more attention to knowledge transfer to your Data Warehouse developers and scoped to a maximum of 2 days.

    Details:

    • Price: 2.500 EURO (excluding VAT and travel expenses)
    • Group-size: Maximum of 2
    • Language: Dutch or English
    • Location: At your company (in your country)

    DWH Deck

    This is a free tool we developed that can help you automate the development of your Data Warehouse.

  • ma
    11
    apr 11

    frequently asked questions (faq)

    Which database systems en versions does the the DWH Deck support?

    Micosoft SQL Server 2005, 2008, 2008R2

    Can you import text files?

    No you can't. You have to use another tool like SSIS to import them first.

    Quanto Costa?

    Single user version 360 Euro (VAT excluded) per quarter.
    Company version 900 Euro (VAT excluded) per quarter.

    Do you need the DWH Deck to run the ETL?

    No. You can use another tool to schedule and run the stored procedures to load the data in your data warehouse.

    Can I evaluate the product?

    Yes. Contact Marco Schreuder for a trial license key.

    You may use this key only for testing and presentation purposes.

  • vr
    08
    apr 11

    Introducing DWH Deck version 1.1

    A month later then planned. But I'm finally satisfied with this new release.

    It includes improvements regarding:

    • application logic
    • error handling
    • integration scenarios
    • selection of source data
    • delta determination

    You can download DWH Deck 1.1 if you already have a license key. If not contact me at marco@dwhdeck.com.
    Next week I'll be updating documentation. Use the documentation of version 1.0 for now.

  • ma
    07
    feb 11

    DWH Deck - Update

    The past week I have been gathering feedback regarding the first release of the DWH Deck.
    I like to thank all participants for testing the DWH Deck.

    This month I'll focus on:

    • Improving application logic
    • Improving error handling  and
    • Some additional features

    Expect next release early march.

  • di
    18
    jan 11

    Video Introduction of the DWH Deck

    A short introduction to the DWH Deck.

  • wo
    12
    jan 11

    DWH Deck - Tip 4 Changing satellites in one sql statement

    By default the DWH Deck places each attribute / column in the data vault area in a separate table.
    (As in the anchor modeling approach)

    For instance street in the table Customer_hub_street and city in the table Customer_hub_City. This can effect the performance due to the extra joins. You can change that as described in this blog post. (by entering another name in the satellite group text box).

    If you want all attributes in only one satellite table per hub / link you can change the entries in the dbo.SourceColumns table with the next sql statement:

    UPDATE dbo.SourceColumns SET SatGroup='sat'