Business Intelligence Chronicles

The impact of the host OS on virtual machine performance

2011-11-30T22:08:00.000Z

If you use virtual machines on a daily basis then I am sure you would have at some point wondered which host OS is the best to run your VM's on. So I decided to do a very quick basic benchmark of a Windows Server 2008 R2 virtual machine on both Windows 7 and Ubuntu 11.10 as hosts. For the benchmark I used PassMark PerformanceTest 7 (installed on the VM).

The machine that I ran this on was my Sony Vaio VPCF115FM latop. It has a core i7 1.60 processor with 8GB of RAM. The VM was on a Seagate 1TB external drive which runs at 5400rpm.

The file system on the external drive was NTFS and remained NTFS for both Windows and Ubuntu tests.

The Windows 7 with SP1 host OS installation only included Office 2010 and all anti virus and firewall software turned off and closed down. I also set the Removal Policy of the external drive to Better Performance instead of the Quick Removal default.

The Ubuntu host OS was stock standard Ubuntu 11.10 with the Nvidia drivers from the Ubuntu package library. It also had all firewall and anti virus software disabled and turned off.

After three executions on each machine I got a proper baseline and the results are displayed in the two images included.

Ubuntu 11.10

Windows 7 SP1

It seems that Ubuntu has the upper hand in CPU, Memory and HDD performance and Windows only beat it (although by quite a bit) on 3D performance. (Which I expected as the Nvidia GT330M graphics card seems to always be a sticking point for Linux distro's)

I was rather surprised by the difference in performance of the CPU and especially the HDD. Since we know that disk performance is always the biggest bottleneck for VM's then it seems that if you want to squeeze the maximum performance out of your VM's using Ubuntu as a host OS might just be a good choice.

Running Total using Window Functions in Denali

2011-10-07T23:07:00.000+01:00

At the SQLRelay event in London last night Itzik Ben-Gan did a great session on the Window Functions in Denali. This session got me thinking about the benchmarking that I did a while ago for running totals. In this benchmark the Quirky update performed the fastest by far, however using an update statement is sometimes just not feasible in certain scenarios.

The Window Functions in Denali introduces another way to calculate running total and I thought I would give this a try to see how it stacked up against the quirky update.

Here is the code to create the base table:

CREATE TABLE RunningBalance (Ident INT IDENTITY(1,1) PRIMARY KEY,
                             CustomerName VARCHAR(50),
                             Value INT,
                             RunningBalance INT)
DECLARE @Start INT = 1,
        @End INT = 1000000

WHILE @Start <= @End
BEGIN

INSERT INTO RunningBalance (CustomerName,
                              Value)
VALUES('Customer1',1);

SET @Start += 1

END

Since the tests were done on a VM I did a run of the quirky update on the VM and it returned completed in 11 seconds and if we enable the discarding of results after execution in SSMS then the result is 7 seconds.

So next up was the SELECT statement that used the Window Function to calculate the running total.

SELECT Ident,
        CustomerName,
        SUM(Value) OVER (PARTITION BY CustomerName
                         ORDER BY Ident ASC
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningBalance
FROM RunningBalance
ORDER BY Ident ASC

This statement returned the results in 14 seconds. Which is fantastic as this includes the time to return the results to the grid control in SSMS.

If we enable the discarding of results after execution to eliminate the amount of time it takes to populate the grid control in SSMS then the SELECT with the Window Function returns in 6 seconds. This is truly great performance for this type of query!

The Window Functions in Denali will definitely be adding some fantastic options for solving some of the rather thorny challenges in previous versions of SQL.

Setting up a virtual BI environment

2011-10-02T23:04:00.000+01:00

For anyone wanting to setup a test BI environment and looking for a step by step guide have a look at the following TechNet articles http://technet.microsoft.com/en-us/library/hh223286.aspx The step by step guide has both written instructions and webcasts so you can take your pick of the format you like most.

Remember if you are using virtual machines and you copy them from a base line virtual machine you will need to reset the SID of the copied virtual machines using SYSPREP, ensure you have the Generalize checkbox ticked to generate a new SID.

PerformancePoint Analytic Chart showing unformatted value of SSAS measure

2011-07-07T23:02:00.000+01:00

I have recently come across some very peculiar behaviour of the PPS Analytic Chart where it for some reason showed values with a scale of 7 instead of the formatted value which has a scale of 2. To make this even more bizarre when you change the chart type to grid and look at the same data it shows all the values as zero (zero is the expected value in this case) with a scale of 2 (0.00).

In the screenshots below the value should display a flat line of 0. As you can see the chart does not display this but shows some very small value with a scale of 7.

To make things even stranger when you hover the mouse over the chart the tool tip that displays the value for the particular area on the chart displays the measure value of 0.00 and not the value shown on the y axis as can be seen below.

If the same analytic chart is changed by right clicking on it and choosing the Grid report type then the values are shown correctly as being 0.00

So this made me think that for some reason the Analytic Chart is not plotting the formatted values received from the SSAS server but that the Analytic grid does. To ensure that this is the case I ran the MDX query that returned the results with CELL PROPERTIES VALUE to return the unformatted value from the cube. The result confirmed my suspicion as can be seen in the screenshot below.

So PPS is not plotting the formatted value but the actual unformatted value of the measure. When you look at the MDX generated by the Analytic Chart you see that it brings back both the unformatted and formatted Cell Properties and from what I can tell it plots the chart using the unformatted value but uses the formatted value for the tool tip. If you change the MDX generated you lose the interactivity of the Analytic Chart so that is not the solution in this case.

We have only one option and that is to use Currency as the data type in SSAS for the measure, which ensures we will not have the scale issues as it is an exact numeric data type, it does have some great performance benefits too which can be seen in this blog post from the SQL CAT team.

After making this change the PPS Analytic Chart is behaving the way we want it to as can be seen in the screenshot below.

So in conclusion it does look like PerformancePoint has a bug on the Analytic Chart component which in certain cases let it plot the unformatted value of a measure. The one way around this is to change the data type to an exact numeric such as Currency. In this example we could make that change but if we truly needed to use the Double data type for a measure in SSAS then we would have had some serious problems trying to get PPS to display the correct values in the Analytic Chart.

Create a Last 30 Weekdays Named Set in SSAS

2011-04-27T22:49:00.000+01:00

One of the most common Sets to be create is the “Last N Days” type of set which is normally just the last 30 or last 60 days from the current day. Another common request is for a Last N Working Days or Weekdays. Lets have a look at how we can create a Named Set that will always provide the last 30 Weekdays using the Adventure Works cube solution.

Lets have a look at the MDX statement that will do the work for us.

    Tail
    (
      Extract
      (
        Exists
        (
          {NULL
            :
              Exists
              (
                [Date].[Date].[Date].MEMBERS
               ,[Date].[Is Current Day].[True]
              ).Item(0)
          }
         ,[Date].[Is Weekday].[True]
        )
       ,[Date].[Date]
      )
     ,30
    )

Lets break the MDX down into the main parts and explaining each step in the above statement.

Tail
(
Extract
(
    Exists
    (
      {NULL
        :
          Exists
          (
            [Date].[Date].[Date].MEMBERS
           ,[Date].[Is Current Day].[True]
          ).Item(0)
      }
     ,[Date].[Is Weekday].[True]
    )
   ,[Date].[Date]
)
,30
)

The highlighted piece of MDX above will get the current day member from the Date attribute hierarchy in the Date dimension. The method used here is described in detail in a previous blog post here. This can be replaced with any MDX that will return the Current Day member.

Tail
(
Extract
(
    Exists
    (
      {NULL
        :
          Exists
          (
            [Date].[Date].[Date].MEMBERS
           ,[Date].[Is Current Day].[True]
          ).Item(0)
      }
     ,[Date].[Is Weekday].[True]
    )
   ,[Date].[Date]
)
,30
)

The above highlighted part of the code will create a set containing all the members of the Date attribute hierarchy in the Date dimension before and up to the current day (including the current day).

Tail
(
Extract
(
   Exists
    (
      {NULL
        :
          Exists
          (
            [Date].[Date].[Date].MEMBERS
           ,[Date].[Is Current Day].[True]
          ).Item(0)
      }
     ,[Date].[Is Weekday].[True]
    )
   ,[Date].[Date]
)
,30
)

Next we use the Exists function highlighted above to return only the members that are weekdays. This uses an attribute, “Is Weekday”, on the Date dimension that has a True or False as value similar to the “Is Current Day” attribute.

Tail
(
Extract
(
    Exists
    (
      {NULL
        :
          Exists
          (
            [Date].[Date].[Date].MEMBERS
           ,[Date].[Is Current Day].[True]
          ).Item(0)
      }
     ,[Date].[Is Weekday].[True]
    )
   ,[Date].[Date]
)
,30
)

Our second last part of the MDX expression is highlighted above. The Extract function is used to only return only the tuples from the Date attribute hierarchy from the current set.

Tail
(
Extract
(
    Exists
    (
      {NULL
        :
          Exists
          (
            [Date].[Date].[Date].MEMBERS
           ,[Date].[Is Current Day].[True]
          ).Item(0)
      }
     ,[Date].[Is Weekday].[True]
    )
   ,[Date].[Date]
)
,30
)

The last part of the MDX is to use the Tail function to get the last 30 tuples and thus create a set that contains the last 30 weekdays.

Since the Adventure Works demo DW does not have data for 2011 in it I forced the 31st of August 2004 to be my current day in the cube. The result of using the above MDX in a query is shown below.

All that is left is to add the MDX to a Named Set in the Adventure Works cube in BIDS.

This method of creating an attribute that indicates if a specific date is a weekday can be modified and used in many different ways to create sets containing specific tuples.

Getting the Current Date in SSAS

2011-04-26T22:45:00.000+01:00

So there is a few ways that you can go about getting the Current Day in SSAS. It is very often a requirement to have the ability to get the Current Day in an MDX select statement or in a Calculated Member in the cube. Below are a few ways of achieving thins.

Method 1 – The GETDATE() option

Matt Winslade did a very nice post on this on his blog http://devmau5.com/blog/?p=47

StrToMember("[Date].[Date].[" + Format(now(), "yyyyMMdd") + "]")

The biggest issue with this method is if your Date attribute hierarchy uses a Key or a Name property that does not conform to one of the standard date formats, sequential numbers for surrogate keys like the Adventure Works demo comes to mind. This would make it quite difficult to construct the correct string to convert to a member.

Method 2 – The IsCurrentDay attribute

Part 1 using the DSV

One of my favourite ways is to include an IsCurrentDay attribute on the Date dimension. This attribute will only be “True” for the specific date on the dimension that matches the current date when processing the dimension. In its simplest form (and not best practice) this can be implemented in the DSV of the cube with a new Named Calculation. The DSV should only be used if you do not have access to the underlying tables and views that the SSAS solution is built from.

The above screenshots is using the Adventure Works demo cube. You can modify the following code snippet by changing the FullDateAlternateKey to the column in your date dimension that contains the date.

CASE WHEN CONVERT(DATE,FullDateAlternateKey) = CONVERT(DATE,GETDATE()) THEN CONVERT(BIT,1) ELSE CONVERT(BIT,0) END

Once you have the IsCurrentDay attribute on the Date dimension you will be able to get the current date member by using the following MDX.

Exists([Date].[Date].[Date].MEMBERS,[Date].[Is Current Day].&[True]).Item(0)

Part 2 using a view for the source of the Date dimension

Best practice is to use a views as the source for your cube’s dimensions and measure groups. The exact same method described in Part 1 above could be used to create the IsCurrentDay. This would be the best option if you want the IsCurrentDay attribute to be updated automatically each time the cube is processed.

Part 3 using the ETL to update the Date table in the database

Option 3 is to add a IsCurrentDay column to the date table that the Date dimension is built from. This column is then updated each time the ETL runs. It give more flexibility over which day gets flagged as the Current Day since the ETL can be made much more complex by using ETL execution tables or anything for that matter to update a specific record in the Date Dimension as the Current Day.

Conclusion

The above methods allow us to get the Current day in a cube. Method 1 will always be the most dynamic as it will update based on the system time of the server the SSAS cube is on. This can be either a desired result or a hindrance when a cube is not processed or a backup restored since the Current Day will then be incorrect.

Method 2 is a more controlled method that give the same ability to identify the Current Day but with the ability to have control over which day is flagged as being “Current”. The 3 ways of implementing this is entirely up to the developer’s choice but the recommendation would be to use a View or the ETL to achieve this. If this second method is used you will need to at least do a Process Update on the Date dimension for the changes in current day to take effect.

Determine if a day is a weekday using TSQL

2011-04-26T22:43:00.000+01:00

I have seen a few places where people have created elaborate functions to determine if a day is a week day or not. The easiest way that I have come across and which I personally use is the following statement.

CASE WHEN DATENAME(DW, GETDATE()) NOT IN ('Saturday', 'Sunday') THEN 1 ELSE 0 END

The “GETDATE()” part of the code should be changed to the column name which contains your date. If you would rather create a function that uses the above statement then replace the “GETDATE()” with the variable that you use to pass the date into your function.

The nice thing about this is that you can also alter the section that contains you weekend days, by replacing the day names in the brackets at the end with the the days that are not week days.

Keep in mind that if your database language changes to anything other than English then you will need to update the Day Names that you have supplied to be in the correct language. If you are looking for a more universal way which is independent of the Language settings of the database then have a look at the following blog post by Ryan Farley http://ryanfarley.com/blog/archive/2005/02/14/1685.aspx

Benchmark results for the fastest way to calculate Running Totals in SQL Server

2011-04-10T22:41:00.000+01:00

I have recently had a challenge to calculate running balances over a very large set of data. This had to be done in TSQL and I found a great article on SQL Server Central by Jeff Moden http://www.sqlservercentral.com/articles/T-SQL/68467/

The article covers the different methods extensively so I won’t be repeating anything with regards to the methods. I will however post some of the results I achieved using three of the methods.

In the examples I will be using a table with an Identity Column which serves as the Primary Key and thus full fill the requirement of the Quirky update to have a Clustered index in the order of the update. The Identity column will allow the verification of the Running Balance to be much easier as well. We will use a loop to populate the table with a million records.

CREATE TABLE RunningBalance (Ident INT IDENTITY(1,1) PRIMARY KEY,
                             CustomerName VARCHAR(50),
                             Value INT,
                             RunningBalance INT)

DECLARE @Start INT = 1,
        @End INT = 1000000


WHILE @Start <= @End
BEGIN

INSERT INTO RunningBalance (CustomerName,
                              Value)
VALUES('Customer1',1);

SET @Start += 1

END

The Value field will contain a value of 1 and if the running balance is calculated correctly then the value of the RunningBalance column will equal the Ident column value.

The first method is the Triangular join method and was done using the following code:

-- Triangular Join Method
UPDATE RunningBalance
SET Value = Value + (SELECT ISNULL(SUM(Value),0) FROM RunningBalance WHERE Ident < r.Ident)
FROM RunningBalance r

I stopped the query after 28 minutes as it was clear that this was not a viable method.

Don't forget to reset the RunningBalance table’s RunningBalance column to NULL before testing the different methods.

UPDATE RunningBalance
SET RunningBalance = NULL

The next method was to loop through the records using a While Loop and calculate the running balance.

-- While Loop
DECLARE @Start INT = 1,
        @End INT = 1000000,
        @PrevValue INT

WHILE @Start <= @End
BEGIN

    SET @PrevValue = ISNULL((SELECT RunningBalance FROM RunningBalance WHERE Ident = @Start - 1),0)

    UPDATE RunningBalance
        SET RunningBalance = Value + @PrevValue
    FROM RunningBalance r
     WHERE Ident = @Start


    SET @Start += 1

END

This method was much faster and completed the calculation in 7 minutes and 39 seconds.

The last method was the Quirky update and this was performed using the following code.

-- Quirky Update
DECLARE @RunningBalance INT

UPDATE RunningBalance
SET @RunningBalance = RunningBalance = r.Value + ISNULL(@RunningBalance,0)
FROM RunningBalance r WITH (TABLOCKX)
OPTION (MAXDOP 1)

The Quirky update performed the calculation in 4 seconds. This is by far the fastest method of all and is definitely worth having a look at when the circumstances allows for its use.

The article by Jeff Moden mentioned at the start of this post covers all the details about the method and is a definite must read.

Using a Translation Mart for translations in Data Warehouses, Reports and Cubes

2011-03-08T21:39:00.000Z

In most BI or DW solutions that span different countries and regions there is always a requirement to accommodate multiple languages. There are multiple solutions to this issue especially considering that SSAS has great Multi Language support. However there are normally some challenges with labels in Reporting Services not to mention maintaining one central repository for the translations that can be used across multiple systems.

I have put together a prototype of a database that can be used to achieve the above requirements while storing all translations centrally.

Installation

Create a database on your SQL Server Instance called TranslationMart
Run the scripts that form part of the solution in the following order (the scripts can be downloaded here):
- Create Tables.sql
- Create UDF TransPivotResult.sql
- Create Function udfTranslate.sql
- Create uspReturnTranslationPivot.sql
- Create uspReturnTranslationUnPivot.sql
Use the “Execute Proc Example.sql” script to test the working of the solution

Overview

The translation solution allows for the easy translating of key business terms that are used in a BI solution. The database structure is a star schema and is shown in the below database diagram.

DimLabel will contain the term or word that will be translated. The LabelName field will be in the chosen base language.

DimLanguage will contain all the Microsoft Language codes with their descriptions.

DimGroup will contain any user created groups. These groups will be used to retrieve sets of translations, for example when you need to translate the labels and report headings of a particular SSRS report. The stored procs that form part of the system will allow you to specify either a group or a particular label that you want to translate. (You do not need to create groups in order to use the solution, they are there to help make retrieving groups of translation easier)

LnkGoupLabel will contain the mapping between the groups and the labels that form part of a particular group. This allows you to list a label in multiple groups without having to store a label per group in the fact table.

FctTranslation will contain the translated Label value stored against each of the specific languages.

Using the solution

The TranslationMart contains two stored procedures and one Scalar function. The two stored procedures will mainly be used when retrieving translations in batches for use, such as the creation of datasets in SSRS.

Both stored procedures accept three parameters:

@LanguageCode: This parameter is the language in which you want to translate the Label into.

@LabelName: This is a label that exists in the DimLabel table which you want to translate; this can be left blank in which case a group name must be provided.

@GroupName: This is a valid group name that exists in the DimGroup table. If this value is specified the stored procedure will return translated values in the language identified by the @LanguageCode parameter for each label that is linked to the group.

uspReturnTranslationPivot

This stored procedure will create a pivoted result set that has the LabelName values as the column names and one row in the result set with the translated value corresponding to each LabelName.

Due to the fact that you cannot pass a dataset easily to a SSRS function, you cannot retrieve the values in a normal two column result set without having to use a data region, such as a list, to then filter out all values except the one you are interested in.

This stored procedure helps you get around that because SSRS allows you to specify a specific column from a dataset that you would be able to specify for a specific Report Header or textbox. An example of the expression that will be used in a particular textbox or report header is as follows:

=First(Fields!SubHeading.Value, "dsetTestGroup")

uspReturnTranslationUnPivot

This stored procedure is included for use in scenarios where you would like to retrieve translation in a two column result set consisting of LabelName and the translation Value. It works exactly as the above procedure and only differs in that it does not pivot the result set into columns for each LabelName.

The solution also contains a scalar function which can be used to retrieve translations in SELECT statements.

udfTranslate

The function accepts two parameters namely @LangaugeCode and @LabelName. It will then return the translated value.

Examples Results

The scripts loads data into the tables and translate them into Afrikaans for demo purposes. Below are two queries that show how the data is stored in the database.

-- Data stored in the fact table
SELECT *
FROM dbo.FctTranslation FT
INNER JOIN DimLabel DL ON FT.LabelID = DL.LabelID
INNER JOIN DimLanguage L ON FT.LanguageID = L.LanguageID

-- Data used to group labels
SELECT *
FROM dbo.DimGroup DG
INNER JOIN LnkGoupLabel GL ON DG.GroupID = GL.GroupID
INNER JOIN DimLabel DL ON GL.LabelID = DL.LabelID

The results are:

Lets look at the data that is returned from the uspReturnTranslationPivot stored procedure.

-- PIVOT
-- Return English Values
EXEC [dbo].[uspReturnTranslationPivot] 'en-GB','','TestGroup'

EXEC [dbo].[uspReturnTranslationPivot] 'en-GB','SubHeading',''

-- Return Afrikaans Values
EXEC [dbo].[uspReturnTranslationPivot] 'af','','TestGroup'

EXEC [dbo].[uspReturnTranslationPivot] 'af','SubHeading',''

The results are:

The uspReturnTranslationUnPivot return the following results.

-- UNPIVOT
-- Return English Values
EXEC [dbo].[uspReturnTranslationUnPivot] 'en-GB','','TestGroup'

EXEC [dbo].[uspReturnTranslationUnPivot] 'en-GB','SubHeading',''

-- Return Afrikaans Values
EXEC [dbo].[uspReturnTranslationUnPivot] 'af','','TestGroup'

EXEC [dbo].[uspReturnTranslationUnPivot] 'af','SubHeading',''

In query translations could be achieved by using the function.

SELECT L.LabelName,
dbo.udfTranslate('af',LabelName) AS Translation
FROM DimLabel L

Maintaining the translations

It will be possible to maintain the translations in multiple ways. One would be to create a data spec for loading the translations from CSV files using SSIS. Another, which will be the most user friendly, will be to develop a custom front end application to allow users to make changes to the translations.

Conclusion

The solution will allow a central place to store all translations which could then be used to translate data in data warehouses or other databases. Performance with a properly partitioned fact table would be very fast.

Quick Installation Guide for Analysis Services Load Testing using ASLoadSim and ASQueryGenerator

2011-03-03T21:36:00.000Z

There are two projects available on the CodePlex website that allows load testing against Analysis Services. These two projects are ASLoadSim and ASQueryGenerator. The two projects are used in conjunction with each other in order to allow for load testing of a specific SSAS cube. They have been around since 2007 with the most recent update being in 2009.

I recently had to use these two projects to do Load Testing of a Cube and decided to put together a short step by step guide for installation and execution. The ASLoadSim project comes with the SSAS Load Testing Best Practices - Draft v1.3b.doc document which I will refer to for detailed information regarding certain installation and execution steps.

This is a quick guide to installing the two projects and configuring them to perform Load Testing. This guide assumes that you have the following software installed on your machine.

Microsoft Visual Studio 2010 Premium (Or a version that allows you to create Load Tests)

You will also require access to the Adventure Works DW Cube as well as a SQL Server instance in which you will create the LoadTest database to capture all the results of the load test.

Quick installation and execution guide

1. Create a folder on your client machine that you will extract the solutions to. (In this scenario we will call it SSAS Load Test)

2. Create a folder called Queries.

3. Extract the ASLoadSim.zip and ASQueryGenerator.zip files into the folder.

4. Check the folder properties of both folders to ensure that they are not set to Read Only.

5. Navigate to the ASQueryGenerator folder and double click on the ASQueryGenerator.sln file to open the solution. (Or open the solution from Visual Studio 2010)

6. Convert the project to using the Visual Studio Conversion Wizard.

7. In the Solution Configuration drop down select Release and then click on Start Debugging.

This will build the ASQueryGenerator executable in the C:\SSAS Load Test\ASQueryGen\bin\Release\ folder.

8. Open the Template file AdventureWorks_template.XML in the folder C:\SSAS Load Test\ASQueryGen\Query Templates\ and make the required changes. (download the AdventureWorks_Template.XML file here and add it to the Query Templates folder)

For more information regarding how to make changes to the template file, or for generating a new template file, refer to the document SSAS Load Testing Best Practices - Draft v1.3b.doc p10 (CollectingLoad Queries) and p30 (Generating MDX Client Files)

9. Open the command prompt.

10. In the command prompt navigate to the folder C:\SSAS Load Test\ASQueryGen\bin\Release\

11. Type the following in the command prompt to generate the Client MDX files. ASQueryGenerator.exe "C:\SSAS Load Test\ASQueryGen\Query Templates\Adventureworks_Template.xml"

12. Hit Enter. You should now see the output of the ASQueryGenerator.

13. Move the Adventureworks_query.xml files from the C:\SSAS Load Test\ASQueryGen\bin\Release\ folder to the C:\SSAS Load Test\Queries\ folder.

14. Open the loadtestresultsrepository.sql SQL Script located in the C:\SSAS Load Test\ASLoadSim\ folder in SQL Server Management Studio and run the script. (This will create the LoadTest database which is required to log the load test results to.)

15. Open the UpgradeLoadTestResultsRepository.sql SQL Script located in the folder C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\ (Remember that if you are running a 64bit Operating System the path will be C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE)

16. Run the script to upgrade the LoadTest database to the 2010 version.

17. Navigate to the ASLoadSim folder and double click on the ASLoadSim.sln file to open the solution. (Or open the solution from Visual Studio 2010)

18. Convert the project to using the Visual Studio Conversion Wizard.

19. Click on the Test menu in the menu bar in Visual Studio.

20. Click on Manage Test Controllers.

21. Click on the ellipse next to the connection string shown for Load test results store.

22. Configure the connection to point to the LoadTest database.

23. Click OK and then Close to return to the Visual Studio IDE.

24. Double click on the LoadPlugin.cs file in the Solution Explorer window.

25. Scroll down until you find the string private string m_configFileName change it to point to the configuration.xml file in the ASLoadSim project folder. Below is an example, remember to use two "\" characters in the path as you can see below.

Click on the save button to save the changes made.

26. Double click on the configuration.xml file to open it.

27. Edit the configuration file to correspond to the servers and folders of your deployment.

28.

For more details about the configuration file refer to the document SSAS Load Testing Best Practices - Draft v1.3b.doc p32 (Configure Load Simulator)

29. Double click the LoadTest1.loadtest This will open the load test in Visual Studio.

Ensure that you change the ConfigFileName property under the Context Parameters to point to the configuration file inside the ASLoadSim folder if it does not point to it already.

30. Click on the Run Test button in the top left.

31. The test will now run and you should see the following screen.

For more information on editing or creating new load tests refer to the document SSAS Load Testing Best Practices - Draft v1.3b.doc p33 (Configure Visual Studio for Load Test Run)

For information regarding querying the LoadTest database for Load Test results refer to the document SSAS Load Testing Best Practices - Draft v1.3b.doc p42 (Reporting Performance Test Results)

Account Balances and retrieving the last record in Fact Tables

2011-02-10T21:34:00.000Z

Everyone knows how LastNonEmpty behaves according to the official documentation, however recently I looked at a very unique situation in which I had to retrieve the last record in the fact table for a particular time period and I thought I would share this in case anyone found themselves facing the same set of user requirements. Keep in mind that under normal circumstances you would use an ETL and modelling solution to solve the issue but in this case the user requirements and technologies made available prevented it.

Lets say you are busy working with Account Balances. With some example data below:

In the above records we see that we have account balance movements for a Customer on a Date at a Time. This means we might have many account movements within a second. Under normal circumstances we would simply take the last movement in the ETL and load that into our fact table or we might try to get the data at a lower level of granularity. However in this example we have the requirement that we load all the records into the fact table because we want the end users to be able to use the “Show Details” option in PerformancePoint graphs and grids to bring back all the movements that happened in a particular time period. (Keep in mind that we do not have SSRS available to us in this example)

So with the stage set lets look at some default aggregation behaviour. If we build the cube with the Account, Date and Time dimensions and use LastNonEmpty as the aggregation for our Value measure we end up with the below results.

This is exactly the correct behaviour of LastNonEmpty, all the records in our fact table that fall into the lowest level of our time dimension, the second level, are aggregated together and this aggregate is returned. LastNonEmpty as per the documentation does not return the last record in the fact table but the last non empty value for the member on your time hierarchy that you have selected in your cube. (Keep in mind you need to assign the correct dimension types and attribute types to both the Date and Time dimension)

If we want to achieve the desired behaviour mentioned in the beginning we will need to achieve this by changing our model slightly. We will need to introduce and Order dimension.

The Order dimension will simply have a key attribute that runs from 1 to the maximum number of movements you will have within a second for a customer within a second.

In our fact table we will add an OrderID column which we will use to link the Order dimension to the fact table but which will also play an important role as a measure to perform a bit of trickery later on. So with this column added the records in the fact table will look as follows.

The next step is to create a new measure on the measure group with an aggregate function of Max over the OrderID column in the fact table. Lets call it MaxOrder. This measure will be hidden as it will only be used for our calculated measure that will depend on it.

The next step is to create our calculated member that will work out our account balance. (This looks a little messy and I will add a more elegant method as soon as possible)

In the calculation we build a tuple that will always have the Order attribute that is the last one for a specific time period for a customer. This is where the MaxOrder measure is used as it is converted to string and then is used to construct our tuple.

(STRTOMEMBER("[Order].[Order].[" + CSTR([Measures].[MaxOrder]) + "]"), [Measures].[Value])

The end result of this is a tuple that would look like this.

([Order].[Order].[4],[Measures].[Value])

After we have create this calculated member we can browse the cube and the result would looks as follows.

Look at the AccountBalance field and you will see that this is now returning the correct results as we would have expected. Also when a user uses PerformancePoint to look at the details of the data they will see all the individual transactions that happened as was the original requirement.

If we want to hide the Order dimension from the users we can simply add a perspective over the cube to achieve this.

It has to be said that there will be a performance cost to all of this and if its possible the best solution will always be to use the correct model with the data at the lowest possible grain or to use the ETL to only bring in the last record at the correct level of granularity. However if like in this example these options are not going to work given the user requirements then its still possible to achieve the desired result as has been shown above.

Another Method

Another method that works that is more elegant than creating the MaxOrder measure and dynamically building the tuple in a calculated measure is to use the following MDX.

SUM(TAIL(NonEmptyCrossJoin([Order].[Order].[Order].Members),1),[Measures].[Value])

In this case you don’t need to create the MaxOrder measure in the measure group and you only have the calculated member with the above MDX. You still need the Order dimension as in the first method above. From my testing this method works well and is by far a much neater method as it requires much less effort to achieve the result.

Very Useful PowerPivot Diagrams

2011-02-08T21:32:00.000Z

There are two very useful PowerPivot diagrams that illustrates both the client/server architecture as well as the security architecture. These diagrams are available with some more details from the below links.

PowerPivot Client/Server Architecture

http://sqlcat.com/whitepapers/archive/2010/04/23/powerpivot-technical-diagram-powerpivot-client-server-architecture.aspx

PowerPivot Security Architecture

http://technet.microsoft.com/en-us/library/ff955761.aspx

SSAS with Type 2 Slowly Changing Dimensions and Duplicate Members in Attribute Hierarchies

2011-01-19T21:30:00.000Z

Slowly changing dimensions are an integral part of any BI project and while most people are familiar with handling this in the data warehouse, exposing these dimensions to end users through an Analysis Services cube can sometimes be confusing for the end users if not done properly.

Having had such a case recently I have decided to do a quick post explaining some pitfalls as well as ways to deal with Type 2 SCDs in SSAS. There was a lot of questions and answers on the MSDN forums as well as other blog posts but few addressed the issue of having duplicate member names in an attribute hierarchy in an easy and straight forward way.

Lets look at an example of a very easy Type 2 slowly changing dimension table.

The above table shows a very simple Product dimension with each product rolling up into a Category and the ProductID field being the surrogate key. Records 1 to 3 were the original records in the table and record 4 is a new record inserted when the “Trek Riding Gloves” moved from the “Accessories” category to the “Gloves” category. All of this is normal SCD Type 2 changes handled in the ETL.

When we build the dimension in SSAS from the table you will create two attribute hierarchies namely Product and Category.

The Product attribute will be the Key Attribute and you could use the ProductID surrogate key as the KeyColumns property and the Product Name column for the NameColumn property.

If you do not use the Surrogate Key for the KeyColumns property you will need to create a ProductID attribute as the Key Attribute and a Product attribute with a composite key which includes the ProductName field and the CategoryName field for the KeyColumns property and the ProductName field for the NameColumn property. If you do not create this you will end up with the following error:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_dimProduct', Column: 'ProductName', Value: 'Trek Riding Gloves'. The attribute is 'Product'.

The reason for this error is that when you have the Attribute Relationships defined in the way the natural hierarchy rolls up in the table (Product rolling up into Category) you will have “Trek Riding Gloves” rolling up into two categories. The attribute relationship we defined expects a Many – to – One rollup from the child level to the parent level.

For this example we will use the Surrogate Key as the KeyColumns property of the Product attribute and not create the composite example mentioned above. It is important to note that both methods will produce the same effect we will look at next.

We create a User Defined Hierarchy that rolls from Category to Product to help with easier navigation.

After processing the dimension we can browse it and have a look at how it will look to the user. The effect is correct for the Products hierarchy which we have defined drilling down as would be expected with “Trek Riding Gloves” appearing under both the “Gloves” and “Accessories” categories.

The problem comes in when we browse the Product attribute hierarchy where you will see a duplicate entry for “Trek Riding Gloves”.

This behaviour is exactly correct because the key for the Product attribute has changed and SSAS uses the KeyColumn of an attribute to identify each member. This is why even if you created a composite key it would still have a duplicate as the combination of ProductName and GroupName would result in a new value. The fact data is being captured against the new record in our dimension and when we browse our cube by Date and the Products User Defined Hierarchy we will see the data split correctly.

The challenge comes in when a user wants to view the sales at the Product level alone. This will show two lines for “Trek Riding Gloves”.

This is confusing to a user and will surely not be welcomed. So we need to ensure that users have the ability to see the sales captured by product without having the duplicates displayed.

The best way to achieve this is to add a new Attribute to the Product dimension which we will call “Product Name”. This attribute will use the ProductName column as its KeyColumns and its NameColumn properties and will have an Attribute Relationship with the Product attribute rolling into the “Product Name” attribute.

We will in turn hide the Product attribute by setting the AttributeHierarchyVisable property to False. Note that we do not replace the Product attribute in the Products User Defined Hierarchy with our new “Product Name” attribute as this would cause us to have an Unnatural Hierarchy which has a detrimental impact on cube performance etc. (More info about this is available in the SQL Server 2008 White Paper – SSAS Performance Guide available for download here http://www.microsoft.com/downloads/en/details.aspx?FamilyID=3be0488d-e7aa-4078-a050-ae39912d2e43&DisplayLang=en )

When the user now browses the cube he would have the ability to use the Products hierarchy to view the data drilling down from a Category level to the Product. The user will also then have the ability to look at the data from a product only point of view and this will be achieved by using our new “Product Name” attribute hierarchy and will show only one line per product as can be seen below.

Changing SQL Server Browser Account Using Configuration Manager Fails

2010-12-08T21:29:00.000Z

I have recently run into a rather niggling issue when I tried to change the account under which my SQL Server Browser Service was running.

The issue came to light after realising that I have specified the LocalSystem account for my SSAS service and the NetworkService account for the SQL Server Browser service during a quick installation on a development virtual machine. (Have a look at http://msdn.microsoft.com/en-us/library/ms143504.aspx for the best practice recommendations regarding the SQL Server service accounts’ setup)

When trying to connect SSAS in Management studio I received the following error:

A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient)

No connection could be made because the target machine actively refused it 127.0.0.1:2382 (System)

This in turn led me to a blog post where it was suggested that the SQL Server Browser Service be run under the LocalSystem account instead of the NetworkService account.

When I tried to change this in the SQL Configuration Manager I encountered the following error:

WMI Provider Error: The group name could not be found. [0x800708ac]

The solution to this was to change the account under Windows Services instead which worked perfectly and resolved all the above issues.

SQL Server 2008 Microsoft Certified Master Readiness Videos

2010-12-03T21:27:00.000Z

For anyone looking to achieve the SQL Server 2008 Microsoft Certified Master credentials there are some great readiness videos available at http://technet.microsoft.com/en-us/sqlserver/ff977043.aspx

Even if you are not looking at achieving the MCM certification these videos contain some great content.

Moving my blog

2010-12-02T13:39:00.001Z

I have recently decided to wind down my blogging on here and have opted to move my blog to www.beeii.com

All the current content has been moved over and in future all new content will be added to the new site. Head over and have a look.

Creating an IsDecimal CLR function for use in TSQL

2010-12-01T14:50:00.001Z

This morning I saw a SQL Server Central article about the ISNUMERIC function in SQL Server and the fact that it does not always behave the way that people expect it to behave. This reminded me about the first time that I used it and was also surprised when my conversions in my scripts failed after using ISNUMERIC to test for what I believe would be values that would successfully convert to DECIMAL or NUMERIC data types.

As my experience was during some ETL work I promptly decided to use SSIS and a script task in which I could use the VB.NET Isnumeric() function that provided the desired result.

However what would you do if you need this functionality in your stored procs or TSQL scripts. One solution is to create the function that is shown at the end of the article, the other solution would be to use a CLR function written in C#.

The CLR function is really simple to write and the function itself consists mainly of the lines below.

public static SqlBoolean IsDecimal(SqlString Value)
    {
        decimal decValue;

        if (decimal.TryParse(Convert.ToString(Value), out decValue))
        {
            return new SqlBoolean(true);
        }
        else
        {
            return new SqlBoolean(false);
        }
    }

The function will use the TryParse method of the decimal type. After deploying the function I ran a test using the same SELECT statements used in the article to make sure that the CLR function returns the desired result.

Modified SQL statements:

The results were spot on with the behaviour I was looking to achieve:

Ascii Code	Ascii Character	ISNUMERIC Returns	IsDecimal Returns
9		1	0
10		1	0
11		1	0
12		1	0
13		1	0
36	$	1	0
43	+	1	0
44	,	1	0
45	-	1	0
46	.	1	0
48	0	1	1
49	1	1	1
50	2	1	1
51	3	1	1
52	4	1	1
53	5	1	1
54	6	1	1
55	7	1	1
56	8	1	1
57	9	1	1
92	\	1	0
128	€	1	0
160		1	0
162	¢	1	0
163	£	1	0
164	¤	1	0
165	¥	1	0

The function also worked great for the last two SELECT statements in the above modified code.

If you want to grab a copy of the CLR Project you can download it here.

Simon Munro on Professional Tools

2010-11-30T08:45:00.000Z

I thought I would share a blog post by Simon Munro which I thought was brilliant! http://simonmunro.com/2010/11/23/professional-tools/

Have a look at the end where he breaks down the cost of a developer using a sub par computer for development. Its always good to see the real cost of decisions and not the window dressing that is so many times held up as the truth.

Enjoy!

Getting the last occurrence of a character in SSIS using FINDSTRING

2010-11-22T13:04:00.000Z

When looping through a folder containing files to upload it is always much easier to have the For Each Loop container retrieve the filename "Fully Qualified". This allows you to simply specify one variable in the expression used to configure your Flat File Source connection string.

When you then want to include the name of the file being loaded in the data flow you will have to use a Derived Column Transformation to find the last occurrence of the "\" in the string contained in your filename variable. This can be done using a combination of the FINDSTRING and REVERSE functions, after which you then use the RIGHT function to only retrieve the name of the file with its extension.

If we look at the below example of a fully qualified filename you will see that there could be any number of "\" characters when your For Each Loop also traverse sub folder.

C:\Test\Upload\DummyFile.csv

As the FINDSTRING function does not allow built in functionality to allow you to specify that you want the last occurrence we use the REVERSE function to reverse the string and thus give us the first occurrence of the "\" character. After we have determined this we simply subtract the value from the total length of the string and this gives us the position of the last "\" in our fully qualified filename. We then simply use the RIGHT function to retrieve all characters from the last position -1 (remember we want to exclude the character itself from the returned value and only want the filename.). Always remember that you will need to convert your DT_STR (string) datatypes to DT_WSTR (unicode string) as the FINDSTRING expression only works with the latter.

If we use a variable called strFilename the expression will look like this:

RIGHT(@[User::strFilename],(LEN(@[User::strFilename]) - FINDSTRING(REVERSE((DT_WSTR,50)@[User::strFilename]),"\\",1) -1))

This will then bring the filename into the data flow.

Project Crescent links and teaser video

2010-11-19T11:25:00.000Z

In case you have missed it here are some links to resources about Project Crescent, the new data visualization tool.

http://blogs.msdn.com/b/sqlrsteamblog/archive/2010/11/09/a-glimpse-at-project-crescent.aspx

The teaser video can be found here http://blogs.msdn.com/b/bi/archive/2010/11/09/data-visualization-done-right-project-crescent.aspx

Project Crescent not part of Denali CTP1

2010-11-10T11:42:00.000Z

If you have had a look at the PASS keynote of yesterday ( 9th of November 2010) you will have seen a demo of Project Crescent. Unfortunately a CTP of Crescent will not be shipping with SQl Server Denali CTP1, according to the SQL Server Reporting Services Team Blog http://blogs.msdn.com/b/sqlrsteamblog/archive/2010/11/09/a-glimpse-at-project-crescent.aspx

Project Crescent's interactivity has reminded me of the data visualizations used by Hans Rosling in his presentations at TED. For those who have never seen one of his presentations and his truly amazing way of letting data tell its story head over to this TED presentation http://www.ted.com/talks/lang/eng/hans_rosling_at_state.html

By the looks of it Project Crescent will, through the interactivity and functionality, give a loud voice to the data in any organization.

SQL Server Denali on Technet Wiki

2010-11-10T09:55:00.001Z

Have a look at some of the content that has started to appear on the Technet Wiki for SQL Server Denali. At the moment these are mostly about the changes in SSIS, hopefully we will see more about the other features. I am personally looking forward to seeing some more details regarding Crescent.

http://social.technet.microsoft.com/wiki/contents/articles/tags/Denali/default.aspx

Books Online for SQL Server Denali has also been made available (in Pre Release format) http://msdn.microsoft.com/en-us/library/ms130214%28v=SQL.110%29.aspx

SQL Server Denali CTP

2010-11-09T22:52:00.000Z

The CTP of SQL Server Codename Denali (SQL 2011) has bee released and for those who would like to download it you can get it here.

For a look at some of the new features, which is honestly some of the most amazing functionality to have graced the SQL Server platform, have a look at the SQL PASS Keynote http://www.sqlpass.org/summit/na2010/LiveKeynotes/Tuesday.aspx

Some of the feature that I have to point (which you will see in the keynote) are:

Project Crescent - One of the most exciting web based reporting tools I have ever seen. Think of all the greatest features of all the web based reporting tools you have come across and add to it even more phenomenal interactivity and integration.
Column based storage in SQL Server - This truly delivers amazing performance and is now fully implemented, yes its not some sort of hybrid implementation, its proper fully fledged column based storage in SQL Server.

There is a mention of big change to SSIS but no demo in the above keynote. This should all be revealed in the next presentation at SQL PASS.

SSIS Formatting Shortcut Keys

2010-11-03T21:47:00.000Z

Have you ever wondered what the shortcut keys are for the Formatting options in a SSIS solution?

These options do not have any shortcut keys assigned to them by default, however you can assign your own. By clicking on "Tools" and then "Options" and finally selecting "Keyboard" you will be able to assign your own shortcut keys to the Formatting options available to you in a SSIS solution in BIDS.

High Speed Connectors for Oracle and Teradata in SSIS

2010-10-12T11:33:00.000+01:00

If you have never needed to extract data from Oracle or insert data into an Oracle database in your ETL then you might not know of the Attunity connectors that are available for free (When you use Enterprise Edition of SQL SErver 2008). Its been around for quite a while but definitely worth checking out.

If you experiece slow performance with the built in connectors for Oracle then have a look at the Attunity connectors. http://blogs.msdn.com/b/mattm/archive/2008/10/02/attunity-connectors-for-oracle-and-teradata-are-now-available.aspx

SSIS Error when connecting to OLAP using OLE DB

2010-10-11T13:42:00.001+01:00

When connecting to an OLAP SSAS database using an OLE DB Source in SSIS you might see the following error when running the package:

Error: 0xC004701A at Data Flow Task SSIS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0202009.

The best solution to his has been to add the

Format=Tabular

This is an issue that should be fixed in SP3 of SQL 2005 but I have recently encountered it again and think its worth knowing incase you ever require it.

Letting go of the TSQL legacy when using SSIS

2010-10-03T15:51:00.003+01:00

After reading Johan Åhlén blog post about why he believes the cloud will increase the demand for skilled developers it made me think of one of the things in SSIS that has been highlighted many a time but still occurs on occasion. I am talking about the excessive use of TSQL inside SSIS packages for data transformation and loading.

DTS packages made extensive use of TSQL to load data and it is this legacy that has been carried over incorrectly into SSIS development. It is thus no wonder that almost every SSIS training course that is presented starts with telling the delegates on training to forget about what they use to do in DTS as it will only detract from the quality of their work.

Lets have a look at some of the TSQL legacy habits that should be avoided at all cost when using SSIS.

Transforming data

SSIS is an ETL platform that is phenomenal at transforming data in memory thus making it quicker and more robust than performing these transformations in TSQL. The bad practice of using Convert and Cast in your OLE DB Source components in your data flow does not only reduce the speed of your ETL but it also introduces conversion errors that cannot be handled efficiently.

Here is a quick example, if we have to convert nvarchar values to numeric we could encounter a value that might be stored in exponential format for any number of reasons. If we perform our conversion in the TSQL for example:

This conversion will fail and the only way to get around it in TSQL would be to convert it to float first and then to numeric (we are purely talking about conversion and not looking at fixing this at the source which will always be the best solution when possible). This will thus be two conversion that need to take place.

If we chose to do this conversion using a Data Conversion transformation in our Data Flow then this conversion will succeed without any problems. On top of that if any conversion errors do occur SSIS gives you the ability to handle these error rows and thus will allow you to load them into error tables and continue the loading of your data without bringing the whole ETL routine to a grinding halt as will be the case if the TSQL example is used.

Inserting Data

The use of TSQL insert statements instead of proper Data Flow tasks is another big legacy problem. By using these statements you are eliminating the use of proper logging in your ETL. Using these insert statements requires the data to be staged somewhere before the loading of the records into your destination tables and thus is not taking advantage of the SSIS Data Flow engine to reduce staging by performing transformations in memory. If TSQL insert statements are used in SSIS it is normally preceded by TSQL statements that perform transformations on the data and relegates SSIS to be a work flow tool instead of the ETL platform it is. All of this is classic ELT (Extract Load Transform) which was used in DTS and some other ETL tools. It increases disk IO and reduces ETL performance.

Using TSQL statements in SSIS instead of stored procedures

When TSQL statements are used directly in SSIS Execute SQL Tasks it increases the maintenance of your SSIS packages considerably. This means that when a change to the TSQL code needs to be made you will have to edit your SSIS package, retest it and then redeploy the package.

In contrast when stored procedures are used you can be confident that when a change needs to be made this change can be made in the database and there will be no need to redeploy the package provided that, if the stored procedure returns a values to SSIS, it still returns the values in the same structure and order. Due to some intricacies in OLE DB Source components it is perfectly fine to use a simple select statement in them provided you do not fall into the habit of adding your data conversions to these select statements as explained in the first section above.

Conclusion

I do hear the argument from time to time where people say that “if it works why do I have to change or fix it”, I am sure the same people would agree with me that a Model T Ford would still be perfectly capable of driving down today’s roads but none of them would want to do their daily commute using one. The other argument is normally “We don't deal with large volumes of data so we won’t benefit from the performance increase”, that might be true but you will benefit from a more robust ETL solution and I am sure that with the extra time you will have available to you due to the reduced maintenance requirements of your ETL routines you will discover many other capabilities that SSIS provide on which you were missing out previously.

Microsoft SQL Server 2008 R2 Best Practice Analyzer

2010-09-11T12:27:00.001+01:00

The SQL Server 2008 R2 best practice analyzer tool has recently been released by Microsoft. To quote from the Microsoft Site:

“The Microsoft SQL Server 2008 R2 BPA is a diagnostic tool that performs the following functions:

Gathers information about a Server and a Microsoft SQL Server 2008 or 2008 R2 instance installed on that Server

Determines if the configurations are set according to the recommended best practices

Reports on all configurations, indicating settings that differ from recommendations

Indicates potential problems in the installed instance of SQL Server

Recommends solutions to potential problems “

The tool can be downloaded here.

Inserts in your ETL routines slow down for no reason

2010-09-10T20:14:00.001+01:00

If your ETL loading process has become extremely slow and you cannot figure out why a simple insert of 10 000 records is dragging on for a minute or even more (a simple insert from a source to a target with virtually no transformations in-between should never take that long) then maybe you need to look at your database auto growth settings.

The database initial size and the database auto growth should always be correctly set according to best practice http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx In SQL Server 2005 the default setting of auto growth when creating a new database using the GUI was 10%. In SQL Server 2008 this has been changed to 1 MB. If for some reason you do not follow the advice of the above link and set a correct auto growth size this can severely impact database performance.

The performance degradation can be as severe as a recent case where I was asked to investigate an extremely slow SSIS package. The inserting of 40 000 rows of a fairly wide table with large string columns was taking up to 10 minutes. After confirming that the SSIS package itself was not the problem I had a look at the auto growth of the database and discovered that this had left on the default of SQL Server 2008 which was 1 MB. After setting this to a larger value the same SSIS package loaded the records in 8 seconds.

So before you try to optimize your SSIS or ETL routines, make sure that you have followed best practice from the start, and that would be right from the very first step of creating your database.

Configure Reporting Services to use basic authentication with a SMTP server

2010-08-15T16:25:00.001+01:00

For anyone that has had the requirement to configure a Reporting Services instance to use a remote SMTP server using basic authentication it is sure a great frustration that basic authentication against a SMTP server is not support. There is however a way to get around this problem.

All of this can be achieved by using your own virtual SMTP server and setting up message relay with basic authentication to the remote SMTP server. Lets go through the steps to achieve this.

First you need to install the SMTP feature on your Windows Server instance. In this example I am using Windows Server 2008 R2. In Server Manager go to the features section and click on Add Features.

Select SMTP from the list of feature.

Click on “Add Required Role Services” when you are prompted. (This will added IIS and all the other services that are needed to run the SMTP server)

Click “Next” until you get the “Confirm Installation Selection” window and then click on “Install”.

Click on “Close” after the installation completes.

Next we need to setup our SMTP server to relay our emails via the remote SMTP server. Click on Start, Administrative Tools and then on Internet Information Services (IIS) 6.0 Manager. (Make sure to select the IIS 6.0 Manager)

Click on the + sign next to the server name to reveal the virtual SMTP server.

Right click on the SMTP Virtual Server and click on Properties. Under the general tab, select the IP address of for the SMTP server.

On the Access tab click on “Relay”. If you wish to limit the computers that are allowed to relay through this virtual SMTP server by specifying them explicitly then keep the “Only the list below” option selected and add the IP addresses, Domain or group of computers to the list by clicking on the “Add” button. If you want to allow all machines except certain ones to relay emails through the virtual SMTP server then you can select the “All except the list below” option and add machines you specifically want to deny access to into the list by click on the “add” button. (Keep in mind this will allow any machine to relay mail through this virtual SMTP server unless it is specifically blocked. As this is a testing Virtual Machine I have selected the second option)

Click “OK” and then click on the Delivery tab. On the Delivery tab click on “Outbound security”.

Select “Basic authentication” and specify the username and password that you need to use to authenticate to the remote SMTP server.

Click on “OK”. Next click on “Outbound connections”.

Here we will set the TCP port on which to connect to the SMTP server.

Click on “OK”. Next click on “Advanced” on the Delivery tab.

Here we will specify the address of the remote SMTP server in the “Smart host” textbox.

Click on “OK” twice to complete the setup of the SMTP server. The last thing that remains to be done is to configure Reporting Services to use our virtual SMTP server. You can do that by running the Reporting Services Configuration Manager utility.

Connect to you SSRS instance and then click on the “Email Settings” option.

Type in the email address that will be shown as the sender when SSRS sends emails. (This may be over written by the remote SMTP server so check to see if this is the case. In this example the Google SMTP server will replace any address that you enter here with the email address of the account used to authenticate to it and send emails with.)

In the “SMTP Server” textbox type the IP address that you selected for the virtual SMTP server.

Click on “Apply” and then “Exit” after the settings have been applied successfully.

Reporting Services is now configured to send emails via the local virtual SMTP server which will relay the emails using the Remote SMTP server using basic authentication.

Splitting very large delimited strings in SQL

2010-06-25T13:15:00.001+01:00

Previously I blogged about how to split multi valued report parameters for Reporting Service. In this post I gave the code for a very simple function that will split the values of a delimited string and return them as separate values in result set. This works extremely well for small delimited string values such as those you would normally encounter when working with Reporting Services parameters. However sometimes you might run into a situation where you need to split much larger sets of values (delimited stings with a length of say 200 000 characters or more). In these circumstances a function splitting the values might not perform as optimally as you would have wished. There is an alternative to this, so lets have a look at using a stored procedure and some SQL Server XML functionality to get the same result.

Speed comparison between different methods

Lets compare two different methods, namely the previous NonXML method (using loops) and the XML method. We will then test this using two different database objects, Functions and Stored Procedures. This should give us a very good indication of the performance in both objects, which would be used to solve different challenges.

So the first thing we would need to do is construct a very large delimited string value. This can be done using a simple WHILE loop:

Lets get a benchmark for the time it takes to construct a concatenated string value consisting of 50 000 values by running the piece of SQL.

The result in this case is 6 seconds to generate our list of values. The size of the list in, number of characters, is 288 893.

Next we look at the four SQL Objects that we will examine during our testing. First up is the two functions:

udf_SplitValuesNonXML

As you can see this is the exact same function I posted in the previous post. Next up is our function that makes use of XML.

udf_SplitValuesXML

The function transforms the delimited text into XML which is stored in an variable of the data type XML. Each delimited value becomes an element node (r) value in the XML that is created. These values are then retrieved using a select statement and inserted into the temporary table that the function returns.

In order to determine the execution time of each procedure we will deduct the time it takes to create the delimited string (6 seconds in our test) from the total time it takes to run the whole statment including the function. For example:

So lets see how these two functions perform when splitting out text value.

Function	Execution Time
udf_SplitValuesNonXML	17sec
udf_SplitValuesXML	Stopped after 5minutes

So clearly using the XML code is not the way forward when you want to split a large delimited text value. Lets see what happens when we use stored procedures.

usp_SplitValuesNonXML

This stored proc uses the same logic as the non XML function.

usp_SplitValuesXML

As you can see its the exact same XML logic as we had in the XML function.

Lets see what the performance is like this time round.

Procedure	Execution Time
usp_SplitValuesNonXML	35sec
usp_SplitValuesXML	2sec

Clearly the XML logic outperforms the non XML logic by an extremely large margin when it is used in a stored procedure.

Conclusion

When you need to split a large delimited text value and you are forced to use a function, then the non XML logic seems to be you best bet. However if you need the fastest performance possible and can use a stored procedure instead of a function then the XML logic shown here outperforms the non XML logic by a huge margin.

Wayne Eckerson makes a very interesting point

2010-04-09T18:18:00.001+01:00

Wayne Eckerson has written a very interesting article on the TDWI’s site. It is definitely worth a read and from personal experience I completely agree that industrialized BI teams achieve far less in the same amount of time than their cross skilled counter parts do.

Have a look at the article here http://tdwi.org/Articles/2010/04/08/experts-revolutionary-BI.aspx

Never use FLOAT as the data type for a measure in a fact table.

2010-04-02T21:56:00.003+01:00

As a best practise you should never use a Float, Real or Money data for a measure in a fact table. The reason for this is that if you are doing aggregations you are possibly going to receive incorrect result due to the above mentioned types being approximate data types.

Lets have a quick look at an example. On SQLServerCentral.com you can find a good example of this aggregation error. (http://www.sqlservercentral.com/questions/T-SQL/64521/) This got me thinking about what the impact would be on a Analysis Services cube.

I changed the code slightly so that I can build a very basic cube.

I then created a simple cube with the “MyTable” as the fact table and the “Dim” table as the dimension table. When the values are displayed at the all level of the Dim dimension the results are exactly the same as when you do the sum in TSQL. Even if you change the data type of the measures from Inherited to Double in the Analysis Services project you still get the incorrect aggregation.

Always use precision data types for the measures in your cube to avoid these aggregation errors.

Syntax Highlighter offline

2010-03-25T09:51:00.002Z

It seems that currently the syntax highlighter developed by Alex Gorbatchev is offline. The URL http://alexgorbatchev.com/ give a 403 Error. As a result all the code formatted by it is not displaying. This should be fixed soon and everything will hopefully be back to normal in a short while.

Combining multiple XMLA commands into one script

2010-03-18T07:22:00.004Z

If you have just started using XMLA to perform some actions against Analysis Services you might be wondering how to combine multiple actions (like create partition actions) into one executable script.

Lets say you have the following two Create Partition scripts.

If you want to execute both of these statements as one statement you need to enclose them in a Batch using the following XMLA at the beginning and end.

Notice that in the opening tag of the Batch statement you can specify whether the batch needs to run in a transaction or not but setting the value for “Transaction” to “True“ or “False”. The script can now be executed and all the commands inside the batch will be run one after the other. This becomes really handy if you need to execute a big batch of commands and want to run them all as one single script.

SQL Server 2008 R2 Release Date

2010-01-20T09:14:00.000Z

SQL Server 2008 R2 has now received its official release date. It is set for May 2010.

http://blogs.technet.com/dataplatforminsider/archive/2010/01/19/sql-server-2008-r2-gets-an-official-date.aspx

Maximum number of foreign key table references per table

2010-01-14T16:46:00.000Z

Under normal circumstances you would not easily exceed 253 foreign key table references to a particular table in your database; however in the off chance that you do you might receive the same error as I did.

When doing a delete from the table that was being referenced I received the following error message.

The query processor ran out of stack space during query optimization. Please simplify the query.

After reading the following MSDN article it was clear that the 253 limit was exceeded and that it took its toll on the hardware that was allocated for our SQL Server 2008 instance. We reduced the number of foreign key references after which the delete worked without any problems.

Start a SQL Server Job synchronously using a stored procedure

2009-11-30T21:21:00.006Z

When starting a SQL Server Agent job from your TSQL statements or stored procedures you will notice that the msdb.dbo.sp_start_job system stored procedure starts this job asynchronously. Meaning that the job will be started but that the stored procedure immediately returns after starting the job and does not wait for it to finish executing.

Below is a screenshot of the msdb.dbo.sp_start_job stored procedure starting a job.

This is understandable as these jobs could run for an indefinite amount of time and undoubtedly you would not want the connection to remain open and possibly timing out. However sometimes due to requirements you might need to start the job synchronously and thus wait for the job to finish running before the stored procedure will finish running. In order to do the I have written the following stored procedure that will do exactly that.

The code for the stored procedure is below.

The stored procedure accepts 3 parameters of which only two are required to successfully run.

The JobName parameter can be used to pass the name of the SQL Server Job which you want to start while the JobID parameter can be used to pass the Job Id of the job. In order to run the stored procedure only one of these parameters need to be specified.

The Delay parameter is used to specify the interval in seconds that will be waited before polling the database to check if the job has completed running. This parameter is optional and will default to 5 seconds if not specified.

Below is a screenshot of the stored procedure when used to execute a job. Note that the job that is being executed takes 10 seconds to complete.

The proc ran for 12 seconds before finishing. This is due to the job taking 10 seconds to run and the proc being executed with a Delay of 2 seconds.

Project Gemini is SQL Server Power Pivot for Excel 2010

2009-10-20T09:06:00.008+01:00

Microsoft yesterday announced at the SharePoint conference in Las Vegas that project Gemini is SQL Server Power Pivot for Excel 2010. Not only will this in memory analytics tool be available for Excel but it will also be available in SharePoint. For more information have a look at the following blog post here while the official Microsoft site for Power Pivot can be found here.

This will definitely add a lot of value to the Microsoft BI offering.

One or more parameters were not specified for the subreport

2009-10-09T23:45:00.001+01:00

When using subreports in Reporting Services you are sure to come across the following warning message, “One or more parameters were not specified for the subreport”. This normally is accompanied by the actual report displaying the message “Error: Subreport could not be shown” in the place where your subreport was to appear.

This message is normally generated by not passing an actual value to one of the parameters of the subreport but in some cases you might actually be passing values for all the parameters. The most probable reason for this problem is likely to be that you have some “available values” set on your subreport parameters.

In the example we will use the AdventureWorks database and Reporting Services 2008 with Service Pack 1 applied to our SQL Server instance. We will use one query for the subreport to get all the values form the Sales.SalesOrderDetails table for a specific SalesOrderID. The query is as follows:

SELECT SalesOrderDetailID,

CarrierTrackingNumber,

OrderQty,

UnitPrice

FROM Sales.SalesOrderDetail

WHERE SalesOrderID = @SalesOrderID

The value for the @SalesOrderID parameter will be supplied form the Main report in which the subreport will run. The query used in the main report is the following:

SELECT DISTINCT TOP 20 SalesOrderID,

OrderDate

FROM Sales.SalesOrderHeader

Below is a screenshot of the subreport.

Note that at this stage we have no values set for the @SalesOrderID parameter. When the subreport is previewed the user is prompted for the value of the @SalesOrderID parameter. When we provide a valid value the subreport executes and returns the corresponding records for the SalesOrderID value.

The main report that will be used is composed of a table with a grouping on the SalesOrderID. The subreport the subreport is added in the details row of the table and is executed for each one of the SalesOrderID values. Below is a screenshot of the report.

When the report is previewed the subreport is executed for each one of the SalesOrderID values as expected.

Lets now change our subreport and add an available value to the SalesOrderID parameter. This can either be a valid value or simply any value that the parameter will accept. This is shown in the screenshot below.

It is very important to note that although we are using specific values provided at design time for the subreport, the same behavior will occur when using a dataset to provide the values for the parameter in the subreport.

When the report is previewed the user is prompted to select the value for the SalesOrderID parameter from the drop down list. Once the value is selected the subreport executes and displays as expected.

When we now switch back to the main report and preview it you will see that only the first instance of the subreport renders correctly, all the subsequent executions of the subreport provides the error “Error: Subreport could not be shown”

If we change our subreport and set the available values to “None” the subreport will once again render correctly in the main report. (We will need to preview the subreport and provide a value as we did in the first instance where we previewed the subreport. This ensures that BIDS will reload the subreport when you run the main report, otherwise it is possible that the older version of it will still be used from the cache and the error will remain the same in the main report)

Once these changes are made the report again executes correctly.

Report Name built in field includes .rdl extension in SharePoint Integrated mode

2009-08-27T19:27:00.001+01:00

When your Reporting Services 2008 reports use the Report Name built in field you will be interested to know that based on the type of SSRS installation (Native Mode or SharePoint Integrated) you will receive different values.

Below is an example of a report with the name TestReportName.rdl This report only has the Report Name built in field displayed in a textbox on the report.

When this report is previewed inside Visual Studio or executed using Report Manager in a native mode installation the value of the Report Name built in field is the name of the RDL file excluding the “.rdl” file extension.

When the same report is deployed to a SharePoint Integrate Reporting Services installation the value of the Report Name built in function will include the “.rdl” file extension as in the example below.

In order to remove this you could use an expression in every instance where you use the Report Name built in field. However it would be much better to create a function in the custom code section. This would allow one central place where the code can reside and should you want to make any changes you would not need to change each expression.

The following expression will strip the “.rdl” section from the Report Name.

Public Function RepName(ByVal BIReportName As String) As String

RepName = Left(BIReportName, IIf(BIReportName.LastIndexOf(".") = -1, BIReportName.Length, BIReportName.LastIndexOf(".")))

End Function

Simply use the function created above in the expression as shown below.

Map report item in SQL Server 2008 R2 August CTP

2009-08-23T15:31:00.001+01:00

The August CTP for SQL Server 2008 R2 was made available on the 10th of August and one of the features available is the new Map report item in Reporting Services.

The map report item can be used to visualize business data against a geographical background. This is a really nice feature that will surely add a lot of value to Reporting Services.

The Map report item is visible on the screenshot as a globe in the toolbox.

The Map report item supports the the following spatial data sources:

Map Gallery reports
ESRI shapefiles
SQL Server spatial data stored in a database
Custom locations created by the developer

In the next post I will explore the capabilities of the Map report item and create a few reports that take advantage of this new feature.

Header row does not repeat on subsequent pages

2009-07-25T18:30:00.001+01:00

If you want the header row of a table to repeat in a Reporting Services 2008 report you might find that setting the “Repeat header rows on each page” setting (shown in the below screenshot) does not produce the desired results.

This normally happens when you have a table with no groupings, which would just contain the details group and a header row as in the screenshot below.

In order to get the header row to repeat you need to enable “Advanced Mode” on the groupings panes at the bottom of the report designer.

Once you have done this you will see that you are presented with a couple of static groups that now appear in the Row Groups and Column Groups panes.

Select the static group above the Details group in the Row Groups pane. This is the header row of the details group. In the properties window scroll down and find the “RepeatOnNewPage” property. Set this property’s value to “True”

After you have set this property run the report again. You will now see that the header row repeats on each page.

Error restoring a database (Operating system error 5(Access is denied.))

2009-07-21T20:23:00.001+01:00

A while ago I had to do a seemingly trivial task. Copy a database backup file to a server and restore the database. I was busy using SQL Server 2008 and the database backups were also made on a SQL Server 2008 instance.

After copying the files onto the machine I set about trying to restore the backup in the normal way through SQL Server Management Studio. I made sure that the files were not marked as read only. I received the following error.

Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Staging.bak'. Operating system error 5(Access is denied.).
RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)

I followed the link that it provides with the error http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476

At first it all seemed to be very strange as none of the conditions they were described was actually applicable to my situation. I then decided to check the permission of the account under which SQL Server was setup to run.

This account was part of the administrators group but yet I kept receiving the error. I then added the account to the security permission of the file and gave it full access, after which I could restore the backup with no problem.

To try and replicate this I ended up removing the permission I added but to my surprise I was still able to restore the database from the backup with no problem. For some strange reason things were now working exactly the way it should have from the beginning.

Microsoft BI MSDN Videos

2009-06-06T16:59:00.000+01:00

I have been searching for some good exam prep material for the 70-455 exam and posted a question regarding it on the Microsoft Learning forum. I got a few responses and one of them had this link to some very nice MSDN Videos regarding SQL Server 2008 BI.

How Do I - BI Videos

SQL Server 2008 R2 (Formerly codenamed “Kilimanjaro”) CTP

2009-05-12T09:31:00.001+01:00

Microsoft has announced that a CTP of SQL Server 2008 R2 (formerly codenamed “Kilimanjaro”) will be made available in the summer of 2009. This was announced at Tech Ed North America on the 11th of May along with a host of other announcements regarding Windows 7 and Server 2008. The press release can be found here.

There is also a SQL Server 2008 R2 website where you can sign up for an email notification when the CTP is made available. The SQL Server R2 site can be found here.

SQL Server 2008 R2 is set to have many tons of added functionality and feature. Here is a list of the new BI functionalities:

Master Data Services (MDS)

Self Service Analysis with “Project Gemini”

Self Service Reporting with SQL Server 2008 R2 Reporting Services

We have all been treated to the hype around Project Gemini and I do hope it delivers to the community’s expectations. One interesting thing I noticed was the details around the self service reporting with Reporting Services. More specifically the Shared Library approach to accelerate report development. To quite the site “Central, secure location for IT administrators and users to publish content objects that can be broadly reused and easily customized to meet the users’ needs”

This is something that many organisations have been trying to achieve through all sorts of ways. But did not have a straight forward approach, resulting in some trade offs between performance and functionality.

It seems that the Microsoft BI Stack is set for a brilliant functionality update with the coming SQL Server 2008 R2 release.

Dual Y-axis charts in Performance Point Server

2009-05-08T16:00:00.001+01:00

Ever wondered how to create a dual y-axis chart like the one below in Performance Point Server?

Have a look at the Microsoft Performance Point Team Blog here to see how to do it.

Some SharePoint and Reporting Services Integration notes

2009-05-08T12:34:00.001+01:00

I have recently been busy integrating SQL Server Reporting Services 2008 into Microsoft Office SharePoint Server 2007. What made this particularly interesting was that MOSS was being used as source control in conjunction with Report Builder 2.0.

So after this experience I decided that I will point out one or two of the things that we ran into that you might want to consider when doing a implementation.

Language and localisation

The first thing has to do with language and localisation. It is very easy to use the language settings of a client’s browser to localise your reports as well as the report manager. If you plan to use the Language ID of the client running your report you need to realise that once Reporting Services has been deployed in SharePoint Integrated Mode the Language ID being passed to your reports will be that of the SharePoint site and not the language of the user’s browser.

Now this makes complete sense as you would not want to display reports in a different language than the one your SharePoint site is in. It is however something that you need to be aware if you make use of this functionality (which was what our solution was doing). You could use the UserID (which is still passed correctly to Reporting Services) and keep information regarding your users’ language preferences in the database but this then adds to the maintenance of your solution.

Using Report Builder 2.0 to access report libraries in MOSS 2007

Report Builder 2.0 works very nicely with SharePoint. Combine that with the fact that Report Builder 2.0 now allows users the full report authoring capabilities, which use to be the sole domain of report developers using Visual Studio, and you have the makings of a very business friendly report authoring environment.

Make sure that you have SP1 for SQL 2008 installed as well as the latest Reporting Services add on for SharePoint. This will allow you to use ClickOnce Report Builder 2.0 which is extremely useful for allowing users to simply click on a report in a report library and selecting to open it with Report Builder.

One thing to keep in mind when using Report Builder and SharePoint in this type of scenario is that Report Builder will not check out the .rdl file from the report library in SharePoint. It will however save it as a new version so you will be able to roll back to previous versions. If you want to check out the report before editing you have to do it manually on your SharePoint site. Report Builder will allow a users to open a report that has been checked out by another user but it will not allow any changes to be saved to that report.

Conclusion

The experience of using Report Builder 2.0 and SharePoint to enable the business power users have been a pleasant one. Business power users find Report Builder 2.0 less intimidating due to the Office look and feel. Combining Report Builder 2.0 with SharePoint proved to be a very effective business enabler allowing business users to be in control of their own report development.

SSIS script task to monitor if remote process is still running

2009-05-01T11:35:00.001+01:00

A while ago I posted about how you can use a SSIS script task to start a process on a remote machine. (you can find that post here http://businessintelligencechronicles.blogspot.com/2009/02/using-ssis-script-task-to-executing.html)

That approach would work perfectly if you wanted to start a process and not have to wait until it finishes before continuing with the rest of your SSIS package tasks, however this is not always the case. Say for example you were starting the Performance Point PPSCmd.exe process to synchronise your staging database. You would want to wait for that process to complete before you continue with the rest of the SSIS tasks. In order to get this done you need to make some change to the script that was used in the above mentioned post.

Changing the script to monitor if the process is still running

After finding the following article regarding monitoring processes on a remote machine (http://vbnet.mvps.org/index.html?code/wmi/wmi_processmonitor.htm) I made changes to the original script. Below is the updated script that contains a new function and some code changes in the main section that will loop until the process stops executing on the remote machine.

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Management


Public Class ScriptMain


    Public Sub Main()
        'Declare variables that will be used in the script
        Dim retValue As String
        Dim retCode As String
        Dim Command As String = Dts.Variables("AppToStgCommand").Value.ToString
        Dim TargetMachine As String = Dts.Variables("TargetMachine").Value.ToString
        Dim User As String = Dts.Variables("User").Value.ToString
        Dim Password As String = Dts.Variables("Password").Value.ToString
        Dim WMIQuery As String

        'Add the correct Collection to the end of the Command variable
        Command = Command + " " + "Dimensions"

        'Use a try catch block to handle for any exceptions 
        Try
            retValue = RunCommand(Command, TargetMachine, User, Password)
            'get the return code out of the string that is returned
            retCode = retValue.Substring((retValue.IndexOf(";") + 1))

            'get the return value out of the string that is returned
            retValue = retValue.Substring(0, retValue.IndexOf(";"))

            'Set the value of the WMIQuery variable
            WMIQuery = "SELECT * FROM Win32_Process WHERE ProcessId=" & retCode 'w3wp.exe'"

            'Determine based on the value returned if the script task should fail or succeed 
            Select Case retValue
                'If 0 is returned the process was successfully executed 
                Case "0"
                    'Monitor if the process is till running
                    While GetProcessInfo(User, Password, TargetMachine, retCode, WMIQuery) = "True"
                    End While
                    Dts.TaskResult = Dts.Results.Success

                    'If 2 is returned access to the remote machine was denied 
                Case "2"
                    Dts.Events.FireError(2, "stSyncPPSDatabases", "Access(denied)", Nothing, Nothing)
                    Dts.TaskResult = Dts.Results.Failure

                    'If 3 is returned the account specified has insufficient privileges to perform the action 
                Case "3"
                    Dts.Events.FireError(2, "stSyncPPSDatabases", "Insufficient(privilege)", Nothing, Nothing)
                    Dts.TaskResult = Dts.Results.Failure

                    'If 8 is eturned an unknown failure occured 
                Case "8"
                    Dts.Events.FireError(2, "stSyncPPSDatabases", "Unknown(failure)", Nothing, Nothing)
                    Dts.TaskResult = Dts.Results.Failure

                    'If 9 is returned the path for the executable was not found 
                Case "9"
                    Dts.Events.FireError(2, "stSyncPPSDatabases", "Path(Not found)", Nothing, Nothing)
                    Dts.TaskResult = Dts.Results.Failure

                    'if 21 is returned an invalid parameter was specified 
                Case "21"
                    Dts.Events.FireError(2, "stSyncPPSDatabases", "Invalid(parameter)", Nothing, Nothing)
                    Dts.TaskResult = Dts.Results.Failure
            End Select

        Catch ex As Exception
            'Fire an error event if an exception occurs 
            Dts.Events.FireError(1, "stSyncPPSDatabases", ex.ToString, Nothing, Nothing)
            Dts.TaskResult = Dts.Results.Failure
        End Try




    End Sub

    Function RunCommand(ByVal strCommand As String, ByVal strMachineName As String, ByVal strUserName As String, ByVal strPassword As String) As String
        Dim options As New System.Management.ConnectionOptions
        options.Username = strUserName
        options.Password = strPassword

        Dim path As New System.Management.ManagementPath("\\" & strMachineName & "\root\cimv2:Win32_Process")
        Dim scope As New System.Management.ManagementScope(path, options)

        scope.Connect()

        Dim opt As New System.Management.ObjectGetOptions()
        Dim classInstance As New System.Management.ManagementClass(scope, path, opt)

        Dim inParams As System.Management.ManagementBaseObject = classInstance.GetMethodParameters("Create")
        inParams("CommandLine") = strCommand

        ' Execute the method and obtain the return values.
        Dim outParams As System.Management.ManagementBaseObject = classInstance.InvokeMethod("Create", inParams, Nothing)
        Return CStr(outParams("returnValue")) & ";" & CStr(outParams("processId"))

    End Function

    Public Function GetProcessInfo(ByRef User As String, ByRef Password As String, ByRef TargetMachine As String, ByRef retCode As String, ByRef WMIQuery As String) As String

        Try

            'Declare variables
            Dim options As New ConnectionOptions()
            Dim scope As New ManagementScope("\\" & TargetMachine & "\root\cimv2", options)
            Dim selectQuery As New SelectQuery(WMIQuery)
            Dim searcher As New ManagementObjectSearcher(scope, selectQuery)
            Dim IsProcessRunning As String = "False"

            'Set the values of the Options variables
            options.Username = User
            options.Password = Password

            'Connect to the remote server 
            Try

                scope.Connect()

            Catch f As Exception

                Dts.Events.FireError(1, "stSyncPPSDatabases", f.ToString(), Nothing, Nothing)

                Exit Function

            End Try

            'Get the data of the currently running process
            For Each queryObj As ManagementObject In searcher.Get()
                'Test to see if the process is running
                If queryObj("ProcessID").ToString = retCode Then
                    IsProcessRunning = "True"
                End If

            Next

            'Return the vale of the IsProcessRunning variable
            Return IsProcessRunning

        Catch f As Exception

            Dts.Events.FireError(1, "stSyncPPSDatabases", f.ToString(), Nothing, Nothing)

        End Try

    End Function



End Class

The script task will now only exit successfully after the process that was started finishes.

Extending SSIS with 3rd party components

2009-04-16T09:05:00.001+01:00

Every time I present an SSIS training course I make the comment that “SSIS can connect to anything and be extended to do almost anything”. I always get people looking at me like I have been abducted by a Microsoft UFO and brainwashed, so when I came across the blog post of Andy Leonard (http://sqlblog.com/blogs/andy_leonard/archive/2007/12/18/ssis-third-party-components-cozyroc.aspx) talking about CozyRoc http://www.cozyroc.com/ and their library of advanced custom SSIS 2005 and 2008 tasks, I thought that this was the perfect example to illustrate my point (as well as get people to realise there were no UFO abductions).

They have some brilliant components available and at an extremely reasonable price. Check it out today, it might just save you tons of time or solve a problem you thought was unsolvable.

Extending Reporting Services to your end users

2009-03-12T11:43:00.001Z

No one can argue against the fact that the current economic environment is forcing people to look at how much they are spending and how these funds are allocated. So without me repeating all the information that has already provided on this topic I would rather like to focus your attention on a product that could really help those organisations that have invested in the Microsoft BI stack.

Filling the gap

It is a well known fact that easy end user report creation is a gap that rears its head in any Microsoft BI implementation. I am not talking about your power users here. Microsoft has done an excellent job with Report Builder 2.0 in SQL Server 2008 to address the needs of power users and I would definitely recommend Report Builder 2.0 to any client wanting to enable their power users.

The gap I am talking about filling is the need to have your end users access data from your already existing cubes and data warehouses with as little disruption to their daily routine as possible. Of course there are many tools out there that can perform advanced visualisation when deployed against your cubes but to be honest, and I know I might be stepping on some toes here, those tools are rarely adopted by the end users we are talking about.

So what if you could allow these users to author their own reports quickly and easily in their web browser utilising Reporting Services?

RSinteract for end user report authoring

RSinteract is a web application that works with Reporting Services 2005 and 2008 and allows your end users to create their own reports from either a normal relational data source or from an OLAP data source. It is extremely simple to use, yet it has almost all the functionality of Reporting Service.

It is fully integrated with the Reporting Services meaning that all security and permissions are inherited form Reporting Services. This ensures that you incur no additional administration overheads when deploying the product.

Below is a video that illustrates how an end user would build a report from an OLAP data source.

If you would like to download a trial version of the software or perhaps look at the other video demos they have available visit their website at www.rsinteract.com

Using a SSIS Script task to execute a process (exe) on a remote machine

2009-02-15T11:50:00.001Z

I am sure that sooner rather than later anyone that is involved in developing ETL routines will have the need to execute an exe (process) on a remote machine. For example your SSIS packages are scheduled to executing on Server 1 and need to start or execute a process on Server 2. (In my case I needed to execute the “PPSCmd.exe” process to synchronise the Performance Point staging and application databases)

I found the following post by Steve Schofield to be extremely useful http://weblogs.asp.net/steveschofield/archive/2006/06/06/WMI---start-a-process-on-remote-machine-passing-credentials_2E00_.aspx

I took the scripts that he has given on his blog and changed it to be parameterised in a SSIS 2008 Script Task. The SSIS package contains the following variables.

Command: The Command variable will contain the command we want to execute on the remote machine. In our case we are going to execute the Calculator application on the target machine.

TargetMachine: This variable contains the name of the remote machine or the IP address.

User: The User variable contains the username of an account on the machine or the username of an Active Directory account. If you use and Active Directory account remember to prefix the username with the correct domain.

Password: The Password variable is the password for the specified username

Configuring the Script Task

As you can see in the screenshot below, all the variables are listed as read only variables.

Next we need to reference the System.Management. Right click on the script project in the Project Explorer and click on the “Add Reference” option.

This will open the “Add Reference” window. Under the “.NET” tab scroll down until you find the “System.Management” component and click on “OK”

The Script

Next we are going to have a look at the modified script that we will use to execute our process on the remote machine. Below is the code we are going to use.

Public Sub Main()

    'Declare variables and set them equal to the relevant variables in the SSIS package
    Dim retValue As String
    Dim Command As String = Dts.Variables("Command").Value.ToString
    Dim TargetMachine As String = Dts.Variables("TargetMachine").Value.ToString
    Dim User As String = Dts.Variables("User").Value.ToString
    Dim Password As String = Dts.Variables("Password").Value.ToString

    'Use a try catch block to cater for any exceptions
    Try
        retValue = RunCommand(Command, TargetMachine, User, Password)

        'Determine based on the value returned if the script task should fail or succeed
        Select Case retValue
            'If 0 is returned the process was successfully executed
            Case "0"
                Dts.TaskResult = ScriptResults.Success

                'If 2 is returned access to the remote machine was denied
            Case "2"
                Dts.Events.FireError(2, "stSyncPPSDatabases", "Access(denied)", Nothing, Nothing)
                Dts.TaskResult = ScriptResults.Failure

                'If 3 is returned the account specified has insufficient privileges to perform the action
            Case "3"
                Dts.Events.FireError(2, "stSyncPPSDatabases", "Insufficient(privilege)", Nothing, Nothing)
                Dts.TaskResult = ScriptResults.Failure

                'If 8 is eturned an unknown failure occured
            Case "8"
                Dts.Events.FireError(2, "stSyncPPSDatabases", "Unknown(failure)", Nothing, Nothing)
                Dts.TaskResult = ScriptResults.Failure

              'If 9 is returned the path for the executable was not found
            Case "9"
                Dts.Events.FireError(2, "stSyncPPSDatabases", "Path(Not found)", Nothing, Nothing)
                Dts.TaskResult = ScriptResults.Failure

                'if 21 is returned an invalid parameter was specified
            Case "21"
                Dts.Events.FireError(2, "stSyncPPSDatabases", "Invalid(parameter)", Nothing, Nothing)
                Dts.TaskResult = ScriptResults.Failure
        End Select

    Catch ex As Exception
        'Fire an error event if an exception occurs
        Dts.Events.FireError(1, "stSyncPPSDatabases", ex.ToString, Nothing, Nothing)
        Dts.TaskResult = ScriptResults.Failure
    End Try

End Sub

Function RunCommand(ByVal strCommand As String, ByVal strMachineName As String, ByVal strUserName As String, ByVal strPassword As String) As String
    Dim options As New System.Management.ConnectionOptions
    options.Username = strUserName
    options.Password = strPassword

Dim path As New System.Management.ManagementPath("\\" & strMachineName & "\root\cimv2:Win32_Process")
Dim scope As New System.Management.ManagementScope(path, options)

scope.Connect()

Dim opt As New System.Management.ObjectGetOptions()
Dim classInstance As New System.Management.ManagementClass(scope, path, opt)

Dim inParams As System.Management.ManagementBaseObject = classInstance.GetMethodParameters("Create")
inParams("CommandLine") = strCommand

' Execute the method and obtain the return values.
Dim outParams As System.Management.ManagementBaseObject = classInstance.InvokeMethod("Create", inParams, Nothing)
Return CStr(outParams("returnValue")) '& " Process ID: {0}" & CStr(outParams("processId"))

End Function

Like I indicated earlier, just a couple of small changes to the script Steve Schofield provided on his blog. When we execute the SSIS package the “Calc.exe” process will be started on the remote machine that we have specified.

When you have a look at the processes in the Task Manager on the remote machine you will now see the “Calc.exe” process running. (Make sure you tick the tick box stating that you want to show processes from all users if you used a username and password that is different from the one you have logged onto the target machine with)

I hope the above comes in handy should you ever have the requirement to execute a process on a remote machine from a SSIS package.

Microsoft’s Performance Point announcement and the opportunistic marketing strategies of competitors

2009-01-29T10:36:00.001Z

I am going to warn you from the start that this will be a rant. I am extremely disappointed in the post Performance Point marketing propaganda of some of Redmond’s competitors/partners.

Let me start by saying that I completely understand that a lot of Microsoft partners are now feeling the pain due to the loss of investment in the to be discontinued Performance Point Planning module. This is something that no one can deny will cause a shock to the Microsoft partner network. It is also regrettable that there will no longer be a stand alone planning module as part of the Microsoft BI offering. The official stance on this is that budgeting and planning will be implemented through Microsoft Dynamics and the office 14 suite.

On the other hand the future looks much brighter for Microsoft’s vision of bringing BI to the masses. Not only have they reduced the total cost of ownership of a Microsoft BI solution even further, but they have now made dashboarding, scorecarding and analytic reporting FREE for their Enterprise SharePoint licence holders.

Now for the part that I warned you about. I find it shocking that there are some vendors out there that jumped at the opportunity to bash the Microsoft BI offering. These vendors now proclaim that they will ease the pain for Microsoft customers due to the incomplete Microsoft BI offering. All of this when they do not even have a planning product as part of their own proposed offering. Yes you have guessed it, they only do dashboarding, scorecarding and reporting, on top of that these products merely extend the capabilities of the platforms they are deployed on. In other word they still require a BI platform, such as the Microsoft SQL Server BI stack, or that of another vendor in order to be functional at all!

All I can say is shame on you for deceiving customers! As for the Microsoft announcement as I previously mentioned, yes it is a disappointment but there is also a tremendous upside for those partners willing to embrace the change and target the new market that has just been made available to them. The Microsoft BI offering (one of the best available if I may say so) just got even cheaper and the future has never looked brighter!

Another take on splitting Reporting Services multi-value parameters using a SQL function

2009-01-28T22:08:00.002Z

This is more of a quick post that I thought I would share. I have been meaning to add the SQL function that I use to split Reporting Services multi-value parameters in my stored procedures of my reports for a long time.

This topic has been covered extensively on many forums and blogs so without me retyping what has already been said here are a few of the links:

The MSDN article regarding single and multi-value parameters.
http://msdn.microsoft.com/en-us/library/aa337292.aspx

Simon Sabin's blog on SQL Server stuff
http://sqlblogcasts.com/blogs/simons/archive/2007/11/22/RS-HowTo---Pass-a-multivalue-parameter-to-a-query-using-IN.aspx

Now I know that there are many variations on this type of "split" function and I thought it useful to share the function that I have used on occasions in the past as it is very simple and easy to understand.

CREATE FUNCTION udf_SplitParameter
(

--The actual multi-value parameter from reporting services
@String NVARCHAR(4000),

--The separator that will is used to delimit each new value in the string
@Seperator NVARCHAR(1)
)
RETURNS @Table TABLE(Result NVARCHAR(4000))
AS
BEGIN
   --Loop through the string and insert each section into the return table of the table valued function
   WHILE CHARINDEX(@Seperator,@String) <> 0
    BEGIN
    --Insert the value into the return table of the table valued function
    INSERT INTO @Table (Result)
    SELECT SUBSTRING(@String,1,CHARINDEX(@Seperator,@String) - 1)
    --Remove the inserted string from the original string
    SET @String = RIGHT(@String, LEN(@String) - CHARINDEX(@Seperator,@String))
    END
   --Insert the last value in the string into the return table of the table valued function
   INSERT INTO @Table (Result)
   SELECT SUBSTRING(@String,1,LEN(@String))
   RETURN
END

As you can see it is quite a simple function and works really well. For example you do not have to write any dynamic SQL to build up the string when you pass an integer surrogate key value (Using a multi-value parameter in SSRS) to a stored procedure. For example the following stored procedure would give and error.

CREATE PROCEDURE GetSales
(
@Parameter NVARCHAR(800)
)
AS
BEGIN
   SELECT *
   FROM dbo.FactInternetSales
   WHERE ProductKey IN (@Parameter)
END

It would give an error stating that it could not convert the nvarchar value to integer. That is because Reporting Services converts the result that is returned when you select multiple values in the parameter drop down into a string value separated by commas (,).

When you use the split function instead it will work perfectly. Below is an example.

CREATE PROCEDURE GetSales
(
@Parameter NVARCHAR(800)
)
AS
BEGIN
   SELECT *
   FROM dbo.FactInternetSales
   WHERE ProductKey IN (SELECT Result FROM dbo.udf_SplitParameter(@Parameter,','))
END

Have a look at the result returned by the splitting function below.

The @Parameter variable simulates the way that reporting services will pass a multi-value parameter to your SQL Server stored procedure. As you can see the function returns the string of the parameter in a table and this eliminates any dynamic SQL that you might have had to write in the past.

Allowing users to create their own hierarchies in a cube

2008-12-27T23:00:00.003Z

I have recently been tasked with a somewhat challenging, yet very interesting, request by a client. The request was to allow the users of a BI solution to create their own hierarchies with unlimited levels and leaf members in a cube.

From a business point of view this makes perfect sense. It will allow the users to slice and dice their data in any way that they want or need to.

For example lets say that the users need to restructure their sales teams or that they want to create a special hierarchy that contains all products that were part of a specific sale. By allowing the users to create their own hierarchies they can move the products that were on sale or restructure the sales teams in the desired way and analyse their data according to the new hierarchies.

Sounds like a great plan? Well lets have a look at how you can achieve this.

Creating the tables

So the first thing to do is to create the tables needed for the task at hand. Lets first look at the tables used by the users to create their hierarchies. The database diagram of the tables are shown below.

The three tables are used to allow the users to create the hierarchies through a .NET front end. The CustomHierarchy table will contain all the different hierarchies that the users create. The CustomHierarchyLevel table will contain the hierarchy levels that the users create and the CustomHierarchyLeaf table will contain the actual leaf level members as they are assigned by the users. (We will cover the .NET front end in another post, in this post we will be focus on the database and Analysis Services solution)

Next we have a look at the table that we will use to build the parent child hierarchy in Analysis services.

The DimCustomHierarchy table will be populated using the three tables previously discussed. This is the table that will be used in Analysis Services to build a parent child hierarchy that will allow the users to explore the data in the cube according to their create hierarchies.

In order to link the DimCustomHierarchy table to the fact table in the cube we will need a intermediate fact table, for this we create the FactHierarchy table.

This table will contain the surrogate key of the relevant leaf level members as well as the surrogate key of the custom hierarchies.

The last table we create is the CustomHierarchyHistory table that will contain the CustomHierarchyID of each hierarchy that has already been added to the DimCustomHierarchy table. This is done as to reduce the amount of time spent building the DimCustomHierarchy table. A screenshot of the table is shown below.

The code to create the tables are as follows:

--Create the table that will contain the names of the Custom Hierarchies
CREATE TABLE CustomHierarchy(CustomHierarchyID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
                                                                CustomHierarchyName VARCHAR(100),
                                                                Modified BIT
                                                               );
--Create the table that will contain the Custom Hierarchy Nodes
CREATE TABLE CustomHierarchyLevel(CustomHierarchyLevelID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
                                                                         CustomHierarchyLevelName VARCHAR(100),
                                                                         CustomHierarchyParentLevelID INT,
                                                                         CustomHierarchyID INT FOREIGN KEY REFERENCES CustomHierarchy(CustomHierarchyID)
                                                                       );

--Create the table that will contain the Custom Hierarchy Leaf Level members
CREATE TABLE CustomHierarchyLeaf(CustomHierarchyLeafID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
                                                                        CustomHierarchyLeafName VARCHAR(100),
                                                                        CustomHierarchyLeafKey INT,
                                                                        CustomHierarchyLevelID INT FOREIGN KEY REFERENCES CustomHierarchyLevel(CustomHierarchyLevelID)
                                                                     );

--Create the table that contains the user create parent child hierarchies
CREATE TABLE DimCustomHierarchy(CustomHierarchySK INT IDENTITY(1,1),
                                                                       CustomHierarchyParentSK INT,
                                                                       CustomHierarchyDescription VARCHAR(100),
                                                                       CustomHierarchyID INT,
                                                                       CustomHierarchyLevelID INT,
                                                                       CustomHierarchyParentLevelID INT,
                                                                       CustomHierarchyLeafID INT,
                                                                       CustomHierarchyLeafKey INT
                                                                      );

--Create the table that will contain the history of the custom hierarchies
CREATE TABLE CustomHierarchyHistory(CustomHierarchyID INT)

--Create the intermadiate fact table for use in Analysis Services
CREATE TABLE FactHierarchy (CustomHierarchySK INT,
CustomHierarchyLeafKey INT
);

Creating a custom hierarchy

Now that we have had a look at the tables that we will use lets create a custom hierarchy and look at the data that will be contained in the first three tables we discussed. The hierarchy we will be creating will be a hierarchy that contains all the items used in a specific sale.

Below is the structure of the hierarchy as the users will have created it by dragging and dropping the items under the levels in the custom hierarchy.

In the structure above you will see that we have a new user created hierarchy called Sales 2008. This hierarchy has got three levels called Unassigned, End of range, New lines.

The Unassigned level will contain all the leaf level members that are not used in any other levels in the hierarchy.

The End of range level contains two sub levels 50% off and 75% off. These two sub levels have got the leaf level members assigned to them.

The New lines level contains no sub levels and have the leaf level members assigned directly to it.

Lets have a look at how the three tables we discussed first look after the records have been added to them. The CustomHierarchy table will contain one record and is shown below.

The CustomHierarchyLevel table will contain 5 records. Those levels that are the parent level of others will have a NULL value in the field CustomHierarchyParentLevelID.

The CustomHierarchyLeaf table will contain all the leaf level members that have been assigned to the levels in the hierarchy. Below is a screen shot of what the table looks like.

Building the hierarchies

So the next thing we do is insert the rows into the DimCustomHierarchy table. For that we use the following stored procedure.

CREATE PROCEDURE prc_LoadCustomHierarchies
AS
BEGIN
--Declare table variables
DECLARE @LoadHierarchies TABLE (CustomHierarchyID INT)

   --Insert new hierarchies to be loaded into the temp table
   INSERT INTO @LoadHierarchies(CustomHierarchyID)
   SELECT CustomHierarchyID
   FROM CustomHierarchy CH
   WHERE CH.CustomHierarchyID NOT IN (SELECT CustomHierarchyID
                                                                               FROM CustomHierarchyHistory
                                                                             ) OR CH.Modified = 1;

   --Insert the new hierarchies into the DimCustomHierarchy table
   INSERT INTO DimCustomHierarchy (CustomHierarchyDescription,
                                                                       CustomHierarchyID
                                                                      )
   SELECT CustomHierarchyName,
                    CustomHierarchyID
   FROM CustomHierarchy CH
   WHERE CH.CustomHierarchyID IN (SELECT CustomHierarchyID FROM @LoadHierarchies);

   --Insert the levels into the DimCustomHierarchy table
   INSERT INTO DimCustomHierarchy(CustomHierarchyParentSK,
                                                                      CustomHierarchyDescription,
                                                                      CustomHierarchyLevelID,
                                                                      CustomHierarchyParentLevelID,
                                                                      CustomHierarchyID
                                                                    )
   SELECT DC.CustomHierarchySK,
                    CL.CustomHierarchyLevelName,
                    CL.CustomHierarchyLevelID,
                    CL.CustomHierarchyParentLevelID,
                    CL.CustomHierarchyID
   FROM CustomHierarchyLevel CL
      INNER JOIN DimCustomHierarchy DC ON CL.CustomHierarchyID = DC.CustomHierarchyID
   WHERE CL.CustomHierarchyID IN (SELECT CustomHierarchyID FROM @LoadHierarchies);

   --Update the CustomHierarchyParentSK field for the Hierarchy records
   UPDATE DC
     SET DC.CustomHierarchyParentSK = DC.CustomHierarchySK
   FROM DimCustomHierarchy DC
   WHERE DC.CustomHierarchyParentSK IS NULL AND
                   DC.CustomHierarchyID IN (SELECT CustomHierarchyID FROM @LoadHierarchies);

   --Update the CustomHierarchyParentSK field for the level records
   UPDATE DC
   SET DC.CustomHierarchyParentSK = DC2.CustomHierarchySK
   FROM DimCustomHierarchy DC
     INNER JOIN DimCustomHierarchy DC2 ON DC.CustomHierarchyParentLevelID = DC2.CustomHierarchyLevelID
   WHERE DC.CustomHierarchyID IN (SELECT CustomHierarchyID FROM @LoadHierarchies);

   --Insert the leaf level members into the DimCustomHierarchy table
   INSERT INTO DimCustomHierarchy(CustomHierarchyDescription,
                                                                      CustomHierarchyLeafID,
                                                                      CustomHierarchyLeafKey,
                                                                      CustomHierarchyParentSK
                                                                     )
   SELECT CL.CustomHierarchyLeafName,
                    CL.CustomHierarchyLeafID,
                    CL.CustomHierarchyLeafKey,
                    DC.CustomHierarchySK
   FROM CustomHierarchyLeaf CL
     INNER JOIN DimCustomHierarchy DC ON CL.CustomHierarchyLevelID = DC.CustomHierarchyLevelID
   WHERE DC.CustomHierarchyID IN (SELECT CustomHierarchyID FROM @LoadHierarchies)

--Truncate the many to many fact table for the DimCustomHierarchy table
TRUNCATE TABLE FactHierarchy

   --Create the many to many fact table for the DimCustomHierarchy dimension
   INSERT INTO FactHierarchy(CustomHierarchySK,
                                                        CustomHierarchyLeafKey
                                                      )
   SELECT CustomHierarchySK,
                    CustomHierarchyLeafKey
   FROM DimCustomHierarchy
   WHERE CustomHierarchyLeafKey IS NOT NULL;

   --Insert new hierarchies into the CustomHierarchyHistory table
   INSERT INTO CustomHierarchyHistory(CustomHierarchyID)
   SELECT CustomHierarchyID
   FROM @LoadHierarchies LH
   WHERE LH.CustomHierarchyID NOT IN (SELECT CustomHierarchyID
                                                                              FROM CustomHierarchyHistory
                                                                             );
   --Reset modified field on CustomHierarchy table
   UPDATE CH
     SET Modified = 0
   FROM CustomHierarchy CH
   WHERE CustomHierarchyID IN (SELECT CustomHierarchyID FROM @LoadHierarchies);

END

The result of the stored procedure is displayed below.

As you can see we now have all the records inserted into the DimCustomHierarchy table ready to be utilised in the creation of the parent child hierarchy in Analysis Services.

Creating the cube

So now that our hierarchies have been built it is time to create the cube. Below is the data source view of our cube.

In the cube we create a couple of dimension as can be seen in the screenshot below.

Of the dimension in the solution the most important one is the Custom Hierarchy dimension. This dimension contains a parent child hierarchy, that has been created using the CustomHierarchySK, CustomHierarchyParentSK and CustomHierarchyDescription fields.

This is the dimension that will allow the users to slice and dice their information according to the hierarchies they have created. This dimension is linked through the FactHierarchy table with the FactSales table. This is displayed in the screnshot of the dimension usage tab of the cube below.

After all the work that have been done we are now set to browse the cube according using the hierarchies that we have created according to our business needs.

Below is a screenshot of the cube being browsed using the custom hierarchy that was created.

If a user now decides to add another hierarchy to the BI solution browsing the cube will look similar to the screenshot below.

Conclusion

I am sure that everyone can agree that the ability to allow users to create hierarchies as per their business requirements is a big advantage in any BI solution.

It must be noted that this must be used sparingly as it will have a performance impact on the cube used in the solution. This can be partly offset by using the correct processing strategies but the use of an intermediate fact table does come at a cost.

Getting your drive fragmentation percentage programmatically

2008-09-14T19:10:00.000+01:00

Recently a colleague of my asked me if there is a way to get the fragmentation percentage of a drive programmatically. My first thought was to do this in SSIS but my colleague wanted to get the information using a TSQL script on any version of SQL Server and this prompted me to look for other means of accomplishing this.

The easiest way to get this done was via a visual basic script. I found a very helpful section at Microsoft Script Center that provides you with a very useful guide on how to write a script to accomplish this exact task (http://www.microsoft.com/technet/scriptcenter/topics/win2003/defrag.mspx#EKEAC).

Since the script provided at the above location returns all the information that can be returned from the Win32_DefragAnalysis method of the Win32_Volume WMI class I have decided to strip this down into the following script.

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colVolumes = objWMIService.ExecQuery("SELECT * FROM Win32_Volume WHERE DriveType = 3")

For Each objVolume in colVolumes
   errResult = objVolume.DefragAnalysis(blnRecommended, objReport)
    If errResult = 0 Then
        Wscript.Echo "Volume Name: " & objVolume.Name & "; Total fragmentation: " & _
            objReport.TotalPercentFragmentation & "%"
        Wscript.Echo
    Else
        Wscript.Echo objVolume.Name & " could not be analyzed." & _
        " Error number " & errResult & " occurred."
        Wscript.Echo
    End If
Next

This small script will loop through all the local drives available and it will return the name and the total fragmentation percentage of the drive. (By using the DriveType property of the Win32_Volume WMI Class in the WHERE clause we filter our results to that of the local drives only. By specifying only the local drives we ensure that we will not receive any errors for the optical drives attached to the system.)

If we change the strComputer variable's value to that of a remote machine the query will return the fragmentation information of the remote machine.

The next step will be to save the above script to a file on your drive. You can use notepad to save it. Make sure that you save it with an extension of .vbs otherwise you will not be able to execute the script with the windows script engine.

It is very important to note that the DefragAnalysis and Defrag methods of the Win32_Volume class are only available in Windows Server 2003 and newer editions of windows. It is not available in Windows Server 2000 or Windows XP.

Executing your script from SQL Server

The next step will be to execute our script from SQL Server. Before you do this you need to ensure that you are capable of executing the xp_cmdshell extended stored procedure. Use the Books Online of your particular version of SQL Server to find the steps needed to enable the use of xp_cmdshell.

The following TSQL will execute the script that I saved as scptGetDriveAnalysis.vbs in the Temp folder of my C: drive.

exec master.dbo.xp_cmdshell 'cscript C:\Temp\scptGetDriveAnalysis.vbs'

The result should be similar to the screenshot below.

You can see that I only have two local hard drives on my machine (C:\ and E:\) and the total fragmentation percentage for each is listed next to it.

Now if you would like to use this information to determine which drive you need to defrag you might want to changes the script to look similar to this.

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colVolumes = objWMIService.ExecQuery("SELECT * FROM Win32_Volume WHERE DriveType = 3")

For Each objVolume in colVolumes
   errResult = objVolume.DefragAnalysis(blnRecommended, objReport)
    If errResult = 0 Then
        Wscript.Echo "Drive: " & objVolume.Name & ";" & _
            objReport.TotalPercentFragmentation & _
        Wscript.Echo
    Else
        Wscript.Echo objVolume.Name & " could not be analyzed." & _
        " Error number " & errResult & " occurred."
        Wscript.Echo
    End If
Next

You will see that I have removed most of the text description in front of the values that will be displayed. This will allow us to get the values into a table much easier. Have a look at the script below that creates a table and inserts the values into it.

--Create the table that will contain the values for future use
CREATE TABLE dbo.DefragAnalysis(Date DATETIME,
                                Drive VARCHAR(10),
                                FragmentationPerc FLOAT
                               )

--Declare a table variable for temporary use
DECLARE @TempTable TABLE (TxtLine VARCHAR(80))

--Insert the results of the script into the table variable
INSERT @TempTable
exec ('exec master.dbo.xp_cmdshell ''cscript C:\Temp\scptGetDriveAnalysis.vbs''')

--Delete all the unwanted information from the table variable
DELETE FROM @TempTable WHERE TxtLine NOT LIKE '%Drive%' OR TxtLine IS NULL

--Insert the data into the table
INSERT INTO dbo.DefragAnalysis (Date,
                                Drive,
                                FragmentationPerc
                               )
SELECT Cast(Floor(Cast(Getdate()AS Float)) AS DateTime) AS Date,
       SUBSTRING(TxtLine,8,CHARINDEX(';',TxtLine) -8) AS Drive,
       SUBSTRING(TxtLine,CHARINDEX(';',TxtLine) +1,LEN(TxtLine)) AS FragmentationPerc
FROM @TempTable

If we query the dbo.DefragAnalysis table our results are as follows.

Taking it one step further

After all this work to get the fragmentation percentage of a drive it would be very disheartening if we could not defrag the drives that have a fragmentation percentage above our maintenance threshold.

Luckily we are able to do this and you can use the script below to do exactly that.

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colVolumes = objWMIService.ExecQuery _
    ("Select * from Win32_Volume Where Name = 'C:\\'")

    Set colProcesses = objWMIService.ExecQuery _
    ("Select * from Win32_Process Where Name = ' Dfrgntfs.exe'")

    If colProcesses.Count = 0 Then
      For Each objVolume in colVolumes
            Wscript.Echo "Please wait while drive " & objVolume.Name & " is defragged."
            Wscript.Echo
            errResult = objVolume.Defrag()

           If errResult = 0 Then
                Wscript.Echo "Drive " & objVolume.Name & " successfully defragged."
            Else
               Wscript.Echo "Drive " & objVolume.Name & " could not be defragged."
                Wscript.Echo "Error number " & errResult & " occurred."
                Err.Clear
            End If
        Next

    Else
      Wscript.Echo " Dfrgntfs.exe is running."
End If

The above script will first check to see if an instance of defrag is already running before it starts to defrag the relevant drive.

You can modify the script by changing "C:\" to the drive that you would like to defrag. We could create a script for each drive of our system and execute the script for a particular drive based on the fragmentation percentage stored in our DefragAnalysis table from within a stored procedure or script.

Alternatively we could move the logic to defrag a drive above a certain fragmentation percentage into our script instead of having it in a stored procedure or SQL Script. The following script will defrag all drives with a total fragmentation percentage above 15%.

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colVolumes = objWMIService.ExecQuery("SELECT * FROM Win32_Volume WHERE DriveType = 3")
Set colProcesses = objWMIService.ExecQuery("Select * from Win32_Process Where Name = ' Dfrgntfs.exe'")

For Each objVolume in colVolumes
    errResult = objVolume.DefragAnalysis(blnRecommended, objReport)
    If errResult = 0 Then
      If colProcesses.Count = 0 AND objReport.TotalPercentFragmentation > 15 Then
            Wscript.Echo "Please wait while drive " & objVolume.Name & " is defragged."
            Wscript.Echo
            errDefragResult = objVolume.Defrag()

           If errDefragResult = 0 Then
                Wscript.Echo "Drive " & objVolume.Name & " successfully defragged."
            Else
                Wscript.Echo "Drive " & objVolume.Name & " could not be defragged."
                Wscript.Echo "Error number " & errResult & " occurred."
                Err.Clear
            End If
        ElseIf colProcesses.Count = 0 AND objReport.TotalPercentFragmentation < 15 Then
           Wscript.Echo "Drive " & objVolume.Name & " does not need to be defragged."
        Else
              Wscript.Echo "Drive " & objVolume.Name & " could not be defragged."
                Wscript.Echo "Error number " & errResult & " occurred."
                Err.Clear
        End If
   Else
        Wscript.Echo objVolume.Name & " could not be analyzed." & _
        " Error number " & errResult & " occurred."
        Wscript.Echo
    End If
Next

As mentioned earlier in this post, we can change the value of the strComputer variable to that of a remote machine and in doing so defrag the drives of our remote machine programmatically.

SSIS package for SQL Server Maintenance

2008-08-05T20:29:00.000+01:00

Just a quick post regarding SSIS package for SQL Server Maintenance. Few people actually know that you don't always have to open up the Business Intelligence Development Studio to design SSIS packages. A good example of this is designing your SSIS Maintenance Package from the SQL Server Management Studio.

Have a look at the below screenshot. You will see that you can design your maintenance routines by right clicking on Maintenance Plans under the Maintenance section in SQL Server Management Studio and selecting New Maintenance Plan.

You will be prompted to supply a name for your maintenance plan after which you will see a screen that looks very much like the control flow in BIDS.

From here you can design your maintenance plan which is an SSIS package that will be stored in the MSDB database. Once you connect to your Integration Services Engine in SQL Server Management Studio you will find the maintenance plan package saved under the Store Packages in the MSDB subsection.

Integration Services 2008 - Data Profiling Task

2008-08-01T05:39:00.001+01:00

I have recently been playing with SQL Server 2008 RC0 and have been creating some demos for presentation purposes and would like to share with you a couple of really cool new features.

The release to market of SQL Server 2008 is fast becoming one of the most anticipated events on my calendar. With new features such as the Data Profiling Task in SSIS 2008 how can it not be?

Getting a good look at source data

I am sure that anyone that have been involved in any kind of ETL knows the pain of bad data. Each developer normally has his or her own way of analysing the source data whether it's a third party tool or internal process. In SQL Server 2008 Integration Services Microsoft has provided us with a nice and nifty task call the Data Profiling Task.

The Data Profiling Task allows you to create a set of profiles for tables or views. The source database has to be SQL 2000 or later and an ADO.NET connection manager is used to provide access to the tables or views.

A quick introduction

Let's look at creating a quick profile of a table with this new task.

As I have mentioned previously you need to set up a ADO.NET connection manager to you source database. From the below screenshot you will see that I have established a connection to the Adventure Works 2008 database. (you can get it on CodePlex if you don't already have it)

Now that we have our connection manager the next step is to add a Data Profiling Task to our control flow. Yes the task resides in the control flow and not the data flow.

So now that we have our Data Profiling Task added to the control flow we need to configure it in order to create our data profile.

On the general section of the Data Profiling Task Editor we specify how we want the results to be saved. You have two options one being an XML file and the other being a variable. We will use the XML file as we will use the Data Profiling Viewer at the end to look at the profiling data that the task creates.

On the Profile Requests section we have the ability to manually specify which profiles we want to create and on which columns. Instead of doing an in depth blog post this post is aimed at a brief introduction to this task. We will click on the "Quick Profile" button at the bottom of the General section. This allows us to quickly setup a number of profiles for a specific table or view and is generally a good start if you want to quickly set up profiling for a specific source.

When you click on the "Quick Profile" button the Single Table Quick Profile Form window is launched. Here you will be able to select the different profiles that you would like to create. Keep in mind that this is for a single table. If you want to use multiple tables as you would for certain profiles you need to use the Profile Request section to set up the profiles manually.

In the above screenshot you will see that we specify the ADO.NET connection manager that we created earlier. We then choose the table on which we want to perform the analysis. I have chosen the Product table in the Production schema.

Next we choose the different profiles that we would like to create on the table. (When you use the Quick Profile the profiles that you compute will be done on all the columns in the table where the column supports the profile. For example the Column Patter Profile can only be computed on columns of a text data type (varchar, nvarchar etc.) thus it will not be created for numeric column.)

Here is an extract from Books Online of SQL Server RC0 with the definitions and some detail regarding the different profiles:

Value	Description
Column Null Ratio Profile	Compute a Column Null Ratio Profile. The Column Null Ratio Profile reports the percentage of null values in the selected column. This profile can help you identify problems in your data such as an unexpectedly high ratio of null values in a column. For example, you profile a Zip Code/Postal Code column and discover an unacceptably high percentage of missing codes.
Column Statistics Profile	Select this option to compute a Column Statistics Profile using the default settings for all applicable columns in the selected table or view. The Column Statistics Profile reports statistics such as minimum, maximum, average, and standard deviation for numeric columns, and minimum and maximum for datetime columns. This profile can help you identify problems in your data, such as dates that are not valid. For example, you profile a column of historical dates and discover a maximum date that is in the future.
Column Value Distribution Profile	Compute a Column Value Distribution Profile. The Column Value Distribution Profile reports all the distinct values in the selected column and the percentage of rows in the table that each value represents. This profile can also report values that represent more than a specified percentage in the table. This profile can help you identify problems in your data such as an incorrect number of distinct values in a column. For example, you profile a column that contains states in the United States and discover more than 50 distinct values.
Column Length Distribution Profile	Compute a Column Length Distribution profile by using the default settings for all applicable columns in the selected table or view. This profile reports all the distinct lengths of string values in the selected column and the percentage of rows in the table that each length represents. This profile can help you identify problems in your data, such as values that are not valid. For more information about the settings for this profile, see Column Length Distribution Profile Request Options (Data Profiling Task).
Column Pattern Profile	Compute a Column Pattern Profile. The Column Pattern Profile reports a set of regular expressions that cover the specified percentage of values in a string column. This profile can help you identify problems in your data, such as strings that are not valid strings. This profile can also suggest regular expressions that can be used in the future to validate new values. For example, a pattern profile of a Zip Code/Postal Code column might produce the regular expressions: \d{5}-\d{4}, \d{5}, and \d{9}. If you see other regular expressions, your data likely contains values that are not valid or in an incorrect format.
Candidate Key Profile	Compute a Candidate Key Profile. This profile This profile reports whether a column or set of columns is a key, or an approximate key, for the selected table. This profile can also help you identify problems in your data, such as duplicate values in a potential key column.
for up to N Column keys	Select the maximum number of columns to test in possible combinations as a key for the table or view. The default value is 1. The maximum value is 1000. For example, selecting 3 tests one-column, two-column, and three-column key combinations.
Functional Dependency Profile	Compute a Functional Dependency Profile. The Functional Dependency Profile reports the extent to which the values in one column (the dependent column) depend on the values in another column or set of columns (the determinant column). This profile can also help you identify problems in your data, such as values that are not valid. For example, you profile the dependency between a column of United States Zip Codes and a column of states in the United States. The same Zip Code should always have the same state, but the profile discovers violations of this dependency.
for up to N Columns as the determiner	Select the maximum number of columns to test in possible combinations as the determinant columns. The default value is 1. The maximum value is 1000. For example, selecting 2 tests combinations in which either single columns or two-column combinations are the determinant columns for another (dependent) column.
Value Inclusion Profile	Compute a Value Inclusion Profile. The Value Inclusion Profile computes the overlap in the values between two columns or sets of columns. This profile can also determine whether a column or set of columns is appropriate to serve as a foreign key between the selected tables. This profile can also help you identify problems in your data such as values that are not valid. For example, you profile the ProductID column of a Sales table and discover that the column contains values that are not found in the ProductID column of the Products table.

It is important to note that Value Inclusion Profile Type is not available for the Single Table Quick Profile Form.

I am very sure that by only looking at the information in the above table that this feature will add a great deal of value to any ETL project.

Next we click on the "OK" button. This will take us to the Profile Requests section of the Data Profile Task Editor. Here you will see that we now have the profiles we ticked in the Single Table Quick Form created in this section. Below is a screenshot of Profile Requests section.

In the screenshot I have selected the Null Ratio Profile Request. You will notice that in the Request Properties section we have the all the column in our table selected for the Columns property. When you setup the Profiles manually you will specify these details for each and every profile you create. You will see that it will thus be possible to create a profile consisting of different profiles request for different tables and views, even using different ADO.NET Connection Managers all using one Data Profiling Task.

Next we click on "OK" which completes out setup of the Data Profiling Task. We then run the package to create our output file.

Viewing the output file

So now we have created our output file but what is next? We have to view the data of course. If you open up the file with notepad or Internet Explorer you will see that it is in XML format. This allows all sort of other capabilities that I will touch on later.

Luckily we do not need to view the output file in Internet Explorer. We have a utility specifically designed to view the output files of the data profiling task. The utility is the Data Profile Viewer and you can locate it under the Integration Services folder of the Microsoft SQL Server 2008 folder of the start menu.

Once we have launched the Data Profile Viewer you will be greeted with the screen in the below screenshot.

Now we need to open the file that we created with our SSIS package. Click on the "Open" button in the top left corner and navigate to the output file. Once you have loaded the file you will see the following in your Data Profile Viewer.

All we now need to do is to click on the different profiles that we have requested to start and analyse our data. Let's click on the Candidate Key Profile.

In the screenshot you can see that there are three fields with a Key Strength of 100% that could serve as a primary key on the table. Next click on the Column Length Distribution Profile. After you have selected the Length Distribution Profile click on the Color column.

This profile provides us with the maximum and minimum lengths of the specific columns in our table. This is definitely very valuable if you want to get your field lengths as narrow as possible. You also have the Length Distribution window that shows you the percentage of rows that make up the different lengths of values found in the column.

The last profile I want to have a look at is the Column Null Ratio Profile.

This profile shows you the null count per column and also gives you the null percentage. From the above screenshot we can see that in the Color column we have 248 null values and they make up 49.2063% of the total values in the column.

Conclusion

I am sure that you will agree with me when I say that the Data Profiling capabilities is definitely going to add a great deal of value to SQL Server 2008.

One thing that I think definitely will be used a lot is the ability to consume the XML output form a Data Profiling Task using a script task or XML Task and base decisions upon the data.This will for example allow you to notify an administrator when you find anomalies in the data before loading the data into you data warehouse.

We do however have some limitations one being that we cannot develop additional profiles as we need them, however I am sure that the profiles that come with the task will already provide a great deal of functionality.