Adatis BI Blogs

Data Quality Services (RC0) - Cleanse and Train the KB

Victor Mendes — Mon, 09 Jan 2012 16:10:00 GMT

This post will show a new KB (Knowledge Base) being trained using the new feature via domain management which allows the KB to import the knowledge gained from a cleansing project. In the previous release, CTP3, the KB was trained using Knowledge Discovery from either a table or excel file. This could also have been done interactively via domain management, but not directly from a cleansed project.

In this example we will retrain the new KB by importing knowledge learnt from two cleansed samples of data. The outputs of the cleansed projects will be imported into the KB. Before retraining our KB we will need to tweak one of the cleansing configuration settings. This will retrain our new KB faster.

New Knowledge Base

If you have not created a KB before a step by step guide can be found here.

The new KB was called UK Counties. A single domain value was used and it was called UK County (Full Name).

Using the Knowledge Discovery feature we first trained our empty KB with a unique list of UK Counties. The Domain Management feature could also have been used by entering domain values or importing them from an excel file.

Training the KB doesn't stop there, further training is required to expand the KB knowledge. Interactively we could add new domain values or synonyms to the already existing values such as Bucks for Buckinghamshire if we knew this was a potential synonym. For this example we will be using the knowledge gained from our cleansing project to retrain our KB.

Below is a screen shot of some of the domain values prior to retraining and being published to the DQS server.

Configuring Cleansing Projects

During cleansing a computer-assisted process will analyse how the source data conforms to the knowledge in the KB and a confidence level is determined. There are two confidence level thresholds: suggestions and auto corrections. Suggestions can be approved or rejected. Auto corrections are already approved, but it is possible to reject them as well.

These thresholds can be set in the general settings for a cleansing project from the DQS configuration area.

By default 0.6 (60%) and 0.8 (80%) are set respectively for suggestions and auto corrections.

As the KB is still young we need to retrain it with some samples of our real data and therefore make more suggestions. Hence we will reduce the confidence level percentage for suggestions down to 0.4 (40%).

N.B. This can go the other way where too many suggestions are given or a source value could be mapped to a completely incorrect domain value. As the knowledge of KB increases the confidence level for suggestions should also be increased to isolate new values and minimise interactive cleaning.

Training the KB

There are a number of ways to train the KB

Knowledge Discovery, or
Domain Management by either
1. Interactively entering or editing domain values,
2. Importing domain values from an excel file, or
3. Using the completed output of a cleansing project.

The final option is the new feature added to RC0 which makes managing knowledge within the KB simpler.

Let’s look at our first sample of data.

You will notice a number of data quality issues such as postcode and county, town and county or a short county name. Our objective is to clean this data to the County Full Name domain value in the KB.

If you have not created a data quality project before a step by step guide can be found here.

We will skip to the end of our cleansing project and analyse the results.

On successful completion we can see from the results that 0 records were corrected and 7 records have suggested domain values. We can now perform an interactive cleanse by which we approve or reject the suggestions made. Where the data confidence level has not been met for suggestions of 40% or auto corrections of 80% it will be considered as a new value. For this particular example we should not be expecting new values as we have a definitive list of counties and therefore we will need to add corrected values manually.

Let’s start with the suggested values. All are correct and therefore only need to be approved. We approve all values by clicking the check box under the Approve column or we can click the Approve All icon . All suggested values will move into the Corrected tab area.

Now let’s review the new tab below. Three new values in the source data have been found, the analysis could not confidently associate any of the three values with a confidence level greater than or equal to 40% and therefore has presented them as new values.

Looking at the values we know the county is Middlesex as is in the KB, hence we manually enter this value into the Correct to column. Now we can approve each value or we can click the Approve All icon.

All values have now moved to the corrected tab.

Our data cleanse results are now ready for final review before finishing the project. Finishing a project no longer allows the project to be re-run for cleansing purposes as the knowledge gained would be lost, however you will be able to interactively clean to alter the final results. It’s these results and knowledge that is imported back into the KB, but the project must be marked as finished.

It is the CountyName_Source that we want to feed back to the KB as synonyms for the domain values found or modified in the CountyName_Output.

To feed this new acquired knowledge we open our KB via Domain Management and can now use the new feature Import project values.

We select the finished cleansing project and uncheck Add values from New Tab.

Only the new knowledge from the project has been added to the KB. The Show Only New check box is checked automatically.

If we uncheck the Show Only New check box and scroll down to London and Middlesex you will see the synonyms for these counties have been added to the KB.

We now publish the KB to the DQS server and are ready to analyse another data sample.

As above we create a new cleansing project and analyse the data. The newly found knowledge from the previous cleansing project has corrected five values with 4 values suggested, leaving one value as new.

If we view the reason column for the corrected tab, four of the values were associated with the synonyms of domain values within the KB from the knowledge learned. 'London.' was cleansed and had a confidence greater than or equal to 80% and therefore auto corrected.

In the suggested tab the correct to values are all correct and only need approving.

The new value failed the confidence level of 40%, however it could have been found and a suggestion made if the threshold was reduced further. This is not a problem as the correct value was entered and approved.

Finally we review our results and finish the project.

We now feed this new knowledge back into the KB via Domain Management.

The new synonyms will now be added to the existing KB domain values.

This has been a simple example on how to train and retrain a new KB. By having a definitive list of counties the need to analyse our data prior to creating the KB is somewhat reduced.

When building a KB it is important to acquire knowledge from samples of source data using any of the features available. From this example the output of a cleansing project provides a simpler method where the confidence level for suggested values is much lower. Once the KB contains sufficient knowledge for cleansing the confidence level can be increased and larger sets of data can be cleansed.

DQS is a great tool for the power user to maintain content with little input from IT. In posts to follow I will also review Matching projects and the SSIS DQS component.

Master Data Services in SQL Server 2012 RC0

Jeremy Kashel — Wed, 30 Nov 2011 13:31:34 GMT

There’s been a whole host of changes to MDS in the SQL Server 2012 RC (Release Candidate) 0 that came out the other week. This blog post gives an overview of the changes, before diving into detail on a few of them. At a high level, the following changes have been made to MDS:

Improved Master Data Manager front screen UI and navigation paths
Collections interface updated to Silverlight
Improved Excel user interface & functionality
Auto generation of entity code values, without using business rules
New deployment tool
SharePoint integration

Let’s take a look at each of these changes:

Improved Master Data Manager UI

Although the UI improvement (shown below) is good, the best thing about the Master Data Manager changes, in my opinion, is that clicking on the Explorer feature no longer takes you into the Model View, but instead takes you straight into your master data for your core entity (e.g. in a Customer model this entity would be Customer). I’m not sure if this would get a bit frustrating if you didn’t want the core entity, but then again everything seems very quick in RC0, so I don’t think it’s really going to matter.

There’s also a new button next to a domain-based attribute that apparently has been designed to help with Many-to-Many relationships. I can definitely see that working, but it’s useful to have anyway to jump to the member details for the domain based attribute that you are viewing, many-to-many or not:

Collections Interface Updated to Silverlight

The collections interface is now much slicker, getting the Silverlight makeover seen in other areas of Master Data Manager. Switching between collections, for example, which could take a while in R2, now happens very quickly, making collections far more useable. The screen shot below shows how you edit collection members by picking members from one of the entities and adding those over into the collection by clicking the Add button:

Collections now have the concept of a Weight, meaning that you can alter the weighting value associated with the collection members, which could be useful for reporting purposes. The idea is that you extract the collection members and the weight values in a subscription view. A Weight column is actually included in the 2008 R2 collection subscription views, but there was no front end to modify the weight value, which has of course now changed:

Improved Excel User Interface and Functionality

The MDS Excel Ribbon has been given a makeover, meaning that you now see the following in the ribbon:

It’s interesting to see that the Favourites section has been replaced with the concept of ‘queries’. The idea is that you can send a query file via email to another user, providing that user has the Excel add-in. When the user double clicks on the query file (extension *.mdsqx), Excel will open and make a connection to MDS, using the connection and filter information provided in the file. This will result in Excel opening, with the user prompted if they would like to connect to MDS:

It’s also good to see that a few of the domain-based attribute issues have been addressed, namely the display of domain-based attribute names when you filter an entity and also the display of the names and the codes together in the Excel sheet. Here’s a screen shot of the how the attribute names are now visible when filtering the Country domain-based attribute that exists in the Customer model:

Auto Generation of Entity Code Values

If you wanted the code to be auto-generated in 2008 R2, you had to use a business rule. You can still do that if you want, but the create entity admin screen has changed slightly to include an option to auto generate the code. This works slightly better than business rules in my opinion, at least as far as the Excel add-in is concerned, as the code is returned to the user immediately after publishing, whereas the business rules require you to do a refresh in Excel and of course need more development! Here’s a screenshot of the add entity screen:

New Deployment Tool

Deployment has been altered in SQL 2012, with the addition of a new deployment tool, plus the fact that subscription views now get deployed. It seems that the current R2 deployment method (in the Administration section of Master Data Manager) is still included but now will not deploy data. To do that you need to use MDSModelDeploy.exe, as explained here. As an example, here’s how you can deploy the sample Customer model using MDSModelDeploy.exe, for default installations:

MDSModelDeploy deploynew –package “C:\Program Files\Microsoft SQL Server\110\Master Data Services\Samples\customer_en.pkg” –model “Customer” –service “MDS1”

SharePoint Integration and Further Details

Master Data Manager now supports a set of parameters that allow the MDS UI to be displayed without the header, menu bar and padding area. This means that MDS can now be incorporated into SharePoint or other websites. For the details on this, as well as more details on the above points, take a look at the following Technet article.

Working with Images in Power View

Jeremy Kashel — Tue, 29 Nov 2011 13:04:31 GMT

Power View includes several ways to create visualisations with images, ranging from a simple table, to the scrollable tile visualisation. In this post I’m going to explain a bit about the tile visualisation, showing how it can be used in Power View, before showing a couple of tips for working with images in both PowerPivot and Analysis Services Tabular models.

Power View Tiles

The Power View Tiles visualisation allows you to display a scrollable series of images, which, when clicked on, will display data of your choosing. The report below is an example of this:

What’s nice about the View above is that the names of the products are automatically displayed underneath the images. This is a sensible place to display the product names, as it means the area below can focus on whatever product level data you want, in this case Revenue by Month for the selected product.

This works fine in the sample models (in my case the Tailspin Toys model that I think came with CTP3) but it wont work in vanilla PowerPivot / Tabular models unless you configure a few model settings. Trying to reproduce the report above without these settings will give the following tile visualisation, minus the product names:

PowerPivot Advanced Settings

To address this in PowerPivot, you first of all need to switch to Advanced Mode, which you can do by selecting this option from the File menu in PowerPivot:

This gives you the following advanced tab:

Clicking on the highlighted Table Behaviour button in the ribbon gives you the following Table Behaviour window:

Here you can “change the default behaviour for different visualisation types and default grouping behaviour in client tools for this table”. The following properties should be set in order to get the tiles working correctly:

Row Identifier – Specifies a column that only contains unique values, which allows the column to be used as an internal grouping key in client tools.
Default Label – Specifies which column gives a row level display name, e.g. the product name in a Product table. This is key, as when a row level image is used, this property specifies which value to display alongside the image in tiles and other image-based visualisation.
Default Image – Specifies which column contains images representing the row level data, e.g. pictures of products.

The full details for all these properties can be found here.

Tabular Models

Tabular Models contain the same properties, which can be edited when you open a tabular project in the new SQL Server Data Tools. The image below shows how the properties have been configured for the column in my model called Image:

Back to Power View

If we now build a tile visualisation using the image column, we will see that we get the names of the products returned along with the image. It’s also worth noting that the Image and Product Name columns have now have an icon next to them, indicating that the field exhibits “row level” behaviour:

Finally, for some examples of what’s new in Power View SQL 2012 RC0 take a look at this post on the Reporting Services Team Blog.

SQL Server 2012 Running Totals

Martyn Bullerwell — Wed, 16 Nov 2011 15:48:19 GMT

SQL server has sometimes come under fire with the Oracle vs SQL Server debate because of the lack of some of the more advanced (and less used) functionality that is outlined in the SQL ANSI standards. An example of this is Window Functions, which became an ANSI standard under the ANSI:2003 revision. A Window function is an aggregate function that can be applied to a subset of a full set of data. Now this can be achieved in current versions of SQL Server (2008 R2 and its predecessors), but not using Window Functions and therefore has a performance implication. There are about 3 or 4 approaches to achieving a running total in SQL Server prior to the 2012 version, however none particularly elegant.

For my following example I will outline how to perform a running total using AdvertureWorks2008 sample data. I will remind us of 1 of the method's (probably the most common) of how we used to do running totals prior to SQL Server 2012, and then show how to do the same running total using a SQL Server 2012 Window Function. To set the scene, we will be looking for a running total of Line Items for a given Order. This may be kind of query you may wish to write to generate an invoice with a running total on it.

To begin with lets look at the more traditional query:

SELECT
        A.SalesOrderID,
        A.SalesOrderDetailID,
        A.LineTotal,
        SUM(B.LineTotal)
FROM
Sales.SalesOrderDetail AS A
    CROSS JOIN Sales.SalesOrderDetail AS B
WHERE
    B.SalesOrderDetailID <= A.SalesOrderDetailID
        AND
    A.SalesOrderID = B.SalesOrderID
GROUP BY
    A.SalesOrderID,
    A.SalesOrderDetailID,
    A.LineTotal
ORDER BY
    A.SalesOrderID,
    A.SalesOrderDetailID,
    A.LineTotal

This simply works by self joining up to certain point, so works fine for any data that can be ordered easily, as running totals usually are this method usually suffices.

The following query uses the new Window function in SQL Server 2012:

SELECT
    SalesOrderID,
    SalesOrderDetailID,
    LineTotal,
    SUM(LineTotal)
        OVER     (PARTITION BY
                SalesOrderID
            ORDER BY
                SalesOrderDetailID) AS OrderRunningTotal
FROM
    Sales.SalesOrderDetail
ORDER BY
    SalesOrderID,
    SalesOrderDetailID,
    LineTotal

You will instantly notice that the second query is far more elegant, and more simplistic to understand. In short, in this query, we are telling SQL server to Sum the “Line Total” over an ordered partition of the data. Both queries return the same result, however it becomes interesting when we look at the execution plans, relative to each other. This is shown below (apologies for the size of these):

What is important to note that the second (bottom) plan is smaller, and simpler. the other, very significant point is that the Query Cost (relative to batch) is a whopping 97% for the old method of running totals, meaning that the new Windowing Functions are far more efficient.

In summary, I believe that this is an example of where SQL server is becoming a firm competitor to some of its perceived rivals, its one of the few points that can be raised as a valid point in the argument of SQL Server vs Oracle, but not any more!

SQL Server 2012 : Columnstore Index in action

sachatomey — Wed, 09 Nov 2011 16:17:40 GMT

One of the new SQL Server 2012 data warehouse features is the Columnstore index. It stores data by columns instead of by rows, similar to a column-oriented DBMS like the Vertica Analytic Database and claims to increase query performance by hundreds to thousands of times.

The issue with indexes in a data warehouse environment is the number and broad range of questions that the warehouse may have to answer meaning you either have to introduce a large number of large indexes (that in many cases results in a larger set of indexes than actual data), plump for a costly spindle-rich hardware infrastructure, or you opt for a balanced hardware and software solution such as a Microsoft SQL Server 2008 R2 Fast Track Data Warehouse or a HP Business Data Warehouse Appliance where the approach is ‘index-light’ and you rely on the combination of high throughput and performance power to reduce the dependency on the traditional index.

The Columnstore index is different in that, when applied correctly, a broad range of questions can benefit from a single Columnstore index, the index is compressed (using the same Vertipaq technology that PowerPivot and Tabular based Analysis Services share) reducing the effort required on the expensive and slow disk subsystem and increasing the effort of the fast and lower cost memory/processor combination.

In order to test the claims of the Columnstore index I’ve performed some testing on a Hyper-V instance of SQL Server 2012 “Denali” CTP3 using a blown up version of the AdventureWorksDWDenali sample database. I’ve increased the FactResellerSales table from approximately 61,000 records to approximately 15.5 million records and removed all existing indexes to give me a simple, but reasonably large ‘heap’.

Heap

With a clear cache, run the following simple aggregation:

SELECT
    SalesTerritoryKey
    ,SUM(SalesAmount) AS SalesAmount
FROM
   [AdventureWorksDWDenali].[dbo].[FactResellerSales]
GROUP BY
    SalesTerritoryKey
ORDER BY
    SalesTerritoryKey

Table 'FactResellerSales'. Scan count 5, logical reads 457665, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 7641 ms, elapsed time = 43718 ms

Non-Clustered Index

Before jumping straight in with a columnstore index, let’s review performance using a traditional index. I tried a variety of combinations, the fastest I could get this query to go was to simply add the following:

CREATE NONCLUSTERED INDEX [IX_SalesTerritoryKey] ON [dbo].[FactResellerSales]
(
   [SalesTerritoryKey] ASC
)
INCLUDE ([SalesAmount]) WITH
(
    PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, DATA_COMPRESSION = PAGE
) ON [PRIMARY]
GO

Notice I have compressed the index using page compression, this reduced the number of pages my data consumed significantly. The IO stats when I re-ran the same query (on a clear cache) looked like this:

Table 'FactResellerSales'. Scan count 5, logical reads 26928, physical reads 0, read-ahead reads 26816, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6170 ms, elapsed time = 5201 ms.

Much better! Approximately 6% of the original logical reads were required, resulting in a query response time of just over 5 seconds. Remember though, this new index will really only answer this specific question. If we change the query, performance is likely to fall off the cliff and revert back to the table scan.

Incidentally, adopting an index-light ([no index]) approach and simply compressing (and reloading to remove fragmentation) the underlying table itself, performance was only nominally slower than the indexed table with the added advantage of being able to perform for a large number of different queries. (Effectively speeding up the table scan. Partitioning the table can help with this approach too.)

Columnstore Index

Okay, time to bring out the columnstore. The recommendation is to add all columns into the columnstore index (Columnstore indexes do not support ‘include’ columns), practically there may be a few cases where you do exclude some columns. Meta data, or system columns that are unlikely to be used in true analysis are good candidates to leave out of the columnstore. However, in this instance, I am including all columns:

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_Columnstore] ON [dbo].[FactResellerSales]
(
    [ProductKey],
    [OrderDateKey],
    [DueDateKey],
    [ShipDateKey],
    [ResellerKey],
    [EmployeeKey],
    [PromotionKey],
    [CurrencyKey],
    [SalesTerritoryKey],
    [SalesOrderNumber],
    [SalesOrderLineNumber],
    [RevisionNumber],
    [OrderQuantity],
    [UnitPrice],
    [ExtendedAmount],
    [UnitPriceDiscountPct],
    [DiscountAmount],
    [ProductStandardCost],
    [TotalProductCost],
    [SalesAmount],
    [TaxAmt],
    [Freight],
    [CarrierTrackingNumber],
    [CustomerPONumber],
    [OrderDate],
    [DueDate],
    [ShipDate]
)WITH (DROP_EXISTING = OFF) ON [PRIMARY]

Now when I run the query on a clear cache:

Table 'FactResellerSales_V2'. Scan count 4, logical reads 2207, physical reads 18, read-ahead reads 3988, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 235 ms, elapsed time = 327 ms.

I think the figures speak for themselves ! Sub-second response and because all columns are part of the index, a broad range of questions can be satisfied by this single index.

Storage

The traditional (compressed) non-clustered index takes up around 208 MB whereas the Columnstore Index comes in a little less at 194 MB so speed and storage efficiency, further compounded when you take into account the potential additional indexes the warehouse may require.

So, the downsides? Columnstore indexes render the table read-only. In order to to update the table you either need to drop and re-create the index or employ a partition switching approach. The other notable disadvantage, consistently witnessed during my tests, is the columnstore index takes longer to build. The traditional non-clustered index took approximately 21 seconds to build whereas the columnstore took approximately 1 minute 49 seconds. Remember though, you only need one columnstore index to satisfy many queries so that’s potentially not a fair comparison.

Troubleshooting

If you don’t notice a huge difference between a table scan and a Columnstore Index Scan, check the Actual Execution Mode of the Columnstore Index Scan. This should be set to Batch, not Row.

If the Actual Execution Mode is reporting Row then your query cannot run in parallel:

- Ensure, if running via Hyper-V, you have assigned more than one processor to the image.
- Ensure the Server Property ‘Max Degee of Parallelism’ is not set to 1.

Summary

In summary, for warehousing workloads, a columnstore index is a great addition to the database engine with significant performance improvements even on reasonably small data sets. It will re-define the ‘index-light’ approach that the SQL Server Fast Track Data Warehouse methodology champions and help simplify warehouse based performance tuning activities. Will it work in every scenario? I very much doubt it, but it’s a good place to start until we get to experience it live in the field.

Managing SSAS Named Sets with Master Data Services Collections

Jeremy Kashel — Mon, 07 Nov 2011 17:04:00 GMT

Master Data Services Collections are probably not the first feature that come to mind when you think of the MDS product. The hierarchies and member grids tend to be the core functionality, but as this post will hopefully show, MDS Collections are useful also.

Collections are essentially managed lists of members that can come from multiple different MDS explicit hierarchies, or also from another collection. The idea is that this “subset of members” can be maintained in MDS by a business user and then fed to external applications for reporting or other purposes.

Analysis Services Named Sets

One example of how collections can be used is to maintain Analysis Services named sets. Some named sets, such as a Top 50 Customers, don't require any maintenance, as it’s just the top 50 of all customers, based on a particular measure. On the other hand, sometimes named sets can be static lists, e.g. a list of core products that the user wants to see for reporting.

In the latter example, if a user wants the definition of a named set to change, they have to get IT to change the MDX script. MDS collections can help by allowing the user to control the named set definition, reducing the burden on IT.

Example Scenario

Here’s an example of how this is done. First of all, the end game is that I have a named set in Analysis Services that is currently just for 3 products. Therefore, a user can easily drop this set into an Excel report to get quick access to the products that are important to them:

So the challenge is that we need to find some way of extending this named set without doing it manually. This is where MDS starts to come in, so using the sample Product model that comes with MDS, I’ve created a new collection called Favourite Products, as shown in the image below:

If I go and edit the Favourite Products collection in MDS, then I can drag and drop more products into this collection, or remove some existing members. In this case, I’ve chosen to add two new products:

C#, AMO and the MDS API

So the named set represents the target that we need to get to, whereas the MDS collection that’s shown is the source. To get the contents of the MDS collection to update the named set, one way of doing it is to use the MDS API to pick up the collection members, and then to use AMO in order to write the named set into the MDX script. I’m just doing this in a C# windows application, but you could do it via a batch process, such as SSIS. For this post I’m just going to show the code, so here goes:

This post is already starting to feel too long so I’m not going to show the basics of the MDS API. For that, take a look at a good posting by the MDS Team blog here. Also, as anyone who knows me will no doubt agree, I don’t tend to get involved in doing C#, so don’t consider this to be production ready! It should give you an idea of the basics though. Anyway, assuming that we’re now connected to the MDS Web Service, I’ve created the following method that will return the members from the collection:

private HierarchyMembers ObtainHierarchyMembers(string entityId, string hierarchyId, string modelId, string versionId)
{
    HierarchyMembersGetRequest request = new HierarchyMembersGetRequest();
    HierarchyMembersGetResponse response = new HierarchyMembersGetResponse();
    request.HierarchyMembersGetCriteria = new HierarchyMembersGetCriteria();

    //State that our hierarhcy type is a collection and that we want collection members
    request.HierarchyMembersGetCriteria.HierarchyType = HierarchyType.Collection;
    //Pass in the key search criteria to identify the correct collection in MDS
    request.HierarchyMembersGetCriteria.ParentEntityId = new Identifier { Name = entityId };
    request.HierarchyMembersGetCriteria.HierarchyId = new Identifier { Name = hierarchyId };
    request.HierarchyMembersGetCriteria.ModelId = new Identifier { Name = modelId };
    request.HierarchyMembersGetCriteria.VersionId = new Identifier { Name = versionId };

    request.HierarchyMembersGetCriteria.RowLimit = 50;
    request.International = new International();
    OperationResult result = new OperationResult();
    //Return the hierarchy members from the service
    return service.HierarchyMembersGet(request.International, request.HierarchyMembersGetCriteria, out result);
}

Before we use the above method, we need to connect to SSAS using AMO. That can be done quite easily with the following code:

Server server = new Server();
string connection = "Data Source=.;Catalog=Adventure Works DW 2008R2;";
server.Connect(connection);

After we’ve done all the usual error handling associated with connecting to a database, we need to pick up the SSAS database and cube:

Database database = server.Databases["Adventure Works DW 2008R2"];
Cube cube = database.Cubes["Adventure Works"];

Now we’re ready to call our ObtainHierarchyMembers method, before looping through it to build the named set:

StringBuilder mdx = new StringBuilder("\n//Auto generated named set at " + DateTime.Now.ToString() + 
    "\nCREATE SET CurrentCube.[Favourite Products] AS {");
int count = 1;
//Loop through the collection to build the mdx
foreach (ParentChild pc in hm.Members)
{
    //Add the members to the MDX string
    //This references the member by name
    //It would be possible to reference by member key, but would require more work
    mdx.Append("[Product].[Product].[" + pc.Child.Name + "]");
    if (count < hm.Members.Count())
    {
        mdx.Append(", ");
    }
    count++;
}
mdx.Append("};");

Now we need to insert the named set in the correct place within the existing MDX script, bearing in mind it could already exist:

string currentScript = cube.MdxScripts[0].Commands[0].Text;
//Find the correct place to insert the named set within the MDX script:
int start = currentScript.IndexOf("\n//Auto generated named set at");
int end = 0;
StringBuilder newScript = new StringBuilder(currentScript);
if (start != -1)
{
    end = currentScript.IndexOf(";", start);
    //If the named set already exists, remove it
    newScript.Remove(start, end - start + 1);
}
else
{
    start = currentScript.Length;
}
//Insert the named set in the correct place
newScript.Insert(start, mdx.ToString());
//Update the cube's MDX script
cube.MdxScripts[0].Commands[0].Text = newScript.ToString();

Finally we just need to update the cube in order to write the MDX back to the cube:

//Call the update methods to update the cube
cube.MdxScripts[0].Update();
cube.Update();

User Reports

Now that the cube has been updated, if the Excel report is refreshed, then the two new products will appear in the list:

Summary

Controlling SSAS named sets like this won’t be for everybody. I’ve certainly worked in a few clients where strict process has to be followed to update this sort of thing, but I can think of other companies that I know where this would be really useful.

Managing Analysis Services named sets is just one use for collections. Another example might be managing default multi-select parameters for SSRS reports. As collections are just lists of members that can be extracted easily, what you do with them is up to you!

SQL Server 2012 Licensing

sachatomey — Thu, 03 Nov 2011 16:48:00 GMT

Today saw the announcement of how SQL Server 2012 will be carved up and licensed, and it's changed quite a bit. There are three key changes:

1) There's a new Business Intelligence Edition that sits between Standard and Enterprise
2) No more processor licensing. There's a move to Core based licensing instead (with a minimum cost of 4 cores per server)
3) Enterprise is only available on the Core licensing model (Unless upgrading through Software Assurance *)

Enterprise, as you would expect, has all the functionality SQL Server 2012 has to offer.

The Business Intelligence edition strips away
- Advanced Security (Advanced auditing, transparent data encryption)
- Data Warehousing (ColumnStore, compression, partitioning)
and provides a cut-down, basic (as opposed to advanced) level of High Availability (AlwaysOn).

In addition, the Standard Edition removes
- Enterprise data management (Data Quality Services, Master Data Services),
- Self-Service Business Intelligence (Power View, PowerPivot for SPS)
- Corporate Business Intelligence (Semantic model, advanced analytics)

If you are utilising 4 core processors, licence costs for Standard ($1,793 per core, or $898 per Server + $209 per CAL) and Enterprise ($6,874 per core) remain similar (ish). However, you will be stung if you have more cores. The Business Intelligence edition is only available via a Server + CAL licence model and it's apparent that Microsoft are placing a big bet on MDS/DQS, Power View, PowerPivot for SharePoint and BISM as the licence for the Business Intelligence edition is $8,592 per server, plus $209 per CAL, that's nearly 10x more per server than Standard Edition !

For the complete low-down check out these links:

Editions Overview:
http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-editions.aspx

Licensing Overview:
http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-licensing.aspx

Licence Detail (including costs):
http://download.microsoft.com/download/D/A/D/DADBE8BD-D5C7-4417-9527-5E9A717D8E84/SQLServer2012_Licensing_Datasheet_Nov2011.docx

* If you are currently running Enterprise as a Server + CAL and you upgrade to SQL 2012 through Software Assurance, you can keep Server + CAL model, providing you don’t exceed 20 cores.

Master Data Services Training in the UK

Jeremy Kashel — Tue, 01 Nov 2011 23:33:00 GMT

This is just a quick post to announce a range of SQL Server training courses, organised (and in some cases delivered) by Chris Webb. To start off there’s a SQL Server course delivered by Christian Bolton in December, followed by an Analysis Services course delivered by Chris in February. I’ll be delivering a Master Data Services course in February, before Chris delivers an MDX course in March.

The details for all the courses are:

SQL Server Internals and Troubleshooting Workshop - Christian Bolton – 6th – 7th December 2011

The Advanced Troubleshooting Workshop for SQL Server 2005, 2008 and R2 provides attendees with SQL Server internals knowledge, practical troubleshooting skills and a proven methodical approach to problem solving. The workshop will enable attendees to tackle complex SQL Server problems with confidence.

Full details and registration here.

Real World Cube Design and Performance Tuning with Analysis Services – Chris Webb – February 2012

A two day course that takes real world experience in showing you how to build a best practice Analysis Services cube, covering design issues such as data warehouse design and complex cube modelling. Day two then covers performance optimisation for Analysis Services, including MDX optimisation and cube processing.

Full details and registration here.

Introduction to Master Data Services with Jeremy Kashel – February 2012

An end to end look inside Master Data Services, this full day course will begin with a synopsis of Master Data Management before moving on to an overview of Microsoft SQL Server 2008 R2 Master Data Services (MDS). The remainder of the course will cover the major MDS topics, such as modelling and business rules, which will include a number of practical exercises.

More details and registration for here.

Introduction to MDX with Chris Webb – March 2012

The Introduction to MDX course aims to take you from the point of being a complete beginner with no previous knowledge of MDX up to the point where you can write 90% of the MDX calculations and queries you’ll ever need to write. The three day course covers the basics, such as sets, tuples, members to more advanced concepts such as scoped assignments and performance tuning.

Full details and registration here.

HP Business Decision Appliance–PowerPivot in a box!

Calvin Ferns — Thu, 27 Oct 2011 08:32:31 GMT

Further to my blog post about SQL server appliances Microsoft and HP also offer the very exciting Business Decision Appliance. This contains a preconfigured environment with:

Microsoft Windows Server 2008 R2 Enterprise Edition

Microsoft SQL Server 2008 R2 Enterprise Edition with PowerPivot integration for SharePoint

Microsoft SharePoint 2010 Enterprise Edition Prerequisites for SharePoint and PowerPivot

Appliance Administration Console

Appliance-specific SharePoint Home Page

Up to 80 Concurrent Users

The aim of this appliance is to provide a safe and scalable environment for business users to quickly put a PowerPivot environment in place. This is isolated from existing systems and can be implemented by a business department with very limited IT involvement. Its isolated nature is important as many organisations don’t run SharePoint 2010 throughout their enterprise. PowerPivot authors also need Excel 2010 but once reports are built and deployed they can be shared throughout the business through SharePoint using any web browser. Anyone who has already tried to set up an integrated SharePoint and PowerPivot environment will know that the installation is not simple. This appliance takes that pain away with a one click installation from first start up that can have you up and running in under an hour.

The key point is that business users love PowerPivot and its ability to quickly use their data to answer any question. They can respond dynamically, collaborate and share insights throughout the organisation. Importantly PowerPivot allows the creation of reports that look great and are very fast. All this is done from within the familiar Excel interface and requiring little training to get started. Any team of business analysts would likely have a massive boost in productivity from the installation of the BDA in their department. IT can then monitor those reports that are heavily used and decide whether they need making more robust through transition to enterprise software such as Analysis Services. PowerPivot use is at its easiest in an environment where there is a clean data warehouse but where the business aren't happy with the speed or responsiveness to change of their current front end. In an environment with more disparate and dirty data the end users need to be more SQL and data modelling skilled, but the payback can be even greater.

I see massive opportunity in this appliance for any team of data analysts to be able to deliver massive value to their business right now.

More Info on the BDA

MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook - Book Review

Jeremy Kashel — Thu, 13 Oct 2011 16:51:00 GMT

As you may have already seen, Packt have released a new MDX book, namely MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli.

The book is written as cookbook, meaning that you can choose the individual ‘recipes’ that apply to whatever problem you’re trying to solve. Each recipe starts off with a section called ‘Getting Ready’, essentially giving you the base query to use, before moving onto ‘How to do it', which covers the main instructions for the recipe. There are then further sections, which explain how the example works and also suggest other functions/concepts that you may want to consider. This sort of cookbook style makes it really easy to follow, each recipe is displayed very clearly in the book.

A wide range of MDX problems are covered, from time calculations to context-aware calculations. Not every piece of the MDX functionality is covered, which is to be expected, given the size of the book. It also doesn’t cover the usual introduction to members/sets etc that MDX books tend to cover, but it’s clearly stated that having a working knowledge of MDX is a pre-requisite for the book.

I found the copy that I’ve been reading in the Adatis office, but I really do like it, so I’ll definitely be ordering my own copy!

SQL Server can scale

Calvin Ferns — Wed, 21 Sep 2011 17:52:17 GMT

If you work in large enterprises you often come across the attitude that Microsoft SQL Server can’t scale. People therefore turn to vendors such as Oracle and Teradata for solutions at very high cost. This attitude often comes from in-house experience of a growing SQL database which hits something between 5 and 10TB and starts slowing down and becoming difficult to work with. These databases are often run on multi-instance servers with shared SAN access and little thought to data fragmentation. Its no wonder that their environment has problems!

Microsoft Strategy

I was recently invited to a Microsoft partners event which aims to challenge these attitudes and give partners better information to discuss large scale SQL implementations. Their strategy is to use partnership with HP to give SQL server a balanced hardware configuration to run on. This will solve the major bottleneck which gives SQL Server the performance problems people perceive are with the software. They have created a range of solutions together to allow SQL server to scale effectively to meet any need. The first of these solutions was released a couple of years ago and there are more in the pipeline. There are real world case studies available from Microsoft detailing the success of this strategy for many early adopters. These solutions are targeted at the workloads they need to run and are detailed below.

Warehousing

< 5TB – HP Business Data Warehouse Appliance

20TB – 80TB – Fast Track Data Warehouse

126TB – 500TB – HP Enterprise Data Warehouse Appliance

Applications/ Consolidation/Private Cloud

100VM’s- 10,000VM’s

OLTP

On the way shortly - Will scale to the worlds largest OLTP Implementations

These solutions are all positioned such that they are going to be significantly cheaper than almost any other vendor while providing equal or better performance. The appliances also provide excellent improvements in time to market, drastically reducing required configuration time. I would also recommend the 5TB box to anyone building a data warehouse of any size. This give you the confidence that you are buying a competitively priced server which is optimised for a SQL workload and you are not going to hit any hardware bottlenecks which you might come across on a shared or custom build server. The evolution of the Microsoft appliance and fast-track structure has also produced predictable benchmarks. You can choose the performance you need by picking the right server and if you follow the best practices you can be sure that you will achieve the throughput and response time needed.

Oracle’s Exadata platform claims to be able to manage any workload at any scale. What they don’t specify in the sales briefs is the amount of configuration required to match the platform to the workload in your environment. This means you need to invest in a lot of time for your Oracle DBA’s to tune and configure the hardware correctly. It is then not a comparable product to a SQL server installed on a spare bit of hardware which has very little time invested in it.

With a balanced hardware configuration and best practice ETL Microsoft SQL Server can meet any scale requirement asked of it. So next time you come across the “SQL can’t scale” attitude don’t be afraid to let people know that they are out of date and SQL will be able to meet their requirement at a far lower cost than the competitors.

(There is another appliance out for self service BI but it deserves a post all of its own - BDA )

Master Data Services Kindle Contest

Jeremy Kashel — Tue, 13 Sep 2011 12:55:09 GMT

This is a quick blog post to announce that Adatis are running a contest in conjunction with Packt around Master Data Services. The winner will receive an Amazon Kindle pre-loaded with our Microsoft SQL Server 2008 R2 Master Data Services book.

In order to enter, all you need to do is follow @AdatisBI and copy and paste our eBook on Kindle message and retweet it using your twitter account. The message is:

RT & Follow @AdatisBI for a chance to WIN the #Adatis MDS book pre-loaded on a Kindle ! (http://bit.ly/roogTd)

Please mark your retweet with the hashtag (#), so that we can find and consider your entry. Only one entry per person will be accepted.

You can find out the full details of how to enter, as well as terms and conditions, on the following page.

Good luck!

Master Data Services Excel Add-in

Jeremy Kashel — Thu, 25 Aug 2011 08:24:50 GMT

Master Data Services in SQL Server Denali now includes an Excel add-in that allows MDS users to add and update data that exists within Master Data Services. For those of you that haven’t had a chance to download the latest CTP, this post gives an overview of what’s possible in the Excel Add-in.

Excel Add-in Overview

Once you install the Excel add-in (available here for download) you will see an additional toolbar in the ribbon, as shown below:

At a high level, the following functionality is available within the MDS add-in:

Retrieve members from a master data entity
Add or Update members (including annotations) and publish the changes back to MDS
Create entities in MDS if you have sufficient permission
Run the business rules

I don’t want this post to go on for ever, so today I’m going to focus on retrieving and updating members.

Retrieving MDS Entity Members

The Excel Add-in comes with a sidebar called the Explorer that can be used to connect to an MDS model and retrieve data from an entity within the model. What I quite like is that there is a filter button that allows a user to specify how to filter the data before loading it. In the example below, I’ve connected to the sample customer model, and have filtered the customer entity to only show customers of type 1 and customers from Seattle:

On the same filtering window its possible to choose the attributes to display. Therefore in the above example, by clicking the ‘Load Data’ button, a filtered list of customer members will be shown:

Editing Members

Once the members have been retrieved, editing is just a simple case of typing into the field that you want to change. In the example below I’ve chosen to change the names of two of the customers, which has caused the cells to be highlighted, informing me of the changes that I’ve made:

Domain-based attributes are also picked up nicely by Excel. Not that I imagine it would make too much sense, but it’s possible to change the Sales District North Western US (WUSSL) to Central US (CEUS), for example. Excel handles this by rendering a drop down for this domain attribute:

As it is in Master Data Manager, within Excel it’s also possible to display a domain attribute’s name.

Regardless of the type of attribute that gets changed, the changes will just remain in the Excel sheet until I click the publish button:

Clicking the the publish button will result in being prompted to make an optional annotation for each of the changes:

The annotations, as well as the member updates, will be committed to the Master Data Services database. The end result is that the transactions can be viewed as per 2008 R2 in Master Data Manager, or by right clicking in Excel and choosing ‘View Transactions’.

Summary

The Excel add-in doesn’t contain all the functionality available within Master Data Manager, but provides an interface that clearly Excel-based data stewards will be very comfortable with. It’s also much easier to do bulk updates in the Excel front-end, when compared to the web front-end.

That’s about if for now. There’s more to the Excel add-in, which I’m aiming to cover at SQLBits 9, plus Master Data Manager has been given a complete overhaul in Denali – hopefully I’ll find the time to cover that soon…

Master Data Services Training

Jeremy Kashel — Mon, 22 Aug 2011 08:49:26 GMT

As I’m sure you’ve seen, registration for SQLBits 9 – Query Across the Mersey is now open.

This time around I’ll be running a deep-dive training day with Tim Kent on Master Data Services. This will be a full day of training, showing you how MDS can be used to manage the master data in your organisation. We’re going to start by giving an overview of Master Data Management, before moving on to covering the following MDS topics in detail:

Installing, configuring, and maintaining Master Data Services
Creating and using models
Version management
Business rules and workflow
Importing data into Master Data Services
Integrating with other systems
Security

You can register for the MDS Training day, or one of the other 10 training days, by using the following registration page.

Remember, the early bird discount expires at midnight on Friday this week!

Update – We are running another MDS course in February 2012. Click here for the details.

A Pattern To Load Data to Master Data Services via SSIS–Part 2

Neil Dobner — Thu, 18 Aug 2011 16:26:30 GMT

Introduction

This is the second part of a series of blog posts intended to show a way to load data in to MDS via SSIS. In part 1 we have loaded the staging tables with new members and attributes for several entities. In this blog post we are going to extend the SSIS package with tasks to move the data from the staging tables into the MDS Product model and validate these newly inserted members.

Completing The Solution

We need to move the data from the staging tables into the model. This is carried out by executing the MDS staging sweep process. To achieve this we need to add an Execute SQL Task to the control flow of our package. Rename the task – I’ve called mine ‘SQL – Sweep Stage’ and connect it up to the ‘DFL – Load Staging Tables’ task with a success constraint.

On the General tab set the connection to MasterDataServices and the SQL Statement as follows:

DECLARE @ModelName   NVARCHAR(50) = ? 
DECLARE @UserName    NVARCHAR(50) = ?
DECLARE @User_ID     INT 
DECLARE @Version_ID  INT

SET @User_ID =    (SELECT ID  
                   FROM  mdm.tblUser u 
                   WHERE u.UserName = @UserName ) 

SET @Version_ID = (SELECT MAX(ID)  
                   FROM mdm.viw_SYSTEM_SCHEMA_VERSION  
                   WHERE Model_Name = @ModelName)
                   

EXECUTE mdm.udpStagingSweep @User_ID, @Version_ID, 1

Then add the Parameter mapping as shown below:

That’s all there is to do to get our data into the model. However this process is asynchronous and before we can validate the model we need to know when the staging sweep has finished.

Add a For Loop Container task to the control flow of the package and connect it up to the ‘SQL – Sweep Stage’ task with a success constraint. Rename the task – I’ve called mine ‘FLC – Wait Until Batch Completes’. Add an Execute SQL Task inside the loop container task and rename it. Mine is called ‘SQL – Get Staging Batch Status’. Change the connection to MasterDataServices, change the ResultSet property to ‘Single row’ and then add the following SQL script to the SQLStatement property:

DECLARE @Version_ID INT

SET @Version_ID = (SELECT MAX(ID)  
                   FROM mdm.viw_SYSTEM_SCHEMA_VERSION  
                   WHERE Model_Name = ?)

SELECT TOP    1 Status_ID 
FROM          mdm.tblStgBatch 
WHERE         Version_ID = @Version_ID
ORDER BY      ID DESC

Add the parameter mapping as shown below:

And the Result Set as shown below:

Add three more parameters to the package as shown in the table below:

Next configure the For Loop Properties as shown in the table below:

The InitExpression value sets the @dtMDSLoopTimer to the current time plus the interval set in our @intMDSTimeout variable. The EvalExpression checks if the @strMDSBatchStatus is either not equal to 2 (Success) or the timeout has expired.

The For Loop Container can only succeed if the staging batch is successfully loaded.

Now we can validate the model so add an Execute SQL Task to the control flow and connect it to the ‘FLC – Wait Until Batch Completes’ task with a success constraint. Rename the task - mine is called ‘SQL – Validate Model’. Change the connection to MasterDataServices and the SQLStatement as follows:

DECLARE @ModelName    NVARCHAR(50) = ?
DECLARE @UserName     NVARCHAR(50) = ?
DECLARE @User_ID      INT 
DECLARE @Version_ID   INT
DECLARE @Model_id     INT 


SET @User_ID =        (SELECT ID  
                       FROM  mdm.tblUser u 
                       WHERE u.UserName = @UserName ) 

SET @Version_ID =     (SELECT MAX(ID)  
                       FROM mdm.viw_SYSTEM_SCHEMA_VERSION  
                       WHERE Model_Name = @ModelName)
                   
SET @Model_ID =       (SELECT Model_ID 
                       FROM mdm.viw_SYSTEM_SCHEMA_VERSION 
                       WHERE Model_Name = @ModelName) 


EXECUTE mdm.udpValidateModel @User_ID, @Model_ID, @Version_ID, 1

Set the parameter mapping as follows:

Almost done. Just to finish it off lets add a script task to the control flow and connect it to the ‘FLC – Wait Until Batch Completes’ task with a completion constraint. Change the Evaluation Operation of the constraint to ‘Expression and Constraint’ and set the Expression to ‘@strMDSBatchStatus != 2’. Edit the script and add the following line of code under // TODO: Add your code here:

Dts.Events.FireError(0, "SCR - Fire Error", "MDS Timeout Occurred", string.Empty, 0);

This task will fire an error event if the MDS staging batch does not complete successfully.

The finished package control flow should look similar to the following image:

Execute this package and then check the Product entity in MDS. It should look something like the following:

Looking at the other entities you will see that we have added members to three entities and validated all these new members.

Summary

Over the last two blog posts I have shown a way of automating the loading of data to Master Data Services via SSIS. This pattern can be used to cater for most of your loading requirements.

That’s it, the completed Integration Services project source code and MDS Model can be downloaded from here – (You will need to create a login first).

Your comments are very welcome.

Slides from Avon Information management talk

Calvin Ferns — Thu, 21 Jul 2011 11:42:43 GMT

Distributing the slides from my talk last night, it was well received and I think everyone was able to take something away.

Credit goes to Marco Russo and Alberto Ferrari for their data modelling ideas, visit www.sqlbi.com

http://dl.dropbox.com/u/25925341/Learning%27s%20from%20large%20scale%20dw%20project.pptx

Hopefully not my last talk as i enjoyed doing it. Look out for further Bath based events at:

http://avonim.wordpress.com/

All comments welcome

Calvin

A Pattern To Load Data to Master Data Services via SSIS

Neil Dobner — Thu, 14 Jul 2011 15:37:00 GMT

Part 1 Introduction

Loading new members to a MDS entity will be a common requirement in all MDS implementations. In these blog posts I am going to walk you through building an SSIS package that performs the following processes:

Load new members and attributes to several entities via staging tables
Validate the MDS model that contains the entities

In part one we will load the MDS staging tables ready to take our new members and attributes into our MDS model. For a thorough understanding of the staging process in MDS please see the Master Data Services Team blog post on Importing Data by Using the Staging Process.

A pre-requisite is to have the AdventureWorks2008R2 database samples installed on the same instance of SQL Server as Master Data Services.

In MDS I have created a model named ‘Product’ with an entity of the same name. The product entity has the following attributes which are set to the default type and length unless specified:

Name
Code
Model (Domain Attribute)
Culture (Domain Attribute)
Description (Text, 500)

We are going to load this entity with Product data from the AdventureWorks2008R2 database using a SSIS package.

In addition to this there are two further entities in the Product model:

Culture
Model

These entities have just the code and name attributes and are set to the default type and length.

The MDS model and Integration Services project source code can be downloaded from here – (You will need to create a login first).

Building The Solution

OK enough of the intro let’s get on and build the package.

Start a new Visual Studio Integration Services Project and save the default package to a more suitable name. I’ve called mine ‘LoadMDS.dtsx’.

Create the following connection managers as shown below remembering to replace the Server and MDS database names.

Rename the connection managers to ‘AdventureWorks’ and ‘MasterDataServices’ respectively.

Now we need to create some variables so go ahead and create the variables shown in the table below:

We are now ready to put our first task into our package. This task will optionally clear the staging tables of all successfully loaded members, attributes and relationships prior to loading, based on the value of the blnClearStage parameter.

Add an Execute SQL Task to the control flow of your package and rename it - I’ve called mine ‘SQL – Clear Staging Tables’.

On the General tab set the connection to MasterDataServices and the SQL Statement as follows:

DECLARE @ModelName NVARCHAR(50) = ? 
DECLARE @UserName  NVARCHAR(50) = ? 
DECLARE @User_ID   INT

SET @User_ID =    (SELECT ID  
                   FROM  mdm.tblUser u 
                   WHERE u.UserName = @UserName ) 

IF ? = 1 
    EXECUTE mdm.udpStagingClear @User_ID, 4, 1, @ModelName, DEFAULT 
ELSE 
    SELECT 1 AS A

On the Parameter Mapping tab add the variables exactly as shown below:

Add a Data Flow task to the control flow of the package and connect it to the ‘SQL – Clear Staging Tables’ task with a success constraint. Rename the task to ‘DFL – Load Staging Tables’.

Add three further variables to our package as follows:

In the data flow of our package add an OLEDB data source task, set the connection to AdventureWorks and the Data Access Mode to SQL Command. Add the following SQL to the SQL command text window:

SELECT 
    CAST(p.ProductID AS VARCHAR(10)) + pmx.CultureID            AS ProductCode
    ,p.Name            
    ,p.ProductModelID
    ,pm.Name                                                    AS ProductModelName
    ,pmx.CultureID
    ,c.Name                                                     AS CultureName
    ,pd.Description
FROM Production.Product                                         p 
  INNER JOIN Production.ProductModel                            pm 
    ON p.ProductModelID = pm.ProductModelID 
  INNER JOIN Production.ProductModelProductDescriptionCulture   pmx 
    ON pm.ProductModelID = pmx.ProductModelID
  INNER JOIN Production.ProductDescription                      pd 
    ON pmx.ProductDescriptionID = pd.ProductDescriptionID
  INNER JOIN Production.Culture                                 c
    ON pmx.CultureID = c.CultureID

Don’t worry if the formatting turns ugly, that’s just what happens. Press the Preview button and you will see that this query will return us the following columns to our data flow:

ProductCode
Name
ProductModelID
ProductModelName
CultureID
CultureName
Description

We need two more columns in our data flow and to get them we will use a Derived Column transformation task so drag one on to the data flow from the toolbox and connect it up to the data source. Add the columns as shown in the image below:

Next the data flow needs to be duplicated into multiple streams so that the different members and attributes can be loaded to the staging tables. This is achieved by adding a Multicast transformation task to our data flow. This task does not require any configuration. There will be six outputs from the Multicast task and these will be used to load the following:

Product Members
Model Members
Culture Members
Product Model Attributes
Product Culture Attributes
Product Description Attributes

Each of these outputs needs to be tailored as to whether they will be loading a member or an attribute and also which member or attribute they are loading. Add six Derived Column transformation tasks to the data flow and connect them to the Multicast transformation. At this point our data flow should look similar to the following:

For each of the Derived Column transformations add the additional columns as specified below:

OK now we have got all the information we need in our data flows to start loading to the staging tables but before we do that there is one more thing to do. As we are loading new members to the Model and Culture entities as well as Product we need to ensure that we have only distinct values for our member codes to prevent staging errors. To achieve this we add and connect Aggregate transformation shapes to the data flows underneath the ‘Add Culture Member Information’ and ‘Add Model Member Information’ shapes. The images below show how to configure these aggregate transformation shapes:

Group By Culture Group By Model

We are now ready to load the data to the MDS staging tables. Add six OLE DB destination shapes to the dataflow. Three of the destinations will be to load new entity members and the other three will be to load attributes for these new members. Configure the Connection Manager properties of the destinations as follows:

Members Attributes

Connect the first destination shape to the ‘Add Product Member Information’ shape and configure it as a member destination. Click the Mappings tab and set the Input and Destination column mappings as shown below:

Connect the second destination shape to the ‘Group By Culture’ shape and configure it as a Member destination. The column mappings will be the same as above except for the MemberName and MemberCode columns and these will be set to CultureName and CultureID respectively.

Connect the third destination shape to the ‘Group By Model’ shape and configure it as a Member destination. The column mappings will be the same as above except for the MemberName and MemberCode columns and these will be set to ProductModelName and ProductModelID respectively.

Connect the fourth destination shape to the ‘Add Culture Attribute Information’ shape and configure it as an Attribute destination. The column mappings will be as follows:

Configure the next two destinations as Attribute destinations and map the columns as the other Attribute destination replacing the AttributeValue mapping with ProductModelID and Description respectively. Now our completed dataflow should look similar to the following:

If you execute the package you will see that we have inserted 1764 Product member rows, 6 Culture member rows and 118 Model member rows into the mdm.tblStgMember table and 1764 attribute rows for each of the Culture, Model and Description attributes into the mdm.tblStgMemberAttribute table in your MDS database. It is worth noting that the data has now been staged only and we will not see it in our MDS entities yet.

OK that’s as far as we are going to go in part one. In part two we will extend the package to move the data from the staging tables into the MDS model and validate the newly inserted data.

Data Quality Services

Jeremy Kashel — Wed, 13 Jul 2011 12:40:17 GMT

As I’m sure you’ve heard, CTP3 of SQL Server Denali was released yesterday, and can be downloaded here. Denali includes Data Quality Services (DQS), Microsoft’s new data cleansing and matching component that’s based on the Zoomix acquisition that occurred a couple of years back. Data Quality Services didn’t make it into the first CTP, but is now available, so I though it would be worth a quick blog post.

Installing Data Quality Services

Data Quality Services is an option in the main install, but when you go to run the Data Quality Client, you’ll get a message stating that DQS is not installed. As far as I can tell, DQS needs to be installed manually, by running the DQSInstaller.exe, which you can find in the SQL Server Binn directory. This will create two SQL Server databases:

Data Quality Client

Once DQS is configured, you’ll be in a position to use the Data Quality Client, which is a windows application, available in 32 or 64 bit. Once you connect, you’ll get the following screen:

The knowledge base is the key to how DQS works, being utilised to determine the data quality of your source data. You can create your own knowledge base, using your own data, or even cloud-based data. For this example, I’m going to use the built in knowledge base called DQS Data.

As an example, I’ve created some data that I want to cleanse. It’s adventure works country data that I’ve put into a separate table that I’ve called dbo.Country.

My task now is is to clean this data, which I can do by creating a data quality project:

I’ve called my project Countries, and I’ve picked the built-in DQS Data knowledge base, which I noticed contains reference data for countries. The activity that I’ve selected is Cleansing, and then I’ve clicked Create.

DQS will then prompt for mapping the source data to one of the domains in the Knowledge Base. Here I’ve mapping my country name to the Country domain from the knowledge base:

After clicking the Start button on the next screen, the cleaning process starts, which gives the following results:

I put a couple of typos into my source data, such as changing Algeria to ‘Algerian’ and Albania to ‘Albana’. These were picked up by DQS, along with a few others, but a user has the opportunity to approve or reject, via the radio buttons shown above. I chose to approve the first two, and then clicked next. In the final screen, DQS allows you to output the cleaned data to a separate table, as shown below:

If you go into the table that DQS has created, you will see that there is a Name_Status column, which holds the cleaning status on a per record basis:

This can be used to update the source data, if required, and therefore address the data quality issues.

Summary

This has been a quick introduction to cleaning data with Data Quality Services. There’s plenty more to look at, and I hope to find time to do so at some point - In particular I’m keen to take a look at how DQS works with Master Data Services. I’ve noticed that there’s also a new MS DQS blog (http://blogs.msdn.com/b/dqs/) – I’m sure that will be a great resource for DQS learning.

PowerPivot–Simple Custom Rollup

Shaun Ryan — Tue, 05 Jul 2011 15:08:12 GMT

Introduction

I have been having a bit of hands on with PowerPivot just lately and one of the aspects of PowerPivot that I think is extremely powerful is that it provides users with the ability to mash up their own data in Excel with data from a structured SQL Server database. More specifically allowing users to create and control how granular attributes of a dimension might rollup up into a hierarchy is a valuable thing, for example an organisational structure or an accounts hierarchy. Users do need to be quite good with data modelling and DAX to leverage PowerPivot and time will tell on that one. However, with that in mind I’m going to create a user controlled custom rollup on an accounts dimension using only very basic data modelling and DAX concepts.

Essentially within an accounting solution you might want to avoid storing numbers in the context of positive (+ve) and negative (-ve) since in accounting terms either the business owns it or it doesn’t i.e. it’s either an asset or a liability. Also things come into the business and things go out i.e. they purchase supplies and sell goods and services. None of the accounts themselves are either +ve or –ve since in accounting we deal with either credits or debits. Depending on what aspect of the business you’re evaluating and how you’re presenting the figures an account may be considered as in credit for one context but in debit for another. For example the Closing Balance of Stock is considered a Credit in the P&L but a Debit in the Balance Sheet.

In BI data modelling terms we deal with this by associating the aggregation operator with the reporting dimension hierarchy rather than the number itself thus when the measure is combined with the hierarchy it is aggregated within the context of the hierarchy. More specifically if you’re familiar with SQL Server Analysis Services (SSAS) we do this using a rollup operator that is stored on the accounts dimension.

This blog is a simple PowerPivot design pattern to allow business users to create their own custom rollups on an accounts dimension and trial balance fact table from SQL Server.

Solution

I always like to present the final solution before going into the specifics of the design since knowing the final output tends to make design details a bit easier to digest. So here’s what we’re aiming for:

So here we can see I’ve got a P&L hierarchy that rolls up the accounts through Gross Profit and Expenses and calculates the Profit. The accounts and figures have all been take from a SQL Server database however which accounts and how they have been rolled up into Cost of Goods Sold, Sales, Gross Profit, Expenses and Net Profit has all been created and controlled by the Excel user. The P&L roll up is created from the following table in the Excel file that is linked into the PowerPivot model:

So in this table we can see the accounts mapping to relevant parent P&L members and a rollup operator that indicates how the account should be evaluated. The Rollup operators should look familiar if you’re from a SSAS background like me since the symbols are the same. + is for sum, – is subtract and ~ is to exclude. Notice there are many accounts that are not used here since they are not included in the P&L and as such have no parents defined either. A different rollup application might have the numbers excluded from the rollup but still have them included in the hierarchy.

Implementation

The underlying SQL Database contains an Accounts dimension, Date dimension and a fact table containing account Balances. The simple star schema is as follows:

From the diagram we can see that the Account dimension is simply a list of accounts and there is no hierarchy. FactBalance contains the account trial balances which are all physically positive numbers which can conceptually be either summed or subtracted. I’ve included a very simple Date dimension as usual since it’s always good to see how a solution works with more than 1 dimension in play to test and understand what is going on inside the software without making it too complex.

The next step is to pull these 3 tables into Excel 2010 using the PowerPivot add-in. I won’t go into how this is done in detail since there’s loads on the web about getting started with PowerPivot and I’m assuming if you’re looking to do custom rollup then you’ve already made a start with the basics. This is a pretty good book to get your hands on since it covers the basics and some more advanced topics you can get your teeth into further down the road: MS PowerPivot for Excel 2010 (Marco and Alberto)

Below is the Accounts dimension and Balance fact table that I’ve pulled into PowerPivot and renamed for usability.

The next step is to go to the Balance table tab and relate for Account_Key to the Account_Key in the Account table and Date_Key to the Date_Key in the Date table. If your database had these relationships when you imported the tables then PowerPivot will have created them automatically.

So, now we have our PowerPivot model we’re going to create a table in Excel to rollup the Accounts dimension. Enter the following data into spread sheet in the workbook.

With Cell A1 selected click the Link Table icon on the Excel PowerPivot tab.

This will import the data into the PowerPivot model linked directly to the data in the Excel sheet. Rename the new table we’ve just imported to something useful and relate the Account dimension to it using the Account Code and it should look like as follows:

So now we have all the data nicely imported into PowerPivot and related together we’ll apply the custom rollup. To do this I’m going to use the way that SSAS actually applies the operators under the covers (I believe but am sure someone will correct me if I’m wrong). Essentially + operator multiples the fact data by +1, – multiplies the fact data by –1 and ~ multiplies the fact by 0 or null to effectively yield null. After the operator has been multiplied which adjusts the sign of the data or nulls it out we leave it to PowerPivot to simply sum the result to give the answer. At this point you’re probably right in thinking I could’ve just used +1, –1 and 0 instead of +, – and ~ however I just wanted it to be consistent with what we’re (or I’m) used to in SSAS.

In the Budget table we’re going to create a calculated column called PL Operator that uses the PowerPivot relationships we’ve defined to pull the operator down into the Budget table. The simple DAX formula I’ve used can be seen in the next screen capture:

Once we have the operator we can create another calculated column to create the PL Amount column that applies the operator by multiplying PL Operator with the Balance as shown below.

So we can see the PL Amount column now has the rollup operator applied. The only thing left to do is review what columns you want to make visible in the model and drop a pivot table onto a spread sheet and have a look. We could’ve just used 1 calculated column to achieve what we needed to do, I broke it down into 2 just for presentation purposes. Have a play at modifying the Excel based data rollups and refreshing the data model to see how quick and easy it is to manipulate the rollup.

Conclusion

This is a really simple way for users to consume data from a database and apply a very simple custom rollup. I really like the simplicity of it and the fact as a user I can just edit my rollups in my sheet and refresh the data and adjust how the accounts roll up. Also you might have noticed there are trial balance figures for the accounts in my data that would be needed to create a balance sheet and so we could progress this further by having another roll up table to create a Balance Sheet.

I’m going to have a play around a bit more and see if it’s possible to create more complex rollups and perhaps apply different rollup operators for different columns instead of just at the grain of the account. This is where I suspect things will become somewhat more complicated very quickly! Coming from a SSAS background I find it quite useful to take SSAS modelling concepts and challenge my self and PowerPivot to achieve the same end. PowerPivot modelling is a different way of thinking and whilst it may not do what SSAS does out of the box I’m convinced where there is a will there is a way, or maybe not.

SSRS Language parameter using SSAS Translations

Calvin Ferns — Tue, 05 Jul 2011 09:52:24 GMT

Problem

SSAS translations are known to deal with warehousing language requirements well. We recently had a requirement for a multi-language report suite on top of a user browsed cube. First thought was SSAS translations, these will pick up the users local language settings and allow an elegant solution to the problem.

However a requirement was put forward for a language picker in the SSRS report suite to over-ride the users locale if they so desire. This was not so obvious.

Data source

We started with a list of languages to build a picker from, this was built by importing the table available here to a SQL Server and building it as a dataset in our report:

http://msdn.microsoft.com/en-us/library/0h88fahh.aspx

It is important to bring through the decimal value in column 4 and the short string in column 2 as we will see later.

Parameter Build

Use the dataset on MSDN to build a language select parameter. Use the language name in the label field and the decimal locale identifier in the value field. The setup should look something like this:

Default value setup

If you just need English as the default use the UK code 2057 as your default value, however a more elegant final solution is a little more complex.

Reporting services provides the built in variable User!Language which holds the users language in the short string ‘en-gb’ style format. So although we can access the users language it needs translating into a localeId before we can pass it to SSAS. Fortunately for us the same table we used above can be used to translate between short string and locale.

I achieved this using a further hidden parameter defaulted to the built in User!Language variable, the parameter was set up as detailed below:

Using the dataset where LanguageCode = short string in our source table

If this parameter is run first (it must be earlier in the report parameters list) it will contain the users language in the form ‘en-gb’ in the value and ‘2057’ in the label field. It is then simple to set the default of the first dropdown parameter to the value of the hidden parameter’s label using the following syntax.

=Parameters!UserLanguage.Label

I am open to suggestions of a way to get the default without a hidden parameter or a large switch statement so if you find something more elegant please let me know! It is also worth noting that if a user has a language that your source table does not have an entry for you are obviously going to lose the defaulting behaviour.

Parameter Ordering

It is essential to make sure that the parameters are ordered in your parameter list in the sequence they need to be evaluated in. In this case the hidden user language parameter needs to be first, followed by the visible language drop down parameter. Only then should other report parameters be added.

Passing Parameter value to SSAS

In order for SSAS to provide us the data in the language requested we need to pass the locale to analysis services. We can do this by over-riding the locale in the connection string. Go to your report data source and double click for properties. Then we need to edit the connection string expression as circled.

Here we need to make sure our connection to SSAS is provided with the locale from the parameter. We use the following expression to build our connections string.

="Data Source=localhost;Initial Catalog=" & CHR(34) & "Adventure Works DW 2008R2" & CHR(34) & ";Locale Identifier=" & Parameters!Language.Value

(CHR(34) is the “ character that we wanted to escape properly.)

This builds the string detailed below for the parameter selection Spanish.

=Data Source=localhost;Initial Catalog="Adventure Works DW 2008R2";Locale Identifier=3082

This should now preview and allow you to select the language and your data sets will return any translations you have present in the cube.

Notes

If you are using a shared data source reference this is not going to work for you as it is only possible to expression a data source held in the report. In this case perhaps you can use a solution along the lines of the one detailed by Mosha here: http://sqlblog.com/blogs/mosha/archive/2006/10/07/member-caption-translations-in-mdx.aspx

Another warning is to finish your report design first because you cant refresh datasets and metadata once the expression is set

Tested on 2008R2 only.

Hope this helps everyone and allows you to select your existing translations in a flexible way through SSRS. Your comments are welcome.

SQLBits 9–Master Data Services Training Day

Tim Kent — Tue, 05 Jul 2011 08:25:34 GMT

As I’m sure many of you have heard, SQLBits 9 has been announced and will be at the Adelphi Hotel in Liverpool from 29th September to 1st October this year. Jeremy and I will be presenting a full day training on Master Data Services

Master your data with MDS

A end to end look inside Master Data Services, this full day course will begin with a synopsis of Master Data Management before moving on to an overview of Microsoft SQL Server 2008 R2 Master Data Services (MDS) and then looking in detail at topics such as:

installing, configuring, and maintaining Master Data Services
creating and using models
version management
business rules and workflow
importing data into Master Data Services
Integrating with other systems
Security.

It will also take a brief look at what’s coming next for MDS in the SQL Server Denali.
This course is for anyone who wants to manage and maintain their business master data and improve data quality with Microsoft SQL Server Master Data Services. A basic understanding of Microsoft SQL Server is required.

If you don’t fancy MDS, there are ten other training days being offered.

Registration for the training days and conference should be open in the next few weeks and pricing remains the same as last time – an unbelievable £375 for the full conference if you book before 26th August!

Adding a Calculated Field to an Excel Pivot Table With Analysis Services

Jeremy Kashel — Fri, 01 Jul 2011 13:10:17 GMT

One question that I get asked from time to time is how to add a calculated field to an Excel Pivot Table that’s connected to an Analysis Services cube. I’ve been aware of a workaround to do this for a while, but a quick Bing :-) revealed that the common answer given to this question is that it’s not possible.

It definitely is possible, it’s just not that obvious and is perhaps a little limited. Here’s how it can be done in Excel 2010:

Sample Cube

First of all, connect to a cube and produce a pivot table report. I’ve connected to the 2008 R2 version of the Adventure Works DW cube and have produced the following report, using the Internet Sales Amount measure:

Adding a Calculated Field

If you now open the Field List, you will see that Internet Sales Amount is selected of course. Here’s the trick – you can select the measure twice by dragging it into the Values area, which will give the following:

Now click the drop down next to Internet Sales Amount2, which will display the following window:

You can change the name of the field – I’ve called mine Percent Share. In the Show Values as drop-down, I’ve picked % of Grand Total, so that I can see the share of the sales in each country across all years. So now the report is as follows:

Summary

A better approach is to put as many calculations in the cube as possible, but as some calculations requested by users are report-specific, this feature of Pivot Tables allows users to produce the reports that they want.

The calculations that you can carry out are only those in the ‘Show values as’ drop-down. There’s quite a few in there, especially in Excel 2010, as mentioned here. If that’s not enough, then an alternative is to use the OLAP Pivot Table Extensions, which is available to download on Codeplex.

June 2011 Cumulative Update for Master Data Services

Tim Kent — Tue, 28 Jun 2011 09:52:18 GMT

The June CU for SQL Server 2008 R2 includes a good number of fixes and improvements for MDS – Full details and download link:

http://support.microsoft.com/kb/2143880

After installing you need to go in to the MDS configuration manager and upgrade your MDS database:

Calculating Percentile Brackets using SQL Server 2008

David Morrison — Fri, 24 Jun 2011 13:09:41 GMT

Ever had to do statistical analysis on data in sql server? Maybe for reporting or to generate some data for things such as KPI’s in SSAS for example. One aspect of doing data analysis I found interesting and challenging in equal measure is percentiles. What is a percentile I hear you ask? (if you know what a percentile is just skip past this bit)

Well imagine you and 99 other people take a test and that the test is scored numerically. Now generally people being people would like to know how many of their follow exam takers they’ve beaten in the exam (I know I would ). So in our example if you’ve done pretty well and say scored better than 80 of your fellow 99 exam takers, then you would be better than 80% of them and hence you would be in the 80th percentile. This of course is a very simplistic example but you get the idea.

Personally I feel this is more of a common thing in every day life in the America for example than it is here in the UK but it does crop up a lot in reporting and KPI’s for example.

Which way!?

Now in my mind there are two quite distinct “directions” you can take in calculating percentiles. The first is the same as in the example above, you take a score, compare it to the rest of its set or “pot” and work out it’s individual position. The second is where you have a set of percentile “milestones” you wish to calculate (5th, 25th, 50th, 75th and 95th for example), then for each set or “pot” of data you have, you find the value within that pot that is the closest to each of your milestones.

NB: If you try and do something like this in excel, its worth noting that the PERCENTILE function in excel will actually fabricate a figure that is mathematically exact to the percentile you ask for based on the pot of data its given. Although technically the this is more numerically accurate, in my opinion in 99.9% of cases this is overkill, simply because if you calculate percentile milestones from a pot of data, you should only ever compare values from that pot to those milestones, making the additional effort to calculate the precise value rather than finding the closest actual value pointless. My preferred method is to select an actual value from the set that is closest to each of the required milestones.

Each “direction” has its own merits and down points, but I think the decision all boils down to volume, purpose and required accuracy. So using my exam example from earlier, imagine if 100 million people took that test, now think of the follow set of possible scenarios

You want to generate and send each person an individualised result sheet, unless you had to in order to give each person their exact percentile, would you really want to run that calculation 100 million times? Probably not, what’s more likely is that you’d take the milestone approach, even if you set 100 milestones (1 per percentile), it would be cheaper to calculate these first and then compare each value to the mile stones to get roughly their percentile
You allow the test takers to login to a website and see a personalised score sheet and lets say you know that around only 10% of the test takers will login and do this. In this case it would probably be best to generate each individual score as its requested from the website.

Ultimately the choice is up to you but I’d say the milestone approach generally covers more bases, so that will be the focus for this post from here on in.

A worked example

So before we dive into the sql I wanted to run though how this works logically. I feel this will make the sql easier to follow as you can concentrate on how the code is working rather than its doing, if that makes sense.

So lets take our demo data pot below

This is a fictitious set of sales figures by store and sales person, each person value is unique within a store and each unique person has one row each (imagine this is a monthly totals of sales for example).

So the first thing that needs to happen is the data needs to be sorted, smallest to largest. As you can see I have already done this. The next step is to give each row a position or row number, based on its now sorted location in the pot (see below)

Then for each row we need to work out it’s percentile in relation to the pot, we do this simply by dividing 100 by the number of rows in the pot (29 in our case), then multiplying the the resulting figure by that rows, row number. So the calc would look something like this: (100/Recs In Pot) * Row Number

This then gives us each records percentile value relative to its pot (see below):

Now we’ve got the percentile figure for every row what we need to do is evaluate each rows suitability to represent each one of our milestones. In this example I’ll be using 5th, 25th, 50th, 75th and 95th percentiles as my required milestones.

So to determine the each rows suitability to represent each milestone, we do a calculation per required milestone, subtracting the rows percentile value from the required milestone getting the “absolute” value back (This is a mathematical term, basically what it does is ensure you always return a positive figure). This then gives us a, for each rows percentile value, a “distance” from each milestone. The calculation for the 5th percentile would look something like this: ABS(Row Percentile – 5)

See the below for the results for these calculations for our demo data pot:

What this leaves us with is a list of candidate values for each of our milestones. From here it is simply a matter of getting the row with the minimum distance value for each of our milestones (Hence numerically the closest, in either direction, to the percentile milestone). That rows value (sale value, not percentile value) then becomes the value for that given milestone.

This leaves us with a very tidy table as below:

Then all you need to do in your report or wherever you need to know which percentile bracket your row falls in to is to simply compare the rows value to the above percentile table. so to see of your row is in the 25th to 50th percentile range you’d do something like: If My Row Value >= Percentile25Value and <= Percentile50Value then ……

All make sense?? good!

To the code!

So by now hopefully you understand logically how this should work so lets see how to do in sql server!

Ok first of all we need to establish our data set, in this example I am using the adventure works database and working with the [Sales].[SalesOrderDetail] and [Sales].[SalesOrderHeader] tables. I’m using CTE’s here because that’s my style, there are a few ways you can do this, but this is my preferred way. As with all my posts I always say this may not be the out and out best way or maybe it is, but it works for me and works well.

The first part of the code looks like this:

;with SalesDetail as
(
    SELECT  OrderYear, SalesPersonID, LineTotal, (100.00 / RecsInPot) * PositionInPot as Percentile 
    FROM 
    (
        SELECT year(SOH.OrderDate) as OrderYear, SOH.SalesPersonID, SOD.LineTotal 
            ,count(*) over (partition by year(OrderDate), SalesPersonID) as RecsInPot
            ,row_number() over (partition by year(OrderDate), SalesPersonID order by LineTotal) as PositionInPot
        FROM Sales.SalesOrderDetail as SOD
        JOIN Sales.SalesOrderHeader as SOH on SOD.SalesOrderID = SOH.SalesOrderID
        where SOH.SalesPersonID is not null
    ) as T
)

This example varies slightly from the logically worked example above in that this dataset has many “pots” of data within it. In this instance a pot is defined by the OrderYear (which is the year from the SalesOrderHeader OrderDate column) and the SalesPersonID. When we’re done we will be able to see how each sale in each year ranked against all other sales that year with the inclusion of which sales person made the sale. i.e. sales person 123 made a sale of XYZ in 2009, how did that sale compare to all other sales by all other sales people that year.

As in the logical example we take the data, count the number or records per pot (RecsInPot) and then rank the rows in each pot from lowest to highest based on the value of the sale (PositionInPot). From these figures we then work out each rows percentile figure (Percentile).

Then for the next phase we add a second CTE based on the results of the first CTE as below:

Candidates as 
(
    SELECT  OrderYear, SalesPersonID, LineTotal
        ,abs(Percentile - 5) as PT5_Candidate
        ,abs(Percentile - 25) as PT25_Candidate
        ,abs(Percentile - 50) as PT50_Candidate
        ,abs(Percentile - 75) as PT75_Candidate
        ,abs(Percentile - 95) as PT95_Candidate 
    FROM SalesDetail as SD
)

As you can see I have named this CTE “Candidates” and if you’ll remember back to the logical example earlier this is doing exactly the same thing, assessing the suitability each LineTotals value to represent each of the required milestones (I’m using 5,25,50,75 and 95 here but obviously these can be any values you like)

Next we expand the code further, adding a third CTE called Targets

Targets as
(
    SELECT OrderYear, SalesPersonID
        ,min(abs(Percentile - 5)) as PT5_Target
        ,min(abs(Percentile - 25)) as PT25_Target
        ,min(abs(Percentile - 50)) as PT50_Target
        ,min(abs(Percentile - 75)) as PT75_Target
        ,min(abs(Percentile - 95)) as PT95_Target
    FROM SalesDetail as SD
    GROUP BY OrderYear, SalesPersonID
)

As it says on the tin the “Targets” CTE then works out, for each milestone, the smallest percentile distance from the available set available. After this is just a matter of matching up the targets with the candidates which will then give us the value for each milestone. To do this we add the follow final bit to the code:

SELECT  OrderYear, SalesPersonID
    ,[Percentile5]
    ,[Percentile25]
    ,[Percentile50]
    ,[Percentile75]
    ,[Percentile95]
FROM 
(
    SELECT C.OrderYear, C.SalesPersonID, C.LineTotal,
        (
        case
            when PT95_Candidate = PT95_Target then 'Percentile95'
            when PT75_Candidate = PT75_Target then 'Percentile75'
            when PT50_Candidate = PT50_Target then 'Percentile50'
            when PT25_Candidate = PT25_Target then 'Percentile25'
            when PT5_Candidate = PT5_Target then 'Percentile5'
        end
        ) as Percentile 
    FROM Candidates as C
    JOIN Targets as T on 
        T.OrderYear = C.OrderYear
        and T.SalesPersonID = C.SalesPersonID
        and 
        (
            PT5_Candidate = PT5_Target
            or
            PT25_Candidate = PT25_Target
            or
            PT50_Candidate = PT50_Target
            or
            PT75_Candidate = PT75_Target
            or
            PT95_Candidate = PT95_Target
        )
) as T 
pivot (max(T.LineTotal) for Percentile in ([Percentile5],[Percentile25],[Percentile50],[Percentile75],[Percentile95])) as pvt

What we’re doing here is pretty straight forward, we select from the Candidates CTE, joining onto the Targets CTE where they match on order year, sales person and if any of their candidate values match onto one of the targets thus making that rows sales value the value that will represent the matched milestone. We are then pivoting each of the percentile values up to make one row per “pot” which can then be easily joined onto the data later on in reports etc to find a given percentile range for each value.

Finishing up

So the whole of the code looks like this

;with SalesDetail as
(
    SELECT  OrderYear, SalesPersonID, LineTotal, (100.00 / RecsInPot) * PositionInPot as Percentile 
    FROM 
    (
        SELECT year(SOH.OrderDate) as OrderYear, SOH.SalesPersonID, SOD.LineTotal 
            ,count(*) over (partition by year(OrderDate), SalesPersonID) as RecsInPot
            ,row_number() over (partition by year(OrderDate), SalesPersonID order by LineTotal) as PositionInPot
        FROM Sales.SalesOrderDetail as SOD
        JOIN Sales.SalesOrderHeader as SOH on SOD.SalesOrderID = SOH.SalesOrderID
        where SOH.SalesPersonID is not null
    ) as T
),

Candidates as 
(
    SELECT  OrderYear, SalesPersonID, LineTotal
        ,abs(Percentile - 5) as PT5_Candidate
        ,abs(Percentile - 25) as PT25_Candidate
        ,abs(Percentile - 50) as PT50_Candidate
        ,abs(Percentile - 75) as PT75_Candidate
        ,abs(Percentile - 95) as PT95_Candidate 
    FROM SalesDetail as SD
),

Targets as
(
    SELECT OrderYear, SalesPersonID
        ,min(abs(Percentile - 5)) as PT5_Target
        ,min(abs(Percentile - 25)) as PT25_Target
        ,min(abs(Percentile - 50)) as PT50_Target
        ,min(abs(Percentile - 75)) as PT75_Target
        ,min(abs(Percentile - 95)) as PT95_Target
    FROM SalesDetail as SD
    GROUP BY OrderYear, SalesPersonID
)


SELECT  OrderYear, SalesPersonID
    ,[Percentile5]
    ,[Percentile25]
    ,[Percentile50]
    ,[Percentile75]
    ,[Percentile95]
FROM 
(
    SELECT C.OrderYear, C.SalesPersonID, C.LineTotal,
        (
        case
            when PT95_Candidate = PT95_Target then 'Percentile95'
            when PT75_Candidate = PT75_Target then 'Percentile75'
            when PT50_Candidate = PT50_Target then 'Percentile50'
            when PT25_Candidate = PT25_Target then 'Percentile25'
            when PT5_Candidate = PT5_Target then 'Percentile5'
        end
        ) as Percentile 
    FROM Candidates as C
    JOIN Targets as T on 
        T.OrderYear = C.OrderYear
        and T.SalesPersonID = C.SalesPersonID
        and 
        (
            PT5_Candidate = PT5_Target
            or
            PT25_Candidate = PT25_Target
            or
            PT50_Candidate = PT50_Target
            or
            PT75_Candidate = PT75_Target
            or
            PT95_Candidate = PT95_Target
        )
) as T 
pivot (max(T.LineTotal) for Percentile in ([Percentile5],[Percentile25],[Percentile50],[Percentile75],[Percentile95])) as pvt

For reference the resulting output looks like the below. I’m only showing the top 10 here but run the code for yourself against adventure works to get the full set

As you can see this is a small, lightweight reference table that can easily be used to find were a value would fall.

Now that’s about it, apart from a quick note to say if you want to test this yourself I’ve actually added a couple of indexes as below, just to support the query, they aren’t essential but they do make enough of a difference to put them on

create nonclustered index [IX_SalesOrderHeader_SalesPersonID_Inc] on [Sales].[SalesOrderHeader] 
([SalesPersonID] asc)
include (SalesOrderID, OrderDate)
GO



create nonclustered index idx_SalesOrderDetail_SalesOrder_inc on Sales.SalesOrderDetail
(SalesOrderID)
include (OrderQty, UnitPrice, UnitPriceDiscount)

And that’s it, thanks for sticking with it, I know its been a long one and I hope you’ve learnt something

Business Data Warehouse Appliance

sachatomey — Mon, 13 Jun 2011 15:15:00 GMT

Microsoft and HP announced the release of their latest Data Warehousing focused appliance last week, the Business Data Warehouse Appliance (BDW):
http://blogs.technet.com/b/dataplatforminsider/archive/2011/06/06/announcing-the-hp-business-data-warehouse-appliance.aspx

Not to be confused with the BDA (the Business Decision Appliance aka the "PowerPivot Appliance") this latest appliance is targeted at data warehouse workloads and follows the Fast Track Data Warehouse principals.

This is great move, and I think they have the sizing just right; Approx. 5TB-8TB of compressed user data (depending on your achieved compression ratio) will cater for a decent proportion of the warehouses and data marts in operation today.

The announcement appears to focus on the fast deployment (they claim it can be installed and configured in around 10 minutes), and that's pretty impressive but I'd like to know what other appliance specific value they have added to the overall package. After all, installation and configuration of the appliance is just the tip of the iceberg.

I've been lucky enough to be involved in a Fast Track Data Warehouse implementation so I have a couple of ideas (some of which we've implemented) that I'd like to see baked in to a Data Warehouse Appliance offering:

Operational Reporting (Some of which are already achievable through the SQL Server Management Data Warehouse)
- Disk and File group usage reports - Help with on-going capacity planning by detailing growth and trends over time for the disk as a whole and file groups associated with each database.
- Fragmentation Reports - [The methodology surrounding Fast Track has a high emphasis on avoiding and managing fragmentation] Reports that detail levels of fragmentation at both the physical and logical level would potentially pre-empt any fragmentation related issues. A kind of combination of WinDirStat and Internals Viewer would be a great graphical representation of the fragmentation at both those levels.

Database Administration/Developer Accelerators
- Database/object Creation functions - I want to be able to create a database and or a file group of a specific size and let the appliance do the work of creating the physical files on each of the mount points for me. PDW (SQL Server Parallel Data Warehouse Edition) already does something like this that's baked into the product.
- Simplified partition management - For example, I'm likely to want to age my data over time and merge smaller partitions into larger partitions (certainly until the maximum number of partitions limit is raised in 'Denali'), or I might want to remove all the data from a specific partition in preparation for a reload. Make it easy and handle all the swapping out, multi merging etc for me.
- Fragmentation Management - For example, I might want to select 1 or more tables and have them completely rebuilt to remove any extent fragmentation.
- Resize management - My file group is approaching full, I need a bigger one, I want a function to perform that resize in a 'Fast Track approved' manner.
There's a whole host of other 'value-adds' e.g. a 'Best Practice Analyser' that could be included as part of a Data Warehouse appliance, and it will certainly be interesting to see how the appliance develops following adoption over the next few appliance updates/revisions.