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 🙂

Tutorial-by-Bas-van-den-Berg


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
AM-Model-with-mnemonics

Anchor Model without mnemonics
AM-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:
dbo_Calls

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):
SQL-Server-Tables

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)

SSIS-Package-Design

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:

SSIS-Raw-File-Connection

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

SSIS-Raw-File

6) Watch the show

SSIS-Package-Executed

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

AM-data-loaded

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!

Coyote

Advertisements

Social Analytics on Twitter and Xbox Live data (Halo 4 release)

POC: What are the Dutch gamers tweeting on Twitter during Halo 4’s launch?

I’ve created a proof-of-concept to analyze real-time Twitter data (Social Analytics), Xbox Live statuses and make use of Microsoft’s Business Intelligence (BI) Suite.

The inspiration
This POC was inspired by Microsoft’s ‘Analytics for Twitter’ PowerPivot in Excel 2010 sheet. Since this is in Excel, it is (sorry) useless in corporate environments and therefore this POC was born.

Custom software:
– Twitter Search API -> SQL#
– Google Maps API
– Xbox Live API
– Text sentiment analysis by BumbleBI (Tone Dictionary)

Refresh rate: every 5 minutes (up to 1 minute is possible)



Halo4








What is Social Analytics? (Social Business Intelligence)

Social Media Analytics: Monitor, Measure and Manage Your Reputation on the Wild Wild Web of Social Media.

Social Media Analytics is a must-have for campaigns and organizations eager to track, understand and measure word of mouth in the ether of blogs, social networks and micro-blogging applications.

(Wikipedia is terrible btw)

A couple of populair terms about Social (Media) Analytics:

How does it work?

What will be the result of this kind of analysis?

Want to read more?
Actionable Social Analytics

BumbleBI launched service provider: SQL Hotel – Shared and dedicated hosting of SQL Server databases and solutions

BumbleBI launched a new service provider called:
SQL Hotel – Shared and dedicated hosting of SQL Server databases and solutions.
 

 
Our servers are hosted in EasyNet’s datacenter at Schiphol-Rijk (Amsterdam, The Netherlands) which is directly connected to one of the world’s fastest internet hubs: Amsterdam Internet Exchange (AMS-IX).
 
Ideal for smaller databases without paying the expensive licensing costs.
Or for the large corporations for world-wide database access for free usage of Excel, PowerPivot, Power View and/or tablet apps.
 
Our Microsoft Business Intelligence (BI) consultants can support deploying your data to this cloud-environment (near realtime of by a daily import job).
 
The databases of these software suites can be made available in our SQL Hotel environment for analysis, reporting, data mining and more:
 

 
Interested? Check one of our websites:
SQLHotel.nl
SQLServerDatabaseHosting.com
 
Spread the word!


BumbleBI is going Pro!

This is a day of new beginnings…a small step for HP, a giant leap for BumbleBI!
Today, my very own HP rack server was delivered, an HP ProLiant DL360 G7 🙂

Installing Windows…

I’ll keep you posted on my adventures with Hyper-V, SBS2011 and ofcourse SQL Server 2012! 🙂