SSIS Anchor Modeling example (tutorial)

About
This Anchor Modeling SSIS example is brought to you by:

Clint Huijbers (BumbleBI IT)

Bas van den Berg (C2H)

The first version of the demo was created by Bas and shared on AnchorModeling.com.

Tutorial Video by Bas
Advice you to watch it first 🙂

New version of the SSIS package
The fast performing package (final step in his tutorial) was rebuild by Clint and is described below:

Download
To download the SSIS package and T-SQL scripts, click here.

The Anchor Model (AM)
The AM is about telephone calls, that includes two Anchors (Call and Phone), two Ties and three Attributes:

Anchor Model with mnemonics

Anchor Model without mnemonics

How to install

1) Create a new database called ‘AM_SSIS_Example‘

2) Create all AM-objects by executing the script: ‘Generated SQL Script by AM Online Modeler.sql‘

3) Generate source data by executing the script: ‘Create dbo_Calls table (source).sql‘
Give it a few minutes, it will generate 691.200 rows

The table dbo.Calls is already filled with demo data:

4) Create a Batch-table for metadata by executing the script: ‘Create BA_Batch table.sql‘

The database should now have a couple of tables (also Views, SPs and UDFs):

5) Open the SSIS package in BIDS/SSDT and execute the package

The SSIS package demos two scenarios:
#1 – Insert records by using the INSERT-trigger on view dbo.lPhone
#2 – Insert records directly into the Anchor and Attributes
(by using the fast load / bulk insert and parallel processing of the Attributes)

Raw File
A Raw File Source is used to temporary store and quickly re-load the full dataset, located in ‘C:\Temp\AM_SSIS_Example_RAW_FILE‘. Important: make sure to manually create/verify this Raw File. You’ll be able to find it here:

Open the ‘Raw File Destination Editor‘-window (right-click and ‘Edit’) and verify the Raw File’s location:

6) Watch the show

7) Verify the results by executing the script: ‘Verify the results.sql‘

8) Increase the volume!
Would you like to re-process all data and perhaps with a lot more records? 😀
Truncate all tables by executing the script: ‘Truncate and delete all data.sql’
Now, drop the table dbo.Calls.
Execute the script ‘Create dbo_Calls table (source).sql‘ again, but this time increase the variable @EndDateTime with just a couple of days.
Have fun!