IN2BI, Microsoft Business Intelligence
data, stories & insights
rss | email | twitter

Category: 'Microsoft Business Intelligence'

  • di
    jun 11

    SQLBits - Query across the Mersey

    I am very pleased my session "Creating a Meta Data Driven SSIS Solution with Biml" has been selected for SQLBits this year. And I liked to thank the voters and the organization to make this possible.

    SQLBits will be the largest SQL Server Conference in Europe this year with a lot of great technical sessions lined up on Friday September 30 and Saturday October 1. So if you didn't already do so register now and meet me in Liverpool.

    My talk on ETL Automation using Biml will expand on my earlier blog series on Creating a Meta Driven SSIS Solution with Biml.



  • ma
    jun 11

    Creating a Meta Data Driven SSIS Solution with Biml - 2. Setting the stage

    This is the second blogpost on the creation of a Meta Data Driven SSIS Solution using Biml. In this post we are settings the stage. We are going to create a simple Kimball based data warehouse with 3 dimensions:

    • dimCustomer
    • dimEmployee
    • dimProduct

    and one fact table: factOrder.

    Furthermore we'll implement a staging area and a meta database.

    1. Create the databases:

    USE [master]
    CREATE DATABASE [MyDwh_meta]

    2. Create the staging tables:

    USE [MyDwh_st]
    CREATE TABLE [dbo].[Northwind_Suppliers](
    [SupplierID] [int] NOT NULL,
    [CompanyName] [nvarchar](40) NOT NULL,
    [InsertAuditID] [int] NULL,
    [UpdateAuditID] [int] NULL,
    [RowStartDate] [datetime] NULL,
    [RowEndDate] [datetime] NULL);
    CREATE TABLE [dbo].[Northwind_Products](
    [ProductID] [int] NOT NULL,
    [ProductName] [nvarchar](40) NOT NULL,
    [SupplierID] [int] NULL,
    [CategoryID] [int] NULL,
    [InsertAuditID] [int] NULL,
    [UpdateAuditID] [int] NULL,
    [RowStartDate] [datetime] NULL,
    [RowEndDate] [datetime] NULL);
    CREATE TABLE [dbo].[Northwind_Orders](
    [OrderID] [int] NOT NULL,
    [CustomerID] [nchar](5) NULL,
    [EmployeeID] [int] NULL,
    [OrderDate] [datetime] NULL,
    [InsertAuditID] [int] NULL,
    [UpdateAuditID] [int] NULL,
    [RowStartDate] [datetime] NULL,
    [RowEndDate] [datetime] NULL);
    CREATE TABLE [dbo].[Northwind_OrderDetails](
    [OrderID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [Quantity] [smallint] NOT NULL,
    [Discount] [real] NOT NULL,
    [InsertAuditID] [int] NULL,
    [UpdateAuditID] [int] NULL,
    [RowStartDate] [datetime] NULL,
    [RowEndDate] [datetime] NULL);
    CREATE TABLE [dbo].[Northwind_Employees](
    [EmployeeID] [int] NOT NULL,
    [LastName] [nvarchar](20) NOT NULL,
    [FirstName] [nvarchar](10) NOT NULL,
    [Title] [nvarchar](30) NULL,
    [ReportsTo] [int] NULL,
    [InsertAuditID] [int] NULL,
    [UpdateAuditID] [int] NULL,
    [RowStartDate] [datetime] NULL,
    [RowEndDate] [datetime] NULL);
    CREATE TABLE [dbo].[Northwind_Customers](
    [CustomerID] [nchar](5) NOT NULL,
    [CompanyName] [nvarchar](40) NOT NULL,
    [City] [nvarchar](15) NULL,
    [Country] [nvarchar](15) NULL,
    [InsertAuditID] [int] NULL,
    [UpdateAuditID] [int] NULL,
    [RowStartDate] [datetime] NULL,
    [RowEndDate] [datetime] NULL);
    CREATE TABLE [dbo].[Northwind_Categories](
    [CategoryID] [int] NOT NULL,
    [CategoryName] [nvarchar](15) NOT NULL,
    [InsertAuditID] [int] NULL,
    [UpdateAuditID] [int] NULL,
    [RowStartDate] [datetime] NULL,
    [RowEndDate] [datetime] NULL);

    3. Create the dimension and fact tables

    USE [MyDwh]

    CREATE TABLE dimCustomer (
    CustomerKey INT IDENTITY(1,1) NOT NULL
    CustomerID NCHAR(5) NOT NULL
    CustomerName NVARCHAR(40)
    City NVARCHAR(15)
    Country NVARCHAR(15)
    InsertAuditID INT
    ,UpdateAuditID INT
    ,RowStartDate DATETIME
    ,RowEndDate DATETIME);
    CREATE TABLE dimEmployee (
    EmployeeKey INT IDENTITY(1,1) NOT NULL
    EmployeeID INT NOT NULL
    EmployeeName NVARCHAR(50)
    Title NVARCHAR(30)
    Manager NVARCHAR(50)
    InsertAuditID INT
    ,UpdateAuditID INT
    ,RowStartDate DATETIME
    ,RowEndDate DATETIME);
    CREATE TABLE dimProduct (
    ProductKey INT IDENTITY(1,1) NOT NULL
    ProductID INT NOT NULL
    ProductName NVARCHAR(40)
    Category NVARCHAR(15)
    Supplier NVARCHAR(40)
    InsertAuditID INT
    ,UpdateAuditID INT
    ,RowStartDate DATETIME
    ,RowEndDate DATETIME);
    CREATE TABLE factOrder (
    OrderDateKey INT NOT NULL
    CustomerKey INT NOT NULL
    EmployeeKey INT NOT NULL
    ProductKey INT NOT NULL
    OrderQuantity INT
    ,OrderAmount money
    ,DiscountAmount money
    ,InsertAuditID INT
    ,UpdateAuditID INT
    ,RowStartDate DATETIME
    ,RowEndDate DATETIME);

    4. Create the views for the dimensions and fact tables

    USE [MyDwh]

    CREATE VIEW [dbo].[vwDimCustomer] AS
    ,[CompanyName] AS CustomerName
    FROM  MyDwh_st.dbo.Northwind_Customers;

    CREATE VIEW vwDimProduct AS
    , pr.ProductName
    , ca.CategoryName AS Category
    , su.CompanyName AS Supplier
    FROM MyDwh_st.dbo.Northwind_Categories AS ca
    INNER JOIN MyDwh_st.dbo.Northwind_Products AS pr ON ca.CategoryID = pr.CategoryID
    INNER JOIN MyDwh_st.dbo.Northwind_Suppliers AS su ON pr.SupplierID = su.SupplierID;

    CREATE VIEW vwDimEmployee AS
    ,em.[FirstName] + ' ' + em.[LastName] AS EmployeeName
    ,ma.[FirstName] + ' ' + ma.[LastName] AS Manager
    FROM MyDwh_st.dbo.Northwind_Employees em
    LEFT OUTER JOIN  MyDwh_st.dbo.Northwind_Employees ma ON ma.[EmployeeID]=em.[ReportsTo];

    CREATE VIEW vwFactOrders AS
    , OrderAmount=od.Quantity * od.UnitPrice
    , DiscountAmount=CONVERT(money, od.Quantity * od.UnitPrice * od.Discount)
    FROM  MyDwh_st.dbo.[Northwind_OrderDetails] od
    INNER JOIN MyDwh_st.dbo.Northwind_Orders o ON o.OrderID = od.OrderID
    LEFT OUTER JOIN dbo.dimCustomer cu ON cu.CustomerID=o.CustomerID AND cu.RowEndDate>o.OrderDate
    LEFT OUTER JOIN dbo.dimEmployee em ON em.EmployeeID=o.EmployeeID AND em.RowEndDate>o.OrderDate
    LEFT OUTER JOIN dbo.dimProduct pr ON pr.ProductID=od.ProductID AND pr.RowEndDate>o.OrderDate;

    5. Create the objects for the meta database

    USE [MyDwh_meta]

    CREATE TABLE [dbo].[SsisPackages](
    [PackageID] [int] IDENTITY(1,1) NOT NULL,
    [PackageType] [int] NULL,
    [PackageName] [nvarchar](50) NULL,
    [PackageGroup] [nvarchar](50) NULL,
    [SourceInput] [nvarchar](MAX) NULL,
    [SourceConnection] [nvarchar](50) NULL,
    [DestinationName] [nvarchar](50) NULL,
    [DestinationConnection] [nvarchar](50) NULL,
    [ErrorFile] [nvarchar](MAX) NULL,
    [TruncateDestination] [bit] NULL,
    [PackageID] ASC));
    CREATE TABLE [dbo].[SsisConfiguration](
    [ConfigurationFilter] [nvarchar](255) NOT NULL,
    [ConfiguredValue] [nvarchar](1000) NULL,
    [PackagePath] [nvarchar](255) NOT NULL,
    [ConfiguredValueType] [nvarchar](20) NOT NULL);
    CREATE TABLE [dbo].[SsisAudit](
    [AuditID] [int] IDENTITY(1,1) NOT NULL,
    [PackageID] [int] NOT NULL,
    [PackageGUID] [uniqueidentifier] NULL,
    [PackageVersionGUID] [uniqueidentifier] NULL,
    [ExecutionID] [uniqueidentifier] NULL,
    [PackageStartDate] [datetime] NULL,
    [PackageEnddate] [datetime] NULL,
    [ExtractRowCount] [int] NULL,
    [InsertRowCount] [int] NULL,
    [UpdateRowCount] [int] NULL,
    [ErrorRowCount] [int] NULL,
    [TableInitialRowCount] [int] NULL,
    [TableFinalRowCount] [int] NULL,
    [IsProcessed] [bit] NULL,
    [AuditID] ASC));

    CREATE PROCEDURE [dbo].[uspUpdateAuditRow]
    @AuditID INT
    ,@ExtractRowCount INT
    ,@InsertRowCount INT
    ,@UpdateRowCount INT
    ,@ErrorRowCount INT
    ,@TableFinalRowCount INT
    SET [PackageEnddate]=SYSDATETIME()
    WHERE [AuditID]=@AuditID;

    CREATE PROCEDURE [dbo].[uspNewAuditRow]
    @PackageID INT
    ,@PackageStartDate DATETIME
    ,@TableInitialRowCount INT

    6. Create an environment variable to store the connection

    I'll use package configurations to make it easier to deploy the solution to different servers (production, test, development) These package configuration will hold the information for the several connections. The connection to the meta database is stored in an environment variable. The other package configurations will be stored in the SsisConfigurations table in the meta database.

    So create an environment variable with the name Northwind_Config and value Data Source=.;Initial Catalog=MyDwh_meta;Provider=SQLNCLI10.1;Integrated Security=SSPI;

    Windows Explorer - right click the (my)computer node - select Properties - under tab Advanced click the buttton Environment Variables - under System variables click New.

  • vr
    apr 11

    IntelliSense in SQL Server Management Studio 2008


    Microsoft introduced its implementation of autocomplete (IntelliSense) in SQL Server Management Studio (SSMS) with version 2008. Great of course but it has some irritating quirks. Whenever you create new objects IntelliSense doesn't recognize them and keeps giving you those red curly lines. Yesterday I finally figured it out:

    Choose menu Edit - IntelliSense - Refresh Local Cache or short-cut: Ctrl+Shift+R

    SSMS 2008 Intellisense

  • di
    mrt 11

    Choosing the Right Microsoft BI Tool

    Microsoft recently published a whitepaper on Choosing the Right Microsoft BI Tool It contains 39 pages with Microsoft's insights on the subject. This picture sums it up nicely:

    microsoft bi tool

    It also tells the story of why PowerPivot hasn't been so widely adopted: Microsoft sells BI to IT and PowerPivot means less influence of IT departments.

  • di
    nov 10

    De Tijd Dimensie

    De tijd dimensie met voor elke dag een rij is de belangrijkste tabel in menig data warehouse. Veelal gelden speciale attributen zoals feestdagen, werkdagen maar ook alternatieve hierarchieen. Hierdoor is het volgens data warehouse guru Ralph Kimbal het verstandigst om deze dimensie in een middagje in Excel te bouwen.
    (Tip 51: Latest Thinking On Time Dimension Tables)

     Voor eenvoudige tijd dimensies en om snel een begin te hebben kan je onderstaande sql querie gebruiken.

    De querie maakt gebruikt van common table expressions een feature dat vanaf versie 2005 in sql server beschikbaar is.
    Daarnaast wordt gebruik gemaakt van de mogelijkheid om het isoweeknummer te bepalen dat vanaf versie 2008 beschikbaar is.

    IF  EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[dimTime]') AND TYPE IN (N'U'))
    DROP TABLE [dbo].[dimTime]

    -- set first day = 1 monday, default (us standard) = 7 sunday

    --Use recursion in this common table expression
    WITH cteDates AS
    SELECT CAST ('2005-01-01' AS DATETIME) Date --Start Date
    SELECT Date + 1
    FROM cteDates
    WHERE Date + 1 < = '2015-12-31' --End date

    [DateKey] = YEAR(date)*10000+MONTH(date)*100+DAY(date)
    ,[Year]= YEAR (date)
    [Quarter] = DATEPART ( qq, date)
    [YearQuarter]= CAST(YEAR(date) AS VARCHAR(4)) + '-Q' + CAST(DATEPART ( qq, date) AS VARCHAR(1)) --"2010-Q1"-
    ,[Month]= MONTH (date)  
    [MonthName] =DATENAME (mm, date)  
    [ShortMonthName]=LEFT ( DATENAME (mm, date), 3)  
    --,[Week]=DATEPART (wk, Date)
    ,[Week]= DATEPART (ISOWK, Date) --use user function isoweek for sql 2005
    ,[DayInYear]=DATEPART (dy, date)  
    [DayInMonth]=DAY (date)  
    [DayName]=DATENAME (dw, date)  
    [ShortDayName]=LEFT (DATENAME (dw, date), 3)
    [YYYYMMDD] =CONVERT(VARCHAR(8),date,112)
    [YYYY/MM/DD] =CONVERT(VARCHAR(10),date,111)  
    LastDayInMonth=DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,date)+1,0))

    INTO dimTime  
    FROM cteDates

  • ma
    jun 09

    Een 2e leven voor Performance Point planning?

    Microsoft heeft vorige week bekend gemaakt dat ze de broncode van Performance Point planning (gratis - maar met voorwaarden) beschikbaar stellen onder een nieuwe naam: Financial Planning Accelerator.

    MSDN BI Blog:
    Microsoft will make available the source code and project files derived from the PerformancePoint Server 2007 Planning module. This packaged source code will be called the Financial Planning Accelerator (FPA). The source code will be made available on a no-cost, individual license agreement and can be used to support or change PerformancePoint Server Planning functionality.
    Pasted from <>

    Dit is goed nieuwe voor klanten en partners die al geïnvesteerd hebben in Performance Point Planning en in januari volledig verrast werden door Microsoft aankondiging te stoppen met de planningsmodule van Performance Point.

    Hopelijk en zeer waarschijnlijk zal deze Financial Planning Accelerator een goede basis vormen voor nieuw te ontwikkelen planningsystemen.



  • di
    mei 09

    Solid State Storage en SQL PASS Europe Conferentie

    <<aangevuld met opmerkingen en plaatjes van Henk op 7 mei>>

    Hardware heeft niet echt mijn interesse maar de solid state technologie als vervanging voor hard disks is veelbelovend: snellere prestaties, stiller en minder energieverbruik.

    Helaas zijn ze nog erg duur. De 2 TB storage van dsi die Henk van der Valk (Unisys Nederland) gebruikte in zijn presentatie op SQL PASS Europe conferentie in Dusseldorf vorige week zal toch snel 100.000 dollar kosten. Deze presentatie van Henk samen met Thomas Kejser (SQLCAT) vormde één van de hoogtepunten van de conferentie: zeer snelle performance door gebruik van de DATAllegro techniek in de nieuwe Kilimanjaro release op een 96 core machine van Unisys: de ES7000  en de solid state storage.

    Dit alles leidde in eerste instantie tot een throughput van ongeveer 4 miljoen rijen per seconde van een tamelijk complexe dataset. Vergelijkbaar dus met het eerdere wereldrecord van vorig jaar. En na een aantal aanpassingen met het effect dat alle 96 cores gedurende korte tijd continue op 100% cpu draaiden werd het nieuwe record gezet op 14,5 miljoen rijen per seconde. (Ruim  1600  MB/sec  schrijven  en  1900MB/sec lezen van SSD met 96 parallele bulk Insert opdrachten )

    Heb je de presentatie gemist dan is er op 13 mei nog een herkansing in een door PASS Nederland georganiseerde avond sessie. Op vindt je hierover meer details.

    Veel nieuws was er verder niet op de PASS conferentie:

    • Er was uiteraard weer aandacht voor Gemini maar door het op het laatste moment afhaken van Donald Farmer als key-note spreker viel dit een beetje tegen.
    • Master Data Management (Stratature acquisitie) zal ook onderdeel worden van Kilimanjaro release (2010) Met TechEd in mei zal hierover meer informatie verspreid worden.
    • De ‘self learning’ data quality techniek van de Zoomix acquisitie zal eerst in de volgende release van SQL Server geïmplementeerd worden (2011)


    Gerelateerde links:

  • vr
    mrt 09

    To Merge or not To Merge

    In het algemeen is het mijn ervaring dat het samenvoegen van 2 afdelingen of bedrijven slecht werkt.  De in het vooruitzicht behaalde synergievoordelen blijken toch niet te realiseren, mensen raken gedemotiveerd en uiteindelijk blijft er een slechter functionerende afdeling/bedrijf over.
    Voorzichtig dus hiermee.

    Dat geldt ook voor het nieuwe SQL 2008 statement: MERGE.
    Deze biedt de mogelijkheid om een INSERT, UPDATE en DELETE in een statement uit te voeren en lijkt daarmee een ideale kandidaat in het ETL proces voor Slowly Changing Dimensions. De Kimball group heeft hierover design tip  #107 geschreven. Deze geeft een goed beeld hoe je MERGE kan gebruiken bij Slowly Changing Dimensions.

    Toch lijkt het me verstandiger om juist bij Slowly Changing Dimensions gebruik te maken van de SCD task  in SSIS of deze snellere variant. Dit in verband met exception, error en log handling. De nieuwe MERGE statement is wel uitstekend geschikt voor het vullen van datamarts met (geaggregeerde) informatie. Bijvoorbeeld het vullen van tabel met omzetcijfers per klant voor het gehele jaar waarvoor ik hieronder een simpel voorbeeld heb geschreven.

    Dagelijks wordt de tabel JaarVerkopen aangepast met de dagomzetten van gisteren uit de tabel DagVerkopen. Indien het een nieuwe klant betreft dan (NOT MATCHED) volgt een INSERT bij een bestaande klant (MATCHED) wordt het omzetbedrag verhoogd met de omzetten van gisteren.

    MERGE JaarVerkopen AS JV
     USING (
                 Omzet = SUM(Omzet),
           FROM DagVerkopen
           WHERE Datum>GETDATE()-1
           GROUP BY KlantID
     ) AS DV
     ON JV.KlantID =DV.KlantID
           INSERT (KlantID, Omzet)
           VALUES( KlantID, Omzet)
            SET JV.Omzet = JV.Omzet + DV.OMZET;

  • vr
    jan 09

    Microsoft BI Strategy Update


    Microsoft heeft vandaag aangekondigd dat ze:
    - Stoppen met Performance Point Planning
    - Performance Point Monitoring en Analytics een onderdeel wordt van Sharepoint Portal Server.

    Dit lijkt een logische stap gebaseerd op feedback van klanten zoals hier beschreven.
    De vandaag aangekondigde 5000 ontslagen spelen ongetwijfeld ook een rol.

    Voor de meeste klanten van Microsoft is dit een voordeel. Veel hebben al geïnvesteerd in de Sharepoint technologie en krijgen nu de dashboard, scorecarding en analytische tools van PPS er gratis bij.
    Voor een beperkt aantal klanten met investeringen in PPS Planning is het uiteraard een minder gelukkig besluit.

    PPS Planning was een eenvoudig concept met een relatief complexe architectuur en veel (versie 1) schoonheidsfoutjes. Persoonlijk ben ik er dan ook niet rauwig om.

  • do
    okt 08

    Gemini – Spreadmart heaven or hell

    Gemini add-in voor Excel Het grote nieuws van de Microsoft Business Intelligence Conferentie van vorige week was ongetwijfeld de aankondiging van project Gemini. Diverse bloggers waaronder Johan van der Kooij en ikzelf hebben hierover al het een en ander geschreven. De key-note met demo is nu beschikbaar op Ga naar 1 uur en 16 minuten voor het verhaal van Gemini en de presentatie van Donald Farmer

    Een mooi stukje techniek en visie die het business Intelligence landschap danig zal veranderen:

    • Nieuwe Analysis Services OLAP Mode gebaseerd op In Memory en Kolom georiënteerd technieken;
    • Krachtige data cleaning mogelijkheden binnen de Excel Add-In;
    • Hele grote datasets (100 miljoen rijen) bewerken en analyseren op de desktop;
    • Verbeterde draaitabellen met intelligente actieve slicers;
    • Publicatie naar Sharepoint zorgt automatisch voor centrale opslag van OLAP data, waardoor beheer door IT mogelijk wordt.

    Maar moeten we hier echt blij mee zijn? Jarenlang hebben we tenslotte allemaal gepleit voor het 1 versie van the truth principe en dan bedenkt Microsoft zich: Power back to the People. Geef power users deze krachtige tool om hun analyse- en rapportage problemen op te lossen. Door publicatie naar Sharepoint en met behulp van een aantal management tools kunnen ze hun rapporten en analyses delen en kan IT controle houden.

    Microsoft BI guru “Chriss Web is tamelijk negatief: de strategie om Gemini in Excel op de desktop te plaatsen is een uitnodiging om geen gebruik te maken van het data warehouse en creëert een spreadmart hell wat Microsoft ook zegt over het op Sharepoint gebaseerde management van Gemini modellen” Nigel Pendse van OLAP Report is een stuk positiever: “ Microsoft’s Brilliant OLAP Trojan Horse

    Persoonlijk zie ik de bezwaren van Chris ook wel maar ik zie als BI consultant toch vooral veel mogelijkheden omdat iedereen tenslotte rijke, gevalideerde data nodig heeft voor kwalitatieve rapportages en - analyses. Voor de ontsluiting van deze informatie zullen ze bij ons aan blijven kloppen. Wel zullen we dan vaker “later” ingeschakeld worden in dat proces. Verder is het natuurlijk ideaal voor initiële modellering en het ontwikkelen van proof of concepts.

    Kortom voor sommigen "Heaven" voor anderen "Hell" maar uiteindelijk niet tegen te houden. We hebben nog even om ons hierop voor te bereiden. De planning is 1eHJ 2009 CTP en 1eHJ 2010 release.