Adatis BI Blogs

Debug Custom MDS Workflows

Jeremy Kashel — Mon, 20 May 2013 14:19:04 GMT

The custom MDS workflows, AKA External Action Business Rules or custom MDS assemblies, are one of the more advanced features of MDS. They allow you to extend the standard MDS business rules by getting MDS to fire Entity Member member data at an external assembly of your choice.

There are a lot of moving parts to this feature, MDS business rules, the custom .Net assembly, service broker and a windows service! When a business rule meets your condition, then the rule is written to a service broker queue. The windows service interrogates the queue and then passes the information to the custom assembly. I’m not going to show how to do this, this is already done by Johan Machielse and also on MSDN. What I would like to show is how to debug the use of the custom business rules, just due to the number of moving parts and also a couple of quirks that I’ve encountered when using them.

Custom Assembly Deployment

One of the first things that may catch you out is the deployment of the assembly. To troubleshoot this, I recommend the “-console” argument of the workflow service. If you run it in command line mode you may encounter a few errors:

“Could not load file or assembly 'YourAssembly' or one of its dependencies. This assembly is built by a runtime newer than
the currently loaded runtime and cannot be loaded.”

This means that you need to go the properties of your assembly in Visual Studio and change the Target Framework from .NET Framework 4 to 3.5.

Another error that you may get is "ERROR: Could not create type. Ignored.” Assuming that you have actually put the assembly in the bin directory, this is most likely to be that the either the namespace or the class name in your DLL doesn’t match what you have in your config file. Check the contents of Microsoft.MasterDataServices.Workflow.exe.config match your DLL.

You may also get, “ERROR: Type does not implement IWorkflowTypeExtender.” This means that your assembly has been picked up correctly, but it does not implement IWorkflowTypeExtender.

If the assembly does start up ok without errors, you may still get errors when the exe tries to take a message off the service broker queue. An error such as “Could not dispatch due to missing extender <tag>” means that the tag that you’ve given to the assembly in the config file does not match the “Workflow Type” value of the MDS business rule:

<setting name="WorkflowTypeExtenders" serializeAs="String">
<value>MDSTEST=MDS.CustomWorkflow, CustomWorkflow</value>
</setting>

Debugging the Custom Assembly

Once you do get the assembly working, you may find that the code isn’t quite doing what you want. A good way to troubleshoot this is to choose “Attach To Process” from the Debug menu in Visual Studio, as shown below:

If you do this, then you can add breakpoints to your .Net code as normal and step through in order to troubleshoot issues.

To conclude, this probably isn’t an exhaustive list of all the sort of errors that could occur with custom workflows, but with any luck it will save someone a few hours of troubleshooting.

Maintaining Table Width with Hidden Columns in SSRS

jonathan harding — Wed, 15 May 2013 15:32:23 GMT

Sometimes when using SSRS to create documents such as invoices, it is necessary to include columns in a table which may or may not be shown. Often with these documents layout is very important, and having a table changing width as columns are shown or hidden is unacceptable. It is not possible in SSRS to give a table a fixed width, but we can use another method to simulate this without having to dig into the RDL file. We’ll make a report that contains a table made up of 5 columns: Product...(read more)

Friday Fun: GeoFlow does the Great South Run

sachatomey — Fri, 12 Apr 2013 08:08:00 GMT

GeoFlow was released to public preview yesterday; a new 3D visualization tool for Excel which allow users to create, navigate and interact with time-sensitive data applied to a digital map.

Back in October last year, along with 25,000 other people, my good friend and colleague Tim Kent (@TimK_Adatis) and I ran the Great South Run; a 10 mile run around the City of Portsmouth on the south coast of England. As it happened, we both wore GPS watches and using the data collected I've created a simple GeoFlow tour of the race.

Tim is Green - I am Red - who wins... there's only one way to find out ......

Run Race

SSAS Tabular, PowerPivot and conformed snowflakes

Calvin Ferns — Tue, 19 Mar 2013 22:38:25 GMT

Many of you will be aware of the Kimball concept of conformed dimensions. This is the principle of joining multiple fact tables, often from multiple source systems, into the same dimension table. Using this shared dimension the many fact tables can be analysed by the single view of the dimensional data and deliver powerful cross dataset analysis.

In many cases the granularity of the differing source systems is different, a typical example of this is shown in the Adventureworks where sales data is gathered against an individual product but survey data is gathered against Product Category and SubCategory. In order to facilitate easy key generation and lookups the Product Category and SubCategory are then separated from the main product table, this is described as a Snowflake design pattern as shown below.

The first SSAS Tabular consideration here is that if you need to join in at a less granular level than the primary dimension table is is essential to present a snowflake to SSAS tabular, it cannot join in at a higher grain like SSAS Multidimensional can. This is due to its need for a distinct list on the dimension side of the join key.

The relationships in SSAS Tabular or Powerpivot can then be set up as shown below to join other data in at a higher grain such as the FactSurveyResponse below

The next step is to browse this data and check that we can indeed view all our data by Product SubCategory, using the ‘Analyze in Excel’ button we can browse the development cube and set up the pivot table below. Allowing the slicing of disparate data by common dimensions in this fashion is one of the biggest business benefits of data warehousing.

The solution thus far works but, in Excel and other add hoc tools, the three product tables and large number of attributes are confusing to the end user. As such we should look at hiding unused and unwanted columns and using the tabular cube to denormalise or ‘flatten’ the options presented to the business user. Unwanted columns are removed by using the hide from client tools options or removing unwanted columns from the cube all together (ideally using SQL Views). To reduce the number of tables presented to the user we will hide DimProductCategory and DimProductSubCategory entirely from the user while adding derived columns holding this information to DimProduct. To achieve this we use the RELATED keyword in DAX to add a few new columns. Adding a ProductSubCategory Column to DimProduct we use the following expression:

=RELATED(DimProductSubcategory[EnglishProductSubcategoryName])

Once the above column is available, refreshing the Pivot table used earlier with the new column gives in the following result:

This incorrect result is the impact of the fact that relationships are only propagated towards the fact table automatically in Tabular and Powerpivot. We need to explicitly specify that the measure we are using relates to the DimProduct table through ProductSubCategory and the context of this table needs to be used. To do this we need to alter our measure calculation to take into account the related table DimProduct, unsurprisingly this is achieved with the RELATEDTABLE function. We use the CALCULATE function in combination to aggregate our data as appropriate. The measure DAX formula can then be defined as follows:

=CALCULATE(
    DISTINCTCOUNT([CustomerKey]),
        RELATEDTABLE(DimProduct)
        ,VALUES(DimDate),
        VALUES(DimCustomer)
    )

A values statement is needed to allow the current filter context to be used for all other dimensions on the fact, this doesn’t seem very elegant and I am open to a better way of doing it. The measure as defined above can then be used in excel to give the same result as we first achieved while giving a much cleaner user interface with a single product table for users to find all their information.

Any question or improvements get in touch here or @CalvinFerns

Thanks for reading

Technorati Tags: SSAS,Tabular,PowerPivot,Snowflake,Kimball,Comformed,Dimensions,Granularity

Different Methods Of Type 2 Dimension Population

JEOC — Thu, 28 Feb 2013 17:18:00 GMT

This post is to share some of my findings about the different ways of dealing with Type 2 changes in SSIS environment. I wanted to provide comparison of the methods in terms of performance, reusability and ease of maintenance.

For the purposes of the testing let’s take the scenario where a client would like to implement historical tracking in their DW environment for one of their dimensions, lets say that they have given the following requirements:-

The ordered online flag should be replaced with a more user friendly text field containing ‘Yes’ or ‘No (done in a derived column transformation/SQL case statement).
The client would like to track any changes to the Purchase Order Number, Customer Account Number or Carrier Tracking Number so that they can preserve the history of these changes. These will be setup as Type 2 changing attributes and we will need to record the time that each change was induced.
Any changes to the OrderedOnline field will be as the result of corrections made to the source data and historic tracking of changes will not be required for this in the DW and all rows should be updated with the new value (a Type 1 change).

I created the following table to hold the data:

CREATE TABLE DimOrder
    (Order_Key INT IDENTITY(1,1) PRIMARY KEY NOT NULL --Surrogate Key
    ,SalesOrderID INT NOT NULL --Source Key
    ,LineNumber TINYINT NOT NULL --Source Key
    ,OrderNumber NVARCHAR(25) NOT NULL
    ,PurchaseOrderNumber NVARCHAR(25) NOT NULL
    ,AccountNumber NVARCHAR(15) NOT NULL
    ,CarrierTrackingNumber NVARCHAR (25)
    ,OrderedOnline NVARCHAR (3) NOT NULL
    ,RecordValidFromDate DATETIME NOT NULL
    ,RecordExpiredDate DATETIME
)

The three methods I have selected are:-

The SSIS inbuilt Slowly Changing Dimension (SCD) Task.
A custom built SSIS package to perform the Type 2 changes.
Using a T-SQL Procedure to perform the dimension population.

In each of the comparisons I have performed the following tests:-

An initial load of data (all of the records are new are there are no changes.
A load of the same data from Step 1 (no changes all records exist)
A load of data which contains both Type 1 and Type 2 changes

I used Adventure Works as a source and Step 1 & 2 both use the same source statement which looks like this:

SELECT
     OrdHed.SalesOrderID
    ,SalesOrderDetailID
    ,OnlineOrderFlag
    ,SalesOrderNumber
    ,PurchaseOrderNumber
    ,AccountNumber
    ,CarrierTrackingNumber
FROM Sales.SalesOrderHeader OrdHed
INNER JOIN Sales.SalesOrderDetail OrdDet
    ON Ordhed.SalesOrderID = Orddet.SalesOrderID

For the third test I created some copies of the source tables and then ran some basic statements on them to change the format of the data as below:

SELECT * INTO [Sales].SalesOrderDetailTest FROM [Sales].SalesOrderDetail
SELECT * INTO [Sales].SalesOrderHeaderTest FROM [Sales].SalesOrderHeader

--this will only exhibit Type 1 changes

UPDATE Sales.SalesOrderHeaderTest
SET OnlineOrderFlag = Case when OnlineOrderFlag = 1 THEN 0 ELSE 1 END
WHERE salesOrderID BETWEEN 59392 AND 67257

--this will exhibit only a Type 2 change

UPDATE Sales.SalesOrderHeaderTest
SET PurchaseOrderNumber = 'X' + PurchaseOrderNumber
WHERE salesOrderID < 51525

--this does a mix of both.

UPDATE Sales.SalesOrderHeaderTest
SET
PurchaseOrderNumber = 'X' + PurchaseOrderNumber
,OnlineOrderFlag = CASE WHEN OnlineOrderFlag = 1 THEN 0 ELSE 1 END
WHERE salesOrderID BETWEEN 51524 AND 59391

The data is then sourced from the new tables which provide the changes for testing.

1. SSIS Slowly Changing Dimension Task

Configuration of the package is very straightforward; you add a data source, drag on a slowly changing Dimension task to the data flow and double click it to configure how you want it to run. The whole process takes less than 10 minutes to complete and there are many options that should suit a variety of needs.

Once the configuration is completed we end up with a dataflow which looks like this:

2. Custom Built SSIS Package

Next I designed a package in SSIS but without using the SCD wizard. I used the following principles during design to try and improve performance:

1. Changes can be detected in the data flow by using simple lookup transformations (we could consider hash checking to improve performance if there were very many columns) to see if there is any change to the data, by using several lookups with we can determine if the change is Type 1, 2 or both.

2. Updates in the data flow are performed in SSIS using the ‘OLE DB Command’ task. As this works on a row-by-row basis this has a large impact on performance. To avoid this I created tables to hold the Type 1 and Type 2 changes and then used an ‘Execute SQL task’ in the control flow to make the updates in one go.

The completed dataflow looks like this:

New records go straight into the target tables and the ones which have changes are split using a multicast, where a change has occurred the records are inserted into holding tables and then processed in the control flow.

The control flow looks like this:

The control flow executes the statements below:

--Make Type 1 changes

UPDATE TGT
SET TGT.OrderedOnline = SRC.OrderedOnline
FROM DimOrder TGT
INNER JOIN DimOrderType1 SRC
ON TGT.SalesOrderID = SRC.SalesOrderID
AND TGT.LineNumber = SRC.LineNumber;

--Type 2 expire old rows

UPDATE TGT
SET TGT.RecordExpiredDate = SRC.TransactionDate
FROM DimOrder TGT
INNER JOIN DimOrderType2 SRC
ON TGT.SalesOrderID = SRC.SalesOrderID
AND TGT.LineNumber = SRC.LineNumber;

--Type 2 insert new records

INSERT INTO DimOrder(SalesOrderID,LineNumber,OrderNumber,PurchaseOrderNumber,AccountNumber
                              ,CarrierTrackingNumber,OrderedOnline,RecordValidFromDate)
SELECT
    SalesOrderID
    ,LineNumber
    ,OrderNumber
    ,PurchaseOrderNumber
    ,AccountNumber
    ,CarrierTrackingNumber
    ,OrderedOnline
    ,TransactionDate AS RecordValidFromDate
FROM DimOrderType2;

3. T-SQL Script

I also thought I would try a basic SQL script to see how this performed.

CREATE PROC popDimOrder AS

BEGIN

            CREATE TABLE #OrderData
                (
                        SalesOrderID INT NOT NULL
                        ,LineNumber INT NOT NULL
                        ,OrderNumber NVARCHAR(25) NOT NULL
                        ,PurchaseOrderNumber NVARCHAR(25) NOT NULL
                        ,AccountNumber NVARCHAR(15) NOT NULL
                        ,CarrierTrackingNumber NVARCHAR(25) NOT NULL
                        ,OrderedOnline NVARCHAR(3) NOT NULL
                        ,RecordExists smallINT NOT NULL default(0)
                        )

DECLARE @TransactionDate Datetime

SELECT @TransactionDate = GETDATE()

INSERT INTO #OrderData(SalesOrderID, LineNumber, OrderNumber, PurchaseOrderNumber, AccountNumber, [CarrierTrackingNumber], [OrderedOnline], RecordExists
)

            SELECT
                        OrdHed.SalesOrderID
                        ,SalesOrderDetailID As LineNumber
                        ,SalesOrderNumber As OrderNumber
                        ,ISNULL(PurchaseOrderNumber,'') As PurchaseOrderNumber
                        ,AccountNumber
                        ,ISNULL(CarrierTrackingNumber,'') As CarrierTrackingNumber
                        ,CASE OnlineOrderFlag
                                    WHEN 0 THEN 'No'
                                    WHEN 1 THEN 'Yes'
                        END As OrderedOnline
                        ,0 As RecordExists
            FROM [AdventureWorks2012].Sales.SalesOrderHeaderTest OrdHed
            INNER JOIN [AdventureWorks2012].Sales.SalesOrderDetailTest OrdDet
                        ON Ordhed.SalesOrderID = Orddet.SalesOrderID

--Check if records exist

            UPDATE SRC
            SET RecordExists = 1
            FROM #OrderData SRC
            INNER JOIN POC.dbo.DimOrder TGT
                        ON TGT.SalesOrderID = SRC.SalesOrderID
                        AND TGT.LineNumber = SRC.LineNumber

--Insert new records

INSERT INTO DimOrder(SalesOrderID,LineNumber,OrderNumber,PurchaseOrderNumber,AccountNumber
,CarrierTrackingNumber,OrderedOnline,RecordValidFromDate,RecordExpiredDate
)

            SELECT SalesOrderID
                        ,LineNumber
                        ,OrderNumber
                        ,PurchaseOrderNumber
                        ,AccountNumber
                        ,[CarrierTrackingNumber]
                        ,[OrderedOnline]
                        ,@TransactionDate AS RecordValidFromDate
                        ,null As RecordExpiredDate
            FROM #OrderData
            WHERE RecordExists = 0

DELETE FROM #OrderData WHERE RecordExists = 0

--Make Type 1 changes

            UPDATE TGT
            SET TGT.OrderedOnline = SRC.OrderedOnline
            FROM POC.dbo.DimOrder TGT
            INNER JOIN #OrderData SRC
                        ON TGT.SalesOrderID = SRC.SalesOrderID
                        AND TGT.LineNumber = SRC.LineNumber

--Check for Type 2 changes

UPDATE SRC
 SET RecordExists = 2
 FROM #OrderData SRC
 INNER JOIN POC.dbo.DimOrder TGT
 ON TGT.SalesOrderID = SRC.SalesOrderID
 AND TGT.LineNumber = SRC.LineNumber
 WHERE
 TGT.RecordExpiredDate IS NULL
 AND(TGT.AccountNumber <> SRC.AccountNumber
 OR TGT.CarrierTrackingNumber <> SRC.CarrierTrackingNumber
 OR TGT.LineNumber <> SRC.LineNumber
 OR TGT.OrderNumber <> SRC.OrderNumber
 OR TGT.PurchaseOrderNumber <> SRC.PurchaseOrderNumber
 )

--Expire Old records

            UPDATE TGT
            SET TGT.RecordExpiredDate = @TransactionDate
            FROM DimOrder TGT
            INNER JOIN #OrderData SRC
                        ON TGT.SalesOrderID = SRC.SalesOrderID
                        AND TGT.LineNumber = SRC.LineNumber
            WHERE SRC.RecordExists = 2

--Insert New Records

INSERT INTO DimOrder(SalesOrderID,LineNumber,OrderNumber,PurchaseOrderNumber,AccountNumber
,CarrierTrackingNumber,OrderedOnline,RecordValidFromDate)

            SELECT
                         SalesOrderID
                        ,LineNumber
                        ,OrderNumber
                        ,PurchaseOrderNumber
                        ,AccountNumber
                        ,[CarrierTrackingNumber]
                        ,[OrderedOnline]
                        ,@TransactionDate AS RecordValidFromDate
            FROM #OrderData WHERE RecordExists = 2

DROP TABLE #OrderData

END

Results

	Inbuilt SSIS Task	Custom Built SSIS Task	T-SQL Script
Initial Load	9m 22s	3s	2s
Second Load (no new data)	20m 54s	3s	3s
Type 1 and 2 changes	1h 31m 43s	5s	4s

What we see from the results is quite surprising. There is clearly a large difference in speed between the Inbuilt SSIS task and the other two methods, in the last test we see that the T-SQL script performs the same operation over 1000 times faster than then the inbuilt SSIS task. The difference between the T-SQL script and the Custom built SSIS task however is fairly minimal.

I did do some further tests replacing the OLE DB Command component of the auto generated script with inserts into tables with a series of updates much like in the second custom built task, however I found that this performed marginally faster so can only think that the major impact on speed must come from the way that the task performs the matching rather than the row-by-row operations used by the component.

In terms of ease of maintainability I would say that the SSIS task is the simplest one to use and maintain, the Custom built SSIS package is more complex although nothing someone accustomed to SSIS should feel uncomfortable with. Whilst the T-SQL task is definitely the hardest to maintain and takes the longest time to develop.

Conclusion

Whilst there is no correct choice in this kind of situation it is important to be aware of the methods available (this is by no means a complete list!) and make the decision based upon the needs of the environment in which the solution is to be used.

The Inbuilt task does provide an effective mechanism for building SCD into a warehouse and in cases where the number of records are small, however if you intend to deal with large volumes of data or have a tight loading time it is worth assessing the impact of using the SCD component and seeing what kind of benefit using an alternative approach might provide.

Unless the need for fast loading is absolutely critical to the project however I would stay away from the third solution as the performance benefit is small compared to the overhead it places upon maintenance and development.

Please note these are only my findings and it is very important that you should thoroughly test anything that you see here prior to deploying in your own environment.

Data Quality Services - Kimball ETL Screening

Jeremy Kashel — Thu, 28 Feb 2013 10:39:53 GMT

Although it’s arguably not the most popular in the Kimball series, The Data Warehouse ETL Toolkit contains a huge amount of vital information that’s focussed on the ETL side of data warehousing. In particular I’ve found the data quality screens design to be useful, so I thought it might be worth exploring if this concept can be applied to Data Quality Services.

What is a Kimball Data Quality Screen?

A screen is simply a check that is made against data that comes into the ETL pipeline and is used to measure data quality. Physically, the Kimball screen design is typically implemented as a table called Screen, which contains, as rows, the actual checks to perform against staged data. In addition, the screen table will contain metadata to aid data quality measurement, such as screen severity, category, source system and possibly exception actions.

Each time that a screen yields some results (i.e. data quality issues) then another table, Error Event Fact, is populated with the results. Therefore, by using the data generated by the screening process, The Error Event Fact becomes the place to look for an overall measurement of data quality. The schema for this design from the Kimball book (with a few modifications) is as follows:

The check performed by each screen is typically a snippet of SQL that checks for a particular condition, e.g. Is the customer’s postcode missing, or is the tax amount within permitted ranges?

Can Data Quality Services assist with ETL screening?

DQS is capable of auditing and checking data, as each domain within a knowledge base can have its own domain rules. In order to see how this might work as Kimball screens, I’ve set up a simple knowledge base around purchase orders. Here’s the domain management window from the DQS client:

Within the Delivery Charge domain, I’ve setup a domain rule called ‘Delivery Charge Negative’ in order to ensure that the delivery charge cannot be negative:

I’ve done something similar with the PO Date domain, setting up a rule to state that the PO date must be greater than 01/01/2012 in this case. Data run against the PO knowledge base will now be enforced against these two rules, so these are effectively my two Kimball style screens. To recap, DQS cleaning can be run either from the Data Quality Client or from SSIS.

Populating the Error Event Fact with SSIS

I’m going to be running my DQS domain rules via SSIS, as my end goal is to populate the Error Event Fact. Remember, if any DQ issues occur, I want the Error Event Fact to know about them.

In this scenario, the data from the source system has already been staged to a SQL table, which gives the following results in the SSIS source adaptor preview:

This data is passed to the DQS Cleansing SSIS task, with the data mapped to the DQS Knowledge Base in the following way:

Once the package is run, the data will be passed to DQS, which will run the data against its knowledge base, including running the domain rules. The data that is returned from DQS is as shown below. Note, PO55 row has failed both rules, whereas the other two rows have failed one row each.

Now we know exactly which rows have failed each data quality screen. The only trouble is that the data is in the wrong format for our error event fact. We want a total of 4 rows in the error event fact has PO Id 5 has failed 2 screens, whereas the other two rows have failed 1 screen. Therefore, we carry out a unpivot before inserting into the Error Event Fact. For this blog post my ScreenId is hard coded, but in reality you would have to lookup an appropriate ID for the DQS screen. The full SSIS package is as follows:

Data Quality Reporting

With the Error Event Fact populated, a variety of reports can be produced. In this case I’ve made a very simple Analysis Services cube and made a quick report in Excel 2013, using slicers, sparklines and also the new Timeline feature:

From this report I can see the screen that’s failed the most rows, see a trend of data quality and filter on specific issues.

Summary

With a bit of work, DQS is capable of producing the rows needed for the Kimball Error Event Fact, but how good is it? I see the advantages of using DQS as follows:

Front end user interface in order to allow data stewards to set up their own rules to screen the data – much better than holding rules in SSIS or manually in a table;
DQS becomes a one stop shop for all data quality, as, in addition to holding the screens as domain rules, DQS will clean your data for you, according to the valid/invalid domain values that you set up in the knowledge base.

There are a few disadvantages to it also:

The domain rules are not as flexible as pure SQL or SSIS, meaning that you may not be able to express all the screening rules that you had in mind;
DQS can't maintain all the metadata that you need to implement Kimball screening, so you’ll need to supplement it with some SQL tables to hold things like screen severity, screen type etc;
Even though the performance has been improved in CUI, it’s still not suitable for large amounts of data. Here’s an overview of how long you can expect the SSIS cleansing to take.

So my view is that if you’re already using DQS and data stewards like it using it to manage data quality, and your data volumes are not too big, then implement the classic Kimball screening design using DQS. But there are more cons than pros unfortunately, so if you’ve not already made an investment in DQS, and you have the time to invest in your own Kimball style screening solution, then I think you can come up with something that’s a bit more flexible and scalable.

Differences between Regular SSRS and SSRS for Dynamics AX 2012

jonathan harding — Tue, 05 Feb 2013 09:00:00 GMT

I’ve spent quite a long time recently developing SSRS reports for Dynamics AX 2012. In a change from AX 2009, the newer version now uses SSRS as it’s main reporting engine. No doubt this is so that business with existing experience in the MS SQL stack can get on creating reports for their new AX 2012 installation without the need for re-training or bringing in extra people trained in X++.

So not a bad idea, however my experience has turned up a number of important differences between standard SSRS reports and those in Dynamics AX:

Placement of Report Code

In Dynamics AX 2012 SSRS reports you cannot use the code window found by going to the report properties. The window is still there, and you can still type into it, but as soon as you build the report in AX the code will be stripped out of the report. This is because when you build a report design within a Dynamics AX report model in Visual Studio, the process adds a custom code block to the report XML (a method to overwrite the report OnInit() method). This wipes out any code you may have added.

Instead, you must add code as a new Data Method in the report model. This can then be referenced in your report design like this: =MyMethod(arg1)

No ‘ResetPageNumbers’ Property on PageBreak

A great addition to SSRS 2008 was the ability to reset the page number global based on groups within your report. This was handled by a property found by expanding out the ‘PageBreak’ property for the group. Unfortunately this option does not exist in SSRS for Dynamics AX 2012. There may be a good reason for this, and if anyone knows it please let me know!

To get around this I added a data method which keeps track of which group my report is on as it renders and resets the page number appropriately.

Conditionally Hidden Elements cause ConsumeContainerWhitespace Property to Fail

Suppose I have a one-page report that should fit onto an A4 page. The report contains a data table and another section, which should appear at the bottom of the page regardless of the number of rows in the table.

To do this, I enclose the data table in a rectangle element which is sized such that the ‘bottom section’ is at the bottom of the page. By setting the report property ‘consumecontainerwhitespace’ to true, I ensure that as the table grows it fills up it’s containing rectangle, but the ‘bottom section’ stays where it is.

This example would also work in AX, unless you place an element in the rectangle which can be conditionally hidden. For example I may have a row in the table which should only be shown in certain conditions. In standard SSRS, the report would continue to function as before, with the table consuming the rectangle and the rectangle staying the same size. In AX however, the addition of the hidden row causes this behaviour to break and that rectangle to grow as rows are added to the table.

A workaround for this is to make the optional row ‘invisible’ by shrinking it and/or making the text and borders white so they don’t show up.

Absolute Positioning of Report Elements in SSRS

jonathan harding — Fri, 01 Feb 2013 14:58:43 GMT

A client recently asked for an invoice document to be designed using SSRS. The layout was pretty standard but for one important caveat: The payment details section had to be in a fixed position at the bottom of the last page of the report, so a 3-page invoice should look like this example:

This could be achieved by putting the ‘Payment Details Section’ fields in the report footer, and setting their visibility based on the report page number. Unfortunately in SSRS there is only one footer for every page and it’s height cannot be varied between pages. The client did not want to waste space in the report body, which is a side-effect of this method.

So, how to fix the position of a report element to the bottom of the report?

The basic premise of my solution was to incorporate a ‘hidden’ element into the report body. This element can grow or shrink dynamically to alter the distance between the last of the invoice lines and the payment details section, and thus appears to keep the payment details ‘fixed’ at the bottom of the report. To do this, we need to know how many rows of data can fit on the first page of the report (r1):

And how many we need to add to push the payment details section to the bottom of the next page (r2 + r3):

Once we have values for r1, r2 and r3, we need to add (r2+r3) additional rows to the invoice lines table on our layout. These rows must be outside of all groupings on the table as we don’t want them repeated with the groups, we just want them to appear at the foot of the table. To get the desired effect, we need to control the visibility on these additional rows such that given the total number of data rows in the table, enough additional rows are visible to ‘push’ the payment details section to the bottom of the last page.

To control the visibility of the additional rows, we need to add some code to the report:

   1:  Public Function RowHidden(countRows As Integer, thisRow As Integer) As Boolean

2:

   3:          Dim maxRowsPageOne, additionalRows As Integer

   4:          Const TOTALADDITIONALROWS As Integer = 35

   5:          maxRowsPageOne = 12

6:

   7:          If (countRows <= maxRowsPageOne) Then

   8:              If (thisRow <= (maxRowsPageOne - countRows)) Then 'Current row is inside visible range for first page

   9:                  Return False 'Show the row

  10:              Else

  11:                  Return True 'Hide the row

  12:              End If

  13:          Else

  14:              additionalRows = TOTALADDITIONALROWS - ((countRows - maxRowsPageOne) Mod TOTALADDITIONALROWS) 'This is key, determines count of rows to make visible

  15:              If additionalRows < TOTALADDITIONALROWS Then

  16:                  If thisRow <= additionalRows Then

  17:                      Return False 'Show the row

  18:                  Else

  19:                      Return True 'Hide the row

  20:                  End If

  21:              Else

  22:                  Return True 'Hide all extra columns

  23:              End If

  24:          End If

25:

  26:      End Function

This code needs to be called by every additional row in the invoice lines table using =Code.RowHidden(CountRows(), 1) where ‘1’ represents the number of the particular row, starting from the first additional row after the data rows.

And that’s all there is to it! It should be noted that this method means the cells in the invoice line table cannot be allowed to grow, or the calculations for the number of additional rows to show will not work. You also need to be very careful with any future changes to the layout of the report, as these could also affect those calculations. It’s certainly worth seeing if there can be some compromise on the positioning of report elements to something that SSRS can handle better, but if not, then I hope this is useful!

Until next time…..

Unique Values in Master Data Services

Jeremy Kashel — Fri, 07 Dec 2012 13:29:27 GMT

I’ve been doing quite a few demos and Proof of Concept (POC) projects recently for MDS. Understandably there are always a raft of questions that come up during demos and POCs, one of which recently was “how do I ensure that this attribute’s value is unique?”.

MDS can easily deal with simple uniqueness enforcement, which is something that anyone who has done any MDS development will no doubt know straight away. But if the requirement is more complex, such as ensuring that the attribute is unique in conjunction with other attributes, then I don’t think it’s as well known. Therefore I thought it would be worth a quick post on how to do this.

Code Attribute

To get the simple case out of the way, each MDS entity is always created with an attribute called Code, which must be unique. Therefore if you try and create/update a member with a duplicate Code, then you get the following error message:

Business Rules

To enforce uniqueness on any other attribute, you have to use business rules. Therefore, if I want to ensure that the attribute called Name must be unique, then I must carry out the following:

Go to the business rules editor and create a new rule
Drag the action of “must be unique” over to the actions node
Drag the Name attribute over to the “Select attribute” node, meaning that the action should look as follows:
Once that’s done, save the rule step and then re-publish the rule.

Trying to enter a duplicate value for the Name attribute will now give the following validation error:

Business Rules – Multiple Attributes

Now for the harder scenario. In the sample MDS product model, there is a Country attribute and also a Product Sub Category. I want to ensure that Name is unique, but only within a Country and Sub Category combination. To do this we go to the same business rules interface, and add the same action as before. Once that is done we carry out the following additional steps:

Change the radio button to “In combination with the following”
This is the part that I don’t think is that obvious. Drag over the Country attribute and then the Product Sub Category attribute onto the “attributes” node. This should give the following:
As before, save the rule step, then publish the rule.

Now a duplicate name will be permitted, but only within a country and sub category combination. If we do enter a duplicate value within a given Country/Product Sub Category group, then the following validation message is shown:

SQL PASS Summit 2012

Calvin Ferns — Tue, 13 Nov 2012 05:11:49 GMT

Having just returned from SQL PASS I thought I would blog a few highlights about the conference and some of the things I came across and am looking forward to. I went over to Seattle with two of my colleagues and we started with a little sight-seeing to get our body clocks back in sync and I would thoroughly recommend it. The jetlag from the UK gets to you for the first couple of days and you get to see things like this:

The Adatis SQLPass team: Myself, Neil Dobner, Tim Kent

This was my first time at PASS and I was not disappointed. The first couple of evenings in Seattle were spent catching up with some people I have met at SQL Bits. The SQL community being what it is I was quickly introduced to more people and making new connections. The #SQLfamily on twitter are a welcoming bunch and really add a lot to the atmosphere of a friendly conference. I would strongly recommend using twitter for both connecting with people and using the #SQLHelp hash tag to ask questions. I had come across a number of the speakers at SQL Bits and they were all very good quality and I heard very little negative comment on the sessions across the 3 days.

There were a good number of highlights for me that I would like to pass on in case they are as relevant to you as they are to me. I've split them down into a few areas, firstly Powerview:

PowerView coming to SSAS Multidimensional
PowerView in Excel
Powerview on Multidimensional will come to Sharepoint well before it gets into the Office Excel release cycle
Image Urls and geospatial reports are also particularly easy

Some brilliant stuff in a Tabular internals stuff given by Akshai Mirchandani and Allan Folting, DAX queries by Alberto Ferrari and 2012 SSAS by Chris Webb:

Insights into value encoding and hash encoding compression which makes sense of guidance such as
Don't bring in any row level identifier or document numbers etc unless you really have to
Use a process defrag as maintenance when doing updates and deletes
Lower end hardware can be quicker, ie go for less but faster procs
Profile you DAX queries to look out for formula engine calls using: CallBackDataId
Partitions are not eliminated by the query optimiser they only help you manage processing, this is particularly impressive because the engine is so fast it didn't need this!
Direct query really is unusable given the limitations and the awful SQL it generates
Two thirds of the SSAS multidimensional codebase is there to deal with M2M correctly

Finally some database engine stuff:

Windowing functions in 2012 really will be a lifesaver for complex queries, especially lag and lead for those moments where your user thinks you work in Excel
Updatable columns store indexes!
Licencing can be a nightmare of Cores, processors and virtualisation but the key way to keep it sensible on the new model is to keep you core density at 4 cores per processor.

I met loads of fantastic interesting people and have picked up some great contacts. The conference has also had the added side affect of getting me excited about SQL Server again. It is easy to get caught in the day job and forget what a brilliant product we work with and what hard work goes on at Microsoft to keep it that way.

Thanks everyone

See you Next Year? or maybe SQL Bits a bit closer to home.

@CalvinFerns

Master Data Services SQL Server 2012 Vs 2008 R2

Jeremy Kashel — Fri, 09 Nov 2012 15:03:14 GMT

I’ve given quite a few MDS demos recently and one question I seem to get asked often is “What features does MDS 2012 have compared to MDS 2008R2?”. As its happened quite a few times, I thought it would be a good idea to put a quick post together to explain the differences. The table below details all the features MDS has and compares the two versions:

Feature	MDS 2012	MDS 2008 R2
Front End	· New Silverlight UI added for the Explorer area. Better performance, with less clicks to get tasks done. · Excel Add-in introduced	· HTML based ASP.NET implementation. · No Excel Add-in
Modelling	· Carry out in the web UI, plus some tasks can be carried out in Excel · Allows the auto generation of a code without using business rules	· Carry out modelling in the web UI only
Hierarchies	· Three types of hierarchies · Manage hierarchies in the new Silverlight UI	· Three types of hierarchies · Manage hierarchies in the older UI style
Collections	· Add members from Explicit Hierarchies or other Collections · Maintain a weighting on a member by member basis	· Add members from Explicit Hierarchies or other Collections
Staging Process	· A staging table is created per entity that you create in MDS. The table mirrors the exact structure that you see when you view the entity in the front end, making loading easier.	· A generic staging table is used for all entities. This means, for example, you must load attribute changes in attribute name/value pairs
Deployment	· Deploy the model structure from the front end. · Deploy the data from the new command line deployment tool	· Deploy model structure and data from the front end
Security	· Grant permissions to models, entities, attributes and attribute groups · Security cleaned up and simplified (no longer necessary to grant permissions to hierarchies)	· Grant permissions to models, entities, attributes, attribute groups and hierarchies
Business Rules	· Alter attribute values, validate data integrity, initiate workflow. · Some minor validation changes around using business rules to auto-generate Code values	· Alter attribute values, validate data integrity, initiate workflow.
SharePoint Integration	· Initiate a SharePoint workflow from MDS business rules · New functionality to embed the web UI inside a SharePoint page	· Initiate a SharePoint workflow from MDS business rules
Data Quality Services (DQS) Integration	· Connect to a DQS matching policy from Excel, against MDS master data	· No DQS capability, as DQS is new in SQL Server 2012
Version Management	· Create multiple copies of your master data and work with them independently as needed · Some minor UI improvements have been added	· As SQL 2012, create multiple copies of your master data and work with them independently as needed
Transactions (auditing)	· Transactions removed from Explorer and now are only available in Version Management · Therefore users cannot reverse their own transactions, only administrators can do this · Annotations are now permanent and cannot be deleted	· Transactions viewable in two areas, Explorer and Version Management · Users can reverse their own transactions · Annotations can be deleted
Metadata	· No support to view Metadata – this is now a deprecated feature	· Customise the metadata for most objects in MDS · View metadata in the metadata explorer
Installation & Configuration	· Installation from the main SQL Server setup program · Minor changes to the configuration settings	· Installation from the SQL Server media, but not included in the main setup program

For me the improvements to the performance and the addition of the Excel Add-in are the most compelling reasons to upgrade. The great thing in particular about the Excel Add-in is that data stewards can manage data with the familiarity of Excel, but the data itself will be stored in SQL Server 2012.

SQL Server Delete does not release file space allocation

Martyn Bullerwell — Wed, 17 Oct 2012 18:41:22 GMT

This one’s a short blog post, and by no means new, but it may help you in the future and maybe save some head scratching. Commonly in Data Warehousing projects you will be processing data through tables whilst staging and cleaning source data. These tables may commonly be heaps (no Clustered Index), that have no persistent data; rather they are a transient set of tables that get deleted from when your Warehouse loads each day.

What you may not know is that when a heap is deleted from (as opposed to truncated) the space that was used by that data is not automatically de-allocated from the table, and when new data is added the table space is increased, even though the table may be “empty”.

So what is actually going on? When deleting from a heap the database engine either locks the page or the row, as opposed to the whole table when deleting on a clustered index. During the delete on the heap the page is emptied, but the engine does not de-allocate the page. This behaviour only occurs on a heap, and can cause very large tables with no data in them. For my recent project, it caused the stage and clean processes to slow down gradually over time because the database engine was scanning huge tables with no data in them.

So, what can we do to stop this behaviour? There are three options (as documented by Microsoft here (see Locking Behaviour)):

1. When deleting from the table obtain an exclusive lock on the whole table, by using a TABLOCK hint as follows:

DELETE FROM [TABLE] WITH (TABLOCK) WHERE [COLUMN] = 'CRITERIA'

2. If possible, truncate the whole table, this will de-allocate all pages.

3. Finally, my least favourite, add a clustered index to the table.

Technorati Tags: SQL Server 2012,Delete,Heap

RANKX–Analysis Services 2012 Tabular

Martyn Bullerwell — Wed, 17 Oct 2012 07:29:14 GMT

RANKX was a new function made available in DAX at the launch of SQL Server 2012; however Ranking is a widely used function in most BI projects, and was very much anticipated. Having worked extensively with Tabular on a recent project we had the need to rank over large sets of data, with models based on billions of rows. Whilst we were extremely impressed with the performance of Tabular, we found something not quite right with RANKX. Suppose we had the following very simple format of data, and wanted to perform a simple Rank over it.

CountryKey	Date	Units	SalesValue
53	24/01/2012	62	81.290619
156	03/01/2012	3575	5186.565208
48	07/05/2012	321	362.836524
157	16/04/2012	7	8.005637
134	19/08/2012	20	44.12
232	29/07/2012	40	49.313156
178	05/03/2012	47	61.25
63	26/03/2012	51	73.369509
153	17/06/2012	3061	6026.4
31	08/07/2012	3622	5005.038257
140	13/02/2012	9870	7713.968564
14	27/05/2012	29220	46728.26302
98	20/08/2012	297	377.653459
131	30/07/2012	172	182.591931
48	27/03/2012	348	411.801792
157	06/03/2012	3	8.01422

In order to a Rank the above data by country over all time we would probably write a simple DAX query as follows:

DEFINE
MEASURE FactSales[UnitCount]= SUM(FactSales[Units])
MEASURE FactSales[Rank]= RANKX(ALL(FactSales[CountryKey]), FactSales[UnitCount])
EVALUATE
SUMMARIZE(
FactSales
,FactSales[CountryKey]
,"ProductSalesUnits",FactSales[UnitCount]
,"Rank", FactSales[Rank]
)
ORDER BY
FactSales[UnitCount] DESC

Now you can see from the query above, in order to avoid any doubt we only rank over a single table and we have no joins to other tables at all. This works fine resulting in the data below.

FactSales[CountryKey]	[ProductSalesUnits]	[Rank]
227	91818113	1
226	21487836	2
39	9033389	3
14	7597054	4
82	4656585	5
109	3678302	6
75	3371681	7
140	2511239	8
107	1850520	9
208	1443241	10
31	1092047	11
153	997860	12
201	914827	13
59	870366	14
163	840927	15

Now assume we rank over a lot more data and wish to apply a simple CALCULATETABLE in order to filter some data out. We may write a DAX Query as follows:

DEFINE
MEASURE FactSales[UnitCount]= SUM(FactSales[Units])
MEASURE FactSales[Rank]= RANKX(ALL(FactSales[CountryKey]), FactSales[UnitCount])
EVALUATE
CALCULATETABLE (
SUMMARIZE(
FactSales
,FactSales[CountryKey]
,"ProductSalesUnits",FactSales[UnitCount]
,"Rank", FactSales[Rank]
)
,DATESBETWEEN('Date'[CalDate], DATE(2012,1,1), DATE(2012,8,31))
)
ORDER BY
FactSales[UnitCount] DESC

In this instance we are joining to a Date dimension, but that is it. The above query yields the below result.

FactSales[CountryKey]	[ProductSalesUnits]	[Rank]
227	83147875	1
226	19446567	2
39	8137335	3
14	6769390	4
82	4100168	5
109	3321496	6
75	3010890	7
140	2165554	8
107	1655472	9
208	1274740	10
31	987122	11
153	908938	12
201	853322	13
59	771677	14

That works, we get results as we would expect within seconds. Now, if instead of ranking over an integer field, let’s apply the RANKX function to a real number. In this example we get a worrying result set using the DAX as follows:

DEFINE
MEASURE FactSales[SalesValue]= FactSales[Sales Value]
MEASURE FactSales[Rank]= RANKX(ALL(FactSales[CountryKey]), FactSales[UnitCount])
EVALUATE
CALCULATETABLE (
SUMMARIZE(
FactSales
,FactSales[CountryKey]
,"ProductSalesValue",FactSales[Sales Value]
,"Rank", FactSales[Rank]
)
,DATESBETWEEN('Date'[CalDate], DATE(2012,1,1), DATE(2012,8,31))
)
ORDER BY
FactSales[UnitCount] DESC

Which outputs:

FactSales[CountryKey]	[ProductSalesValue]	[Rank]
227	84074007.25	1
226	29928143.25	3
14	10859628.74	4
39	8451588.111	4
109	7964922.769	6
82	6254219.85	6
75	4730390.37	7
107	2466064.97	9
208	1904009.18	10
140	1862708.961	11
153	1311217.35	11
22	1207366.72	13
59	1182179.95	15

Now let’s be clear, all we have done is simply change the measure from an Integer to a Float, the rest of the data is the same. You will notice that there are tie’s in the data that there should not be. Having scratched our heads for hours, rebuilt the model, re wrote the DAX, and had a number of colleagues check it over we found that when no calculate table is applied, then we get the correct answer again as follows:

DEFINE
MEASURE FactSales[SalesValue]= FactSales[Sales Value]
MEASURE FactSales[Rank]= RANKX(ALL(FactSales[CountryKey]), FactSales[UnitCount])
EVALUATE
SUMMARIZE(
FactSales
,FactSales[CountryKey]
,"ProductSalesValue",FactSales[Sales Value]
,"Rank", FactSales[Rank]
)
ORDER BY
FactSales[UnitCount] DESC

FactSales[CountryKey]	[ProductSalesValue]	[Rank]
227	92885561.31	1
226	33237033.3	2
14	12253005.68	3
39	9414266.358	4
109	8928147.606	5
82	7120811.54	6
75	5296490.13	7
107	2756500.54	8
208	2146627.18	9
140	2115750.609	10
153	1479858.53	11
22	1339255.82	12
59	1324799.98	13
105	1320651.83	14
31	1277065.779	15

So what we have learnt here is that RANKX seems to give the wrong answer, but only when ranking over real numbers, and only when we filter the data set in some way using CALCULATETABLE. Not being able to find a clear reason for this behaviour we eventually gave in and raised it with Microsoft. Having spent a week or so working it through with the great support team at Microsoft, it seems that this is a current “feature” of RANKX. It is believed to be a floating point arithmetic issue that is driven from how floating point numbers are stored. It is documented here:

http://support.microsoft.com/kb/78113/EN-US

There is also some further information documented here:

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

http://www.microsoft.com/en-us/download/details.aspx?id=4106

I have been told by Microsoft that this is the same as it is in Excel, and Analysis Services, however still does not explain why the CALCULATETABLE makes a difference. I am still working with Microsoft to see if we can get to the bottom of it. However, if precision beyond 15 significant figures within the rank is not important to you (up to 99 Billion with 2DP) this issue is very easily worked around. Take the following DAX query:

DEFINE
MEASURE FactSales[UnitCount]= FactSales[Royalty Value]
MEASURE FactSales[Rank]= RANKX(ALL(FactSales[CountryKey]), ROUND(FactSales[UnitCount],2))
EVALUATE
CALCULATETABLE (
SUMMARIZE(
FactSales
,FactSales[CountryKey]
,"ProductSalesValue",FactSales[UnitCount]
,"Rank", FactSales[Rank]
)
,DATESBETWEEN('Date'[CalDate], DATE(2012,1,1), DATE(2012,8,31))
)
ORDER BY
FactSales[UnitCount] DESC

Which now correctly outputs:

FactSales[CountryKey]	[ProductSalesValue]	[Rank]
227	84074007.25	1
226	29928143.25	2
14	10859628.74	3
39	8451588.111	4
109	7964922.769	5
82	6254219.85	6
75	4730390.37	7
107	2466064.97	8
208	1904009.18	9
140	1862708.961	10
153	1311217.35	11
22	1207366.72	12
59	1182179.95	14
105	1196551.61	13
31	1132926.109	15

You will notice that all I have done is ROUND the measure in the RANKX function to 2 decimal places, this will stop the calculation engine from ranking over an approximated value, and force it RANK on a decimal with less than 15 significant figures, which will in turn fix the ranking. Further to this, rounding the values prior to it being loaded into the model will also work, just be sure to keep below the 15 Significant figures if possible.

So in summary, it seems that the calculation engine, when using the RANKX function does not handle the float data type as we may expect. This can be easily rectified by losing some precision on the RANK measure you define. We have been informed that using the Currency function will also fix the problem. I hope this stops others going through the same pain. As and When i get any further information on this issue I will post it up.

Technorati Tags: DAX,RANKX,Tabular,SQL Server 2012,Analysis Services

Matching with Master Data Services, DQS and SSIS

Jeremy Kashel — Thu, 11 Oct 2012 16:34:37 GMT

If you have an MDM requirement to consolidate multiple sources of Master Data together into a single golden record, then you have a few different ways to achieve this on the Microsoft platform. This blog post gives an overview of the different ways that various matching methods can be used in conjunction with Master Data Services for consolidation in Master Data Management, outlining the pros and cons of each option. In summary, the options are:

Data Quality Services (DQS)
SQL Server Integration Services (SSIS)
Plus Master Data Services itself has a few (not that well known) matching capabilities

Data Quality Services

A key requirement in all but the simplest MDM solutions is that the matching/consolidation must be carried out in an automated manner, with a Data Steward alerted to take action if needed (e.g. the matching engine incorrectly matches two customers, the Data Steward opens MDS and corrects this). This scenario would be hard to achieve with DQS, as it’s possible to automate the DQS cleaning, but it’s not possible to automate the DQS matching. This is something that I’ve raised connect issues about, here and here.

If your data to be matched into MDS is not coming in on a daily basis, and you therefore need to do more ad-hoc matching to produce your golden Master Data records, then DQS could be for you. The MDS Excel Add-in will give you the capability of matching data that you import into Excel with MDS members, harnessing the power of DQS. An overview of how this works is available here.

Integration Services

SSIS has been around for a long time now and, as many of you will know, contains fuzzy matching components. With the right MDS model design, its possible to carry out a batch based fuzzy match between your master records and end up with a mapping between your the records that exist in your source systems and your MDS golden records. The rough conceptual steps to do this are:

Load the new and changed records from the source systems into a staging area.
Clean and standardise your data. This is actually something that DQS cleaning can help with.
Query your staging area to get the new records that you want to insert/update into Master Data Services.
Now the question arises, do we have an exact or close match for these records already in MDS? While the exact matches are easy to deal with, use the SSIS Fuzzy Lookup component to establish whether there are any approximate matches.
Link the source records to master records (if the match is high enough) using MDS Domain Attributes.
Carry out appropriate inserts and updates into MDS using the MDS staging tables.
Ensure that a Data Steward is alerted in some way if necessary (e.g. if the match threshold is below x% confidence). This can be done with Email or MDS Notifications, for example.

This process can run in batch overnight, with the Data Steward approving or rejecting the matches that SSIS has carried out the following morning. Whilst the above over-simplifies the process and technical work required, hopefully the process makes sense at a high level.

Master Data Services

Although you cannot feed MDS your source data and get it to automatically carry out matching for you, it does actually contain the raw components in order to do this. By this I mean the MDS database contains an assembly called Microsoft.MasterDataServices.DataQuality, which gives you a number of fuzzy matching T-SQL functions. These are called from the MDS front end when you carry out some filtering when viewing entities. Using them just for filtering in the front end really isn’t using the functions to their true capability, but thankfully you can use these functions in your own code.

You can use the MDS T-SQL functions in a similar way to the conceptual SSIS method outlined above, in order to match and eliminate duplicates. In addition, the MDS web API can also be used to carry out a fuzzy match, as mentioned in this forum post. Retrieving match candidates using a web service may be an attractive option if you’re trying to do real time MDM.

Conclusion

Essentially until it’s possible to automate DQS matching, we have a choice between SSIS and the MDS matching functions. The following e-book gives a very detailed overview of the matches that both are capable of doing. The MDS T-SQL functions are more flexible than the SSIS fuzzy components as you can choose what fuzzy algorithm you want to use, but the SSIS components let you choose between Fuzzy Grouping and Fuzzy Lookup out of the box, without having to write SQL. Although I tend find that both give very good matching results, the MDS T-SQL functions produce slightly better matches in my experience, plus give you the option of trying different algorithms to suit different scenarios.

It’s also worth mentioning that Profisee Maestro (full disclosure, we are a partner) integrates with MDS, offering its own matching algorithms. Maestro also has a front end with the ability to assist with survivorship and approval, which I think is a useful addition to MDS. Speaking of survivorship and approval, there are two options in MDS out-of-the box. The new Master Data Manager web front-end is much improved, but potentially the MDS Excel Add-In allows a bit more flexibility for survivorship carried out by a Data Steward, just due to its natural ability for filling/copying/pasting/filtering.

So overall, due to the various improvements, Master Data Services is now capable of tackling more complex MDM scenarios than in the 2008 R2 version.

Boot to VHD – Demo Environments and more

sachatomey — Wed, 26 Sep 2012 11:54:00 GMT

A few people have asked me about this recently, so I thought I’d share my approach.

Creating demo environments, particularly for the MS BI Stack, can be time consuming and a challenge, particularly when you need to take the demo with you and can’t rely on powerful, internal servers and good client internet connectivity.

A bit of history

Initially we used to have a dedicated, decently specced, Demo laptop that would be installed with all the goodies that we would need to demo. This worked until the demo needed to be in two places at once, or you needed to carry around your day-to-day laptop too.

The solution was to use the demo environment as our day to day workstation but it was massive overkill to have full blown Windows Server running SharePoint with multiple instances of SQL Server etc. and, unless you had a high spec machine, everything was a little laggy.

The next approach was to carry around a couple of laptop hard disks that you’d swap in and out depending on whether you were demoing or working. This worked well for a good while but did prevent timely demos (no screwdriver, no demo).

Then we entertained VirtualBox and Hyper-V and other virtualisation tech to run Virtual environments – this was all well and good but the primary downfall of this approach is the fact you need a really high spec machine to run both the host and the virtual environment or performance is going to be a major issue and for demos, you want performance to be good, as good as possible.

Then we discovered Boot to VHD. I’m not sure when this was first possible and I definitely believe we were late to the game but we’ve been using it for around 12 months, long enough to prove it to be a solid approach to creating and running [not only] demo environments.

Boot to VHD

The concept is easy, and “does what it says on the tin”. You create, or acquire a VHD and configure your laptop to Boot directly to the VHD.

Advantages

1) The VHD can use all the host resources. Under traditional virtualisation approaches you need to split memory and/or processors which impacts performance. So on an 8GB, 2 proc laptop traditionally you would have 4GB, 1 proc for the host and 4GB, 1 proc for the virtual environment. With Boot to VHD the virtual environment can utilise the full 8GB and both processors.

2) It’s flexible. I have a chunky external HHD containing several different virtual environments for different purposes. I can backup, swap out, replace and roll-back environments in the time it takes to copy a VHD from local to external or vice-versa. You can even share demo environments with you colleagues.

3) You always have a demo environment to hand. All it takes is a reboot to load up the appropriate environment for those spontaneous demo opportunities.

Disadvantages

1) You do need to be careful regarding disk space usage and be very disciplined to ensure you always have enough disk space available. If you are running a number of large environments there will be an element of maintenance to ensure everything always fits.

2) Without resorting to a hybrid approach, you can’t demo a distributed system working together.

Setup

So to make use of Boot to VHD, we’ll assume we already have a VHD available and ready for booting to. These can either be created manually, acquired from your internal infrastructure team, or from other third-parties.

When creating them manually I ALWAYS create “Dynamically Expanding” virtual hard disks. This way, you can actually store more VHD environments on your laptop than you would otherwise.

Although dynamically expanding disks allow you to store more environments, you will still need to ensure you have enough disk space for the disk to expand into as this will be required at the time of boot up. So, if your VHD is set to a 100GB dynamically expanding disk, (it might only be a 20GB file), but when it’s booted up, it will expand to 100GB, so you will need that space on your hard disk or the boot up will fail.

1) Copy the VHD to your laptop to a suitable location e.g. C:\VHD

2) Create a new Boot entry
Run the following at a command prompt as an Administrator:

bcdedit /copy {current} /d "My New VHD Option"

Be sure to update the label to something to help you identify the VHD – this label will appear on the boot menu when you reboot.

Note the new GUID that has been created.

3) Using the GUID created for you in the previous step and the location of the VHD, run the following three commands, one after the other

bcdedit /set {23dd42c1-f397-11e1-9602-923139648459} device vhd=[C:]\VHD\AdatisBI.vhd
bcdedit /set {23dd42c1-f397-11e1-9602-923139648459} osdevice vhd=[C:]\VHD\AdatisBI.vhd
bcdedit /set {23dd42c1-f397-11e1-9602-923139648459} detecthal on

Note the square brackets around the drive letters, these are required. If you have spaces in your path, or filename, you’ll need to wrap the path, excluding the drive letter, in quotes.e.g.

..vhd=[C:]"\VHD Path\AdatisBI.vhd"

That’s all there is to it. Reboot and you should be presented with a new Boot option and away you go.

Troubleshooting

When it doesn’t work you generally get a BSOD on boot up. To date I’ve identified two reasons for this:

1) You don’t have enough disk space for the VHD to expand (The BSOD actually does inform you of this)

2) You may need to change the SATA Mode configuration in the BIOS. Depending on how and where the VHD was created you may need to change the setting to either ATA or AHCI. If that works, you’ll have to reverse the change to reboot into your physical installation.

I’ve yet to create a portable (i.e. sharable amongst colleagues) VHD for Windows 8. I have successfully created a Windows 8 VHD but it only currently works on the laptop it was created on, this is unlike any other VHD I have created in the past. If I work out a fix, I will update this post.

Additional Information

There are a couple of extra benefits that are worth pointing out.

1) Once you’ve booted to VHD, your original, physical OS installation drive is reallocated, normally to drive D (Your VHD will assume C drive). This allows you to share files between environments, or as I do, place my SkyDrive folder on an accessible location on the original, physical drive. This allows me to have SkyDrive installed on VHDs but only have a single copy of the contents on my HDD.

2) The reverse is true too. You can attach a VHD (from the physical install, or from within another VHD) using the Disk Management tool to access, move or copy files between environments. The disk is expanded at this point so you will need enough disk space to accommodate it.

3) If disk space is a premium, you can shrink the VHD using a tool such as VHD Resizer. It doesn’t resize the physical VHD file, but allows you to reduce the size of the virtual hard disk. It also allows you to convert from Fixed to Dynamic disks and vice-versa.

4) You can remove boot entries with the following (or you can use the System Configuration tool):

bcdedit /delete {GUID}

5) I have found this approach so reliable my day-to-day Windows 7 installation is a VHD. I have not noticed any impact to performance. The only thing that I have noticed is that you cannot determine a “Windows Experience Index” when running a VHD – but I can live with that

Analysis Services Member Properties and Excel

Jeremy Kashel — Tue, 18 Sep 2012 16:40:15 GMT

When working with users who are browsing a cube using Excel 2010, a common requirement that I find is that the users want to create tabular or grid based reports. E.g. “I have a list of currencies, I want to display the currency code in an adjacent column”. Or “Here are my account codes, I want to also see the account type”.

The common way that I see users attempt this is to stack attribute hierarchies on top of one another. For example, using Adventure Works, to get the currency names and codes together, I can stack the two attribute hierarchies:

This produces the following report:

Not exactly what we want – ideally we want the currency codes in separate columns, on the same line as the name.

An Alternative Approach

One well known way to achieve this layout is to right click on the relevant member and display its member properties, whereas another is to set the layout to Tabular Form. I tend to prefer the member properties route as I think it produces slightly better reports, so I’ll be covering a simple member properties design tip shortly. First of all, here’s the sort of report that I’m able to create using member properties:

I’m able to do this because the Adventure Works Source Currency Code Attribute Hierarchy has the Source Currency (names) as a member property. Therefore, I get the following option when I right click on the currency code:

Displaying member properties will only work if the attribute relationships are set up correctly. The reason that I’m able to see the member property ‘Source Currency’ is because of the following attribute relationship:

However, if we just display the currency name, using the Source Currency attribute hierarchy and then try and right click to see the currency code, we’ll unfortunately see nothing in the ‘Show Properties in Report’ submenu. This is because the currency name is a property of the code, rather than the other way around. I’d argue that for a user it’s intuitive to get the name from the code, or vice versa, as they sit at the same level. With a few simple changes we can achieve this, essentially by adding an extra attribute to the dimension…

Within the dimension editor in BIDS, the first step is to add the new attribute. In this case it should be based on the Currency Code column that is, of course, already in use. That doesn’t matter, we want to use it twice. Therefore, I’ve created a new attribute called Currency Code and have set AttributeHierarchyEnabled = False, as there’s no need for anyone to browse this attribute. Now onto the all important attribute relationships. The attribute hierarchy that contains the names is called Source Currency. As it’s not possible to get the currency codes by right clicking on this attribute hierarchy, Source Currency is the attribute needs to be related to the new Currency Code attribute:

Finally, the end result for the user is that they now get the ability to easily get access to the currency codes by right clicking on the names:

Power View in SQL Server 2012 RTM

Jeremy Kashel — Wed, 28 Mar 2012 13:13:01 GMT

Power View has been given a couple of small changes from RC0 to the RTM release:

Firstly, you can now choose the colour of the various visualisations via the Themes section, which you’ll find on the new Styles ribbon tab:

This will allow you to change the colour for a slicer, table or card, whereas for a chart it will set the palette that gets used for the chart series. Surprisingly it seems to set the theme globally, rather than allowing you to set the colour/palette for a single object:

The other change that I’ve noticed is that the Card visualisation now has a Style option on the ribbon:

This allows the cards to be displayed without normal coloured background, with a much larger font. So potentially useful for highlighting key numeric facts:

I do really like Power View, in particular the clean look & feel, fast performance, export to PowerPoint and animations to name a few. However, even though RC0 added a lot of new features, I think it’s still missing some of the functionality offered by other reporting tools. I know that it’s marketed as a data visualisation tool rather than a reporting tool, but those lines will become a bit blurred for users. This is where Connect comes in…

One connect suggestion that I’ve created is to allow drill down in Power View charts. If you’ve used Power View to perhaps spot a trend or spike in the data, then it makes sense for you to manipulate the view to find the root cause, which I think drill down would help with. Other suggestions that I think would be useful are heat maps and allowing Power View to access multidimensional models. Please vote on Connect if you agree!

HTML Links Embedded within Text (SSRS 2008R2)

PhilKibaki — Fri, 23 Mar 2012 16:59:47 GMT

Overview

So, old news, from SSRS 2008 onwards, Reporting Services can render HTML Tags, although there are some limitations to the tags that can be used:

Hyperlink tag: <A href>
Text formatting tags: <S>,, ,
List handling tags: <OL>, <LI>,<UL>
Font tag:
Header, style and block tags: ,<H{n}>,<LI>, <DIV>, , <HN>

I’ll be talking about using the hyperlink tags to drill through or link back to the same report within a lump of text return by a dataset.

Say your dataset returns some summary text that describes the data in the report, like “Sales for the Accessories Category amounted to £#,###.## putting it at around the 11th percentile” and you would like to drill through on the Keyword “Category” only and not the whole text.

So you want

Sales for the Accessories Category amounted to £#,###.## putting it at around the 11th percentile

Instead of

Sales for the Accessories Category amounted to £#,###.## putting it at around the 11th percentile

From a lump of text returned by a dataset.

Solution

We can handle this kind of request by generating the hyperlink to the report you would like to drill through in the dataset. This is as opposed to using textbox actions in the report. There are a number of advantages of handling this in the dataset

Turn on the HTML markup type in the place holder properties for the Summary_Text Field.

HTML Markup Type Setting

Now, you need to build the link to the report you are drilling to, this is best done in the report to keep it dynamic, therefore, deploying the report to a different server or folder won’t mean you have to change any code as you are going to use the global parameters available in SSRS.

Create a hidden parameter that will hold the expression below. Alternatively, you can have this expression directly in the dataset parameter, just create a dataset parameter and in the value expression add this hyperlink expression.

Pass this in to your dataset to complete it with the missing parameters; in this case Category Name is missing.

HTML Link Expression

HTML Expression in Dataset Parameters

The incomplete generated link being passed to the dataset will look something like this:

In your dataset, complete your link with the missing parameter.

Complete your HTML Expression and Create Hyperlink

It may be worth pointing out at this stage you can also add other HTML tags to edit the look of the text. You can add bolding to the text – as in my example above, or even have some conditional formatting to colour code the text. For example green for high percentiles etc. (see the list of HTML tags available at the beginning of this post).

For this example, I am just linking back into the same report and passing in the category that has been clicked. You can of course link to a different report/Drill through.
You end up with something like this

How it will look

You can find the example RDL here. I used the AdventureWorksDW2008R2 Database.

DQS Matching Vs SSIS Fuzzy Grouping and Lookup

Jeremy Kashel — Fri, 23 Mar 2012 11:06:59 GMT

When the term fuzzy matching comes up in the Microsoft world, it’s natural for anyone who’s used the BI stack to think of SQL Server Integration Services (SSIS), due to the fact that it has both Fuzzy Grouping and Fuzzy Matching components. Therefore, when using the matching in Data Quality Services (DQS) the other day, I thought it might be worth contrasting the matching capabilities in the two products.

Overview

The SSIS Fuzzy Grouping transformation is typically used on a set of data containing duplicates, as it will operate across all of the rows, grouping similar rows together based on fuzzy comparisons, with a view to eliminating duplicates. In contrast, the Fuzzy Lookup transformation takes a value in the SSIS pipeline and uses fuzzy matching to match the input value against a set of clean reference data in a database.

The matching in DQS takes a set of data and groups the data into clusters, based on a number of rules and other factors, again with a view to removing duplicates. It’s therefore only directly comparable against the SSIS Fuzzy Grouping, rather than the Fuzzy Lookup.

Test Scenario

For the purposes of this blog post, I’ve got a sample query from AdventureWorksDW, taking data from the DimCustomer table. I’ve unioned all that data with a few sample records that contain typos. E.g. I’ve got 'Pamela Fernndez' misspelt for one customer and '4610 Pinto Rd' instead of ‘Road’ for another. I’m going to attempt to de-duplicate data in SSIS first and then do the same thing in DQS to see if there is any difference.

SSIS Fuzzy Grouping

As shown below, I’ve just got a very simple OLE DB source passing some data into a Fuzzy Grouping, then I’m going to examine the data via a data viewer:

The columns that I’m passing into the Fuzzy Grouping for the Customers are Full Name, Address Line 1, Gender and City just for the purposes of this blog post. I’ve configured the Fuzzy Grouping transform to carry out Fuzzy Matching on Address Line 1 and Full Name, as these are the most inconsistent (intentionally) across my made up data. I’ve also dropped the threshold right down to 0.40 to see a full range of matches that we may or may not get:

I’ve filtered the data for customers with a surname of Fernandez or a first name of Pamela, which includes two made up records. When I run the package, the data viewer produces the following results:

The _score column has a high score of 0.8245935, which is based on the similarity scores of FullName and AddressLine1. There are 3 records included in the cluster, including someone called ‘P Fernandez’ who is male. Although that’s been matched, it’s only because I decreased the threshold, plus it can easily be excluded by adding an exact match for Gender, as shown below:

DQS Matching

So overall SSIS copes well with the test scenario, how does DQS compare?

After creating a knowledge base, creating domains and carrying out knowledge discovery, it’s possible to use DQS to create a Matching Policy. The Matching Policy involves building a number of rules that determine how the data matches. The starting point in creating a matching rule is determining which domains you want to match on and whether they should be matched using the fuzzy algorithms (similar) or matched exactly:

So not too dissimilar to SSIS. You then choose to give each domain a percentage weight, which must add up to 100%. For example, if you give Full Name a weight 30% and, when run, its match score is 61% accuracy, then you have Address Line 1 at a 70% weight and a 45% accuracy, then an overall match score for a row will be (0.3 * 0.61) + (0.7 * 0.45) = 49.8% match score.

Starting off with a 50% weight on the two domains, I get the following results when operating on the same data:

As with SSIS, it hasn’t got it quite right, but using the Gender domain will help. What I quite like in DQS is that I can flag a domain as being a pre-requisite:

Flagging a domain as a pre-requisite means that the domain will be used to eliminate matches, but will not contribute to the matching score. If I wanted it to contribute to the matching score I would just have to uncheck pre-requisite and give it a weight. Running the matching again gives the following results:

So the duplicate is correctly mapped with a score of 75%. Drilling down on the match was achieved is quite nice in DQS also:

This brings me to an interesting observation about the matching. I originally had my dummy record with a value of “3 Pierr Demoulainn” for the address. So in addition to having a typo in the second name and missing the word “Rue”, my other test also missed the second e out of the word “Pierre”. If I run my test data with these values, I get no matches at all. The lack of the second e in “Pierr” seems to have made all the difference. I can get it to match if I give a lot of weighting to Full Name. If I do this, we see the following results:

Address Line 1 has been given a score of 0%. Making the same change to my test data and then running in SSIS gives different results. The similarity for the address drops from 0.81 to 0.75, but it certainly doesn’t drop to zero. Although it will depend massively on your own data, the matching in DQS seems a bit more stringent. This can be mitigated by using more domains for the matching, rather than the simple 2 or 3 domains that I’ve used. To back that statement up, using a composite domain on both Address Line 1 and City does yield a strong match (71%) for the same data.

Conclusion

The rule based matching in DQS gives a lot of flexibility on how to weight matching across different domains. Coupled with the knowledge base components that interact with the matching, such as cleaning and Term-Based Relations, DQS has more features specifically aimed at matching than SSIS. It all depends on what sort of data you’re working with, but in theory, as you maintain the knowledge base over time, DQS should give you strong matches based on the knowledge you’ve given it.

However, there are some drawbacks – it’s a shame that the matching can’t be automated (please vote for my connect suggestion if you agree). SSIS by its very nature can be automated, meaning the matching in SSIS will be more suitable for a lot of implementations. Secondly, I’d like to be able to match external records against correct values already in the knowledge base, rather than just doing a fuzzy grouping on the external records. I’m a bit surprised DQS can’t do a fuzzy lookup, maybe this will change in V2. Finally, as I’ve shown above, some of the matching results are a little unexpected, whereas in SSIS they were good pretty much straight away. To be fair to DQS this could probably be mitigated with a better choice of matching fields and also running cleaning against a fully populated knowledge base beforehand.

Mean, Median and Mode in MDX

Jeremy Kashel — Mon, 19 Mar 2012 11:25:28 GMT

I recently delivered an Analysis Services training course to a client who were a bit surprised to learn that the standard list of Aggregation Functions for an Analysis Services measure doesn’t include Mean, Median and Mode. My answer was of course that all three can delivered using MDX, so I thought it might be a good blog post to show how it’s done.

Before I begin, just a quick refresher for anyone who needs it, the following article seems to be a good reference on the difference between Mean, Median and Mode. Essentially Mean is the average, Median is the middle value, whereas Mode is the value that occurs the most.

Mean and Median

The Mean and the Median are both relatively easy to do in MDX as there are built-in functions for both AVG() and Median(). As they’re well documented here and here, I won’t spend too long covering them. In the following MDX query I’m producing the Mean and the Median of the product sub categories in Adventure Works:

WITH 
MEMBER  [Measures].[Mean] AS AVG([Product].[Subcategory].[Subcategory].Members,
        [Measures].[Internet Sales Amount])

MEMBER  [Measures].[Median] AS Median([Product].[Subcategory].[Subcategory].Members,
        [Measures].[Internet Sales Amount])

SELECT  {[Measures].[Internet Sales Amount], [Measures].[Mean], [Measures].[Median]} ON 0,
        NonEmpty([Product].[Subcategory].[Subcategory].Members, [Measures].[Internet Sales Amount]) ON 1
FROM    [Adventure Works]
WHERE   ([Date].[Date].&[20070727])

If you run the query and copy the data out to Excel you’ll see that it matches the result of Excel’s Median and Mode functions.

Mode

Mode is harder to achieve as there’s no built in MDX function. Fortunately, Excel is on hand to help out, as Analysis Services allows you to use some of the Excel functions in MDX. There are warnings that come with this approach, as you need to have Excel installed on the server and there can also be performance problems. But, if you’re comfortable with these caveats, then the Excel mode function may be an option for you.

If you do use the Excel Mode function then there are a few things to watch out for. Firstly, you’ll have to use the MDX SetToArray() function to pass the set in the format that Excel expects. Secondly, you may get an error returned with the description: “#Error The following system error occurred: Invalid flags.” This is due to the data type of the Measure – it seems that Currency measures are not supported by this function. Therefore, I’m using Cdbl() on Internet Sales Amount to get this to work:

WITH 
MEMBER  [Measures].[Demo] AS Cdbl([Measures].[Internet Sales Amount])
    
MEMBER  [Measures].[ExcelMode] AS
        Excel!MODE(SetToArray(NONEMPTY([Product].[Subcategory].[Subcategory].Members, [Measures].[Demo])
        * [Measures].[Demo]))

Applying this to the example query I’ve got above, the mode returned is 8.99, as its the only number that appears twice.

Bi Modal Result Set

There is a bit more complexity to Mode in some situations, essentially as you can get a bi-modal result set - where there is more than one value occurring the most. E.g. the mode of 1,2,2,3 is just 2, but the mode of 1,2,2,3,3,4 is both 2 and 3 – so it’s known as bi-modal.

The Excel Mode function won’t help in this scenario, as it will just return one of the mode values. Interestingly Excel 2010 does support this, with the Mode.Mult() function, but I’ve been unable to get this to work, presumably as the Excel function returns an array.

All is not lost though, it’s possible to produce mode using MDX. First of all, the numbers that I’m operating on are shown below. As you can see, there are two sets of two numbers that are the same:

As the following forum thread shows, mode can be achieved using MDX. I’m adapting that approach to give the following MDX:

WITH 
--Produce a result set that will guarantee bi modal results
MEMBER    [Measures].[Demo] AS
        CASE WHEN [Product].[Subcategory].CurrentMember IS [Product].[Subcategory].&[31]  THEN 65.91
        ELSE Cdbl([Measures].[Internet Sales Amount]) END

--Count how often each value appears
MEMBER [Measures].[ValueCount] AS 
SUM( 
    Union([Product].[Subcategory].CurrentMember.Level.Members,
        {[Product].[Subcategory].CurrentMember} AS Currentsub)
    , IIF(([Product].[Subcategory].CurrentMember, [Measures].[Demo]) = 
        (Currentsub.Item(0).Item(0), [Measures].[Demo]), 1, null)
)

--Only get the items that appear the most
SET [MaxModes] AS 
    ORDER(FILTER(NONEMPTY([Product].[Subcategory].[Subcategory].Members, {[Measures].[Demo]}),
    [Measures].[ValueCount] = MAX(NONEMPTY([Product].[Subcategory].[Subcategory].Members, [Measures].[Demo]), 
        [Measures].[ValueCount])), [Measures].[Demo], ASC)

SELECT   {[Measures].[Demo], [Measures].[ValueCount]} on 0,
        [MaxModes]
        --Filter out the duplicates 
        HAVING [MaxModes].CurrentOrdinal = 0 OR [Measures].[Demo] <> 
            ([Measures].[Demo], [MaxModes].Item([MaxModes].CurrentOrdinal - 2)) ON 1
FROM    [Adventure Works]
WHERE    ([Date].[Date].&[20070727])

This gives the following correct result set, assuming we just want the two bi modal values:

An alternative is to use an MDX Filter() or HAVING clause to just display all the sub categories that have the mode values, which would just require a small modification to the above code.

I’ve not used these approaches with big data volumes etc, but they should at least give you a few options if you’ve got to do these sort of calculations in your own environment.

Power View Default Field Set

Jeremy Kashel — Thu, 23 Feb 2012 11:57:10 GMT

Here's another quick tip when creating data models for use with Power View - Default Field Sets can be created in both Tabular SSAS and PowerPivot that give the user a shortcut for automatically adding multiple fields to a report.

As an example, here's a screenshot of the Default Field Set in PowerPivot - this will tell Power view that the following fields should be automatically selected when the table is added to a Power View report:

I'd seen this in the documentation a while back, but hadn't actually used it, nor could I see a way to select the table. In fact, although there's no checkbox, it's just a simple single click on the actual table name, as I’ve highlighted below:

When you click on the table name Product above, you will now get the following fields automatically added to a table visualisation:

So not exactly rocket science, but worth doing! Here's how to set up the Default Field set for PowerPivot and Tabular Analysis Services.

PowerPivot Settings for Power View

Jeremy Kashel — Mon, 20 Feb 2012 16:30:59 GMT

I’ve been using both PowerPivot and Power View quite a bit recently and, in addition to the post I did a while back, have made a few further observations on getting the two to play nicely together.

Building an Example PowerPivot Model

For this post I’ve created a very very simple PowerPivot model, based on the Adventure Works Internet Sales Fact table and its related tables. What this means is that after the model is built, I can build a variety of Excel reports that summarise the measures in the FactInternetSales Adventure Works fact table, such as Sales Amount, Tax Amount or Order Quantity. In the example below I’ve built a very basic report showing Order Quantity by year:

On to Power View

Having already published the workbook to SharePoint, my task now is to create a Power View report, which can be done via the PowerPivot Gallery. If wanted to try and create a visual version of the above report in Power View (E.g. a graph), my steps would be to do first of all drag Calendar Year into the report, then Order Quantity. However, when doing this recently, the result wasn’t quite what I expected:

Order Quantity has not been summed at all, which is the opposite behaviour to that of PowerPivot. Consequently I can’t create a graph visualisation, because Power View thinks I don’t have any measures in my report:

Yet if we contrast this to measures such as Internet Sales Amount or Tax Amount, they all work fine. Upon looking at the field list for Internet Sales, we will see that Order Quantity is missing the sum symbol, as Power View has not interpreted it as a measure:

We can get around this in Power View with a bit of dragging and dropping, but we may as well look why it’s happened in the first place. The reason for this is the way in which the PowerPivot import wizard interprets SQL data types. The OrderQuantity column in the FactInternetSales SQL table has a data type of Smallint, which will not be summarised by default in Power View. Decimal and Money types are fine, hence why Tax Amount and Sales Amount above look ok.

Rather than changing the data types, we can force Power View to interpret the column as a measure by going to the PowerPivot advanced tab and clicking on Summarize By. If the Summarize By value is changed from Default to Sum, then Power View will interpret the column as a measure:

If we republish the model and then go back to Power View, we will see quite a difference with the same actions as before. First of all the field list is looking correct:

Secondly, only dragging Calendar Year and Order Quantity will now give the following correct results:

Now that we have a measure, it means that we can now change the table into a visualisation of our choice:

Power View is the kind of product that power users are hopefully going to find intuitive, but we can make things even easier for users by making small changes like this.

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.