MDX and 1.#INF (Infinity)

There’s a knock at the door, its one of your users asking what the 1.#INF value is showing in today’s report.

The official reply is “There’s a problem with the server” … this will buy you enough time to race back to your SSAS solution and make the following adjustment to your calculation and redeploy before the user gets back to their desk… We need to check if Measure B is 0 (to make the calculation null) otherwise carry out the division as normal.

IIF(
Measures.[Measure B]=0,null,
Measures.[Measure A] / Measures.[Measure B]

)

Scenario explained here …

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

A twist on this scenario here, the solution involving checking if the parent member is null …

Thomas I Blog

Analysis Services DependsonDimension Property

I have never used the Analysis Services DependsonDimension property so I was intrigued when trialling a few configurations dealing with custom dimensional security – what it actually does. Looks like Chris Webb is all over this so I won’t cover in detail here but suffice to say the property only really gives the Aggregation Design and Usage Based Optimisation Wizards something to ponder when two dimensions are closely related.

Chris Webb Covers DependsonDimension Property

Is also covered in brief here with more explanation – “If a dimension A has a strong correlation to dimension B, then you should set A “depends on” B. This way, the aggregation design algorithm won’t consider both as mutually independent dimensions. For example, Fiscal Time is dependent on Calendar Time.”

http://technet.microsoft.com/en-us/library/microsoft.analysisservices.dimension.dependsondimension(v=sql.90).aspx.

Indexed Views are Good for Reporting…Inserts please turn away now…

There is a lot of red tape involved in creating indexed views – but the bottom line is they are worth it, particularly for reporting.

If you have ever created Indexed Views before you most definately would have come across an error or two throughout the process. Before you put this into the too hard basket consider the following scenarios where the benefits of fast query performance will be realised…

Applications that benefit from the implementation of indexed views include:

Joins and aggregations of large tables
Repeated patterns of queries
Repeated aggregations on the same or overlapping sets of columns
Repeated joins of the same tables on the same keys
Combinations of the above

Indexed views are therefore likely to benefit in the following scenarios :

Decision support workloads
Data marts
Data warehouses
Online analytical processing (OLAP) stores and sources
Data mining workloads

Ok lets look at some basics. The first thing we need to do is create our view. We can then create one or more indexes on our view starting with a clustered index.

To create the view we will use a similar syntax to creating a regular view except we will use the WITH SCHEMA BINDING Option see Schema Binding …

e.g.


CREATE VIEW dbo.vw_SomeView WITH SCHEMABINDING

AS

SELECT colKey, col2, col3

FROM dbo.Table1

Note that any tables mentioned in the SELECT statement of your view must be in the two part owner.object name format. There are a heap of caveits that could bring you down at this point which would prevent you from creating the view. One of which is you should not use deterministic functions. E.g. DateAdd returns the same value for a set of parameters each time, whereas getdate() does not i.e don’t use getdate() in your view. CONVERT(datetime, somedate) is non deterministic as it can return different results when different values are provided for the style parameter….consider CONVERT(datetime, somedate,103) as it is deterministic – see Using Convert Within an Indexed View. Note in the example Create View statement above there is explicit specification of columns in the select clause (i.e. no select * …), the select is on a base table not other views and if joins were used, OUTER JOINS would be avoided. The full design considerations for indexed views are outlined here http://msdn.microsoft.com/en-us/library/ms191432.aspx.

Once we have successfully navigated the design guidelines and have successfully created our view, now it is time to create the indexes. We create the index as we would on a table. The first index we need to create on a view is a unique clustered index. We can then create additional none clustered indexes as required.


CREATE UNIQUE CLUSTERED INDEX ci_vw_Table1 ON dbo.Table1 (colKey)

Finally Aaron Bertrand runs through a practical example of using Indexed Views and discusses the good and dark sides of indexed views in this nice example Redundancy is No Evil.

MDX Samples – Helping our Business Users see Custom Groupings of Products

One of the more common citations from the desk of the business user, is something that starts with .. “I want to see …”…

Creating a data model that fits the business requirements like a glove might do for now, but the business are great at thinking up new ways of categorising their data. Top items, these items, those items, those items excluding top items, the list goes on. Whilst some I.T. folk might view business requests as “annoying”, we in the B.I. space love seeing out business users take hold of our collaborations and running with them.

The following snippet demonstrates (using Adventureworks), how to group product lines into 3 custom groupings : Top 2 items, All Other Items, and All Items. These techniques show you how to include specific line items of interest in your report, whilst grouping the remaining items you are not so interested in, into one total e.g. Other Products … From the Martin Mason Blog …


WITH SET [Top Items] AS

Generate(

{ [Product].[Category].[Category].Members },

TopCount(

EXISTING [Product].[Product].[Product].members,

2,

( [Measures].[Reseller Sales Amount] )

)

)

MEMBER [Product].[Product].[All].[All Other Products] AS

Aggregate( { EXISTING { [Product].[Product].[Product].Members } - [Top Items] } )

SELECT        {

[Measures].[Reseller Sales Amount]

} ON COLUMNS,

{

{ [Product].[Category].[Category].Members }

* {

[Top Items],

[Product].[Product].[All Other Products],

[Product].[Product].[All]

}

} ON ROWS

FROM        [Adventure Works]

Alternatively lets say the business wants to see all of the products that are listed in the current 20% catalogue sale (20% off all Accessories and Products), we can display the line items for all products in the Accessories and Products categories, then show all other items grouped into one total…


WITH SET [Catalog Accessory Products] AS

'Filter([Product].[Product].[Product].Members,

(InStr(1,[Product].[Category].CurrentMember.Name, "Accessories") <> 0)

)'

SET [Catalog Clothing Products] AS

'Filter([Product].[Product].[Product].Members,

(InStr(1,[Product].[Category].CurrentMember.Name, "Clothing") <> 0)

)'

MEMBER [Product].[Product].[All].[All Other Products] AS

Aggregate(

{ EXISTING

{ [Product].[Product].[Product].Members }

- {[Catalog Accessory Products] + [Catalog Clothing Products]} } )

SELECT        {

[Measures].[Reseller Sales Amount]

} ON COLUMNS,

NONEMPTY({

{ [Product].[Category].[Category].Members }

* {

[Catalog Accessory Products],

[Catalog Clothing Products],

[Product].[Product].[All Other Products]

}

}) ON ROWS

FROM        [Adventure Works];

Convert string with $ and , 000’s separator to a number

You have imported some data from a file or Sharepoint list and the numbers have come across as strings, in their display format. Unfortunately for you this means if you want to perform some arithmetic or sorting on this column, you will need to convert to a number and remove the formatting characters like $ , -.

If you do try to perform some arithmetic on this column directly, you will most likely get the error … “Error converting data type varchar….”.

Now there is nothing like a clean datamart to store this data, however the following example demonstrates how to perform this function in an adhoc query. The code sample searches the varchar column aptly named “Varchar Actual ($)” and replaces any symbols containing the characters $ , – and the space character with empty string…allowing the string to be successfully cast to a number…


CAST(REPLACE(REPLACE(REPLACE(RTRIM(LTRIM

([Varchar Actual ($)])),'$',''),',',''),'-','') as float)

AS [Float - Actual ($)]

If some of your values contain valid negative you will of course need to preserve the leading “-” character.

Reference …

Social MSDN Thread

Cast and Convert

Cast and Convert Basics

Some REPLACE basics with Bru ! Have a Cuppa with Bru

Getting Data From Active Directory and who is Troy Farrell?

With integration ever on the increase, leveraging data stored in the Active Directory is proving to be an increasingly common requirement. Typical scenarios may include efforts to build a configuration database through to building a schema to support row level security for reporting.

Access to active directory data can be retrieved as simply as a linked server configuration and data access using OpenQuery. Brendan Tompkins provides a simple guide to achieving this but keep in mind the importance of getting the adsdatasource and port correct.

Brendan Tompkins A.D. Query Example

Some Ldap query examples are provided here – see Examples.

If linked servers are not your thing or you have problems accessing through the linked server then there is a tool that is installed with the Lightweight Directory Services Role for Windows 2008 called “csvde.exe” see CSVDE. This tool provides a command line interface to the active directory server to extract data in csv format using various object queries.

If you are using a non Microsoft Ldap v3 compliant directory server keep in mind the csvde.exe tool will still work, however you may run into problems. For a start rows may be paged in 500 row lots and csvde cannot page through the data automatically. Secondly, the columns may return to the csv file out of order when returning from the LDAP v3 compliant LDAP server (note this is unrelated to the –l behaviour as design which can return columns out of order if you specify specific attributes in your query against a Microsoft Active Directory Server). As a workaround you can get a Powershell guru like Troy Farrell to write a script to rearrange the data in the csv file.

There are third party tools available for a fee that allow you to interrogate and create command line extracts for LDAP v3 directory servers however be sure to test these tools thoroughly to handle the issues around data paging and out of order columns.

For non Microsoft LDAP servers that utilise single source of the truth for the directory in an ODBC or OLDEB database, you also have the option of bypassing all of the above and extracting that data directly from the LDAP database using SQL Server Integration Services.

Happy A.D.

Add a Primary Key Constraint to an Existing Table

Here are some handy snippets for how to create a primary key on an existing table.

To add an identity column on the fly and then make it a primary key …

ALTER TABLE dbo.Table1 ADD
-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY;

To add a primary key to an existing column that is not null – first set the nullable column to NOT NULL …

ALTER TABLE dbo.Table1
ALTER COLUMN column_b int NOT NULL;

Now to add a primary key to an existing column that is not null …

ALTER TABLE dbo.Table1 WITH NOCHECK
ADD CONSTRAINT column_b_pk PRIMARY KEY CLUSTERED (column_b)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON);

Now you have a primary key on this table it is easy to create a Foreign Key Constraint against it.

ALTER TABLE [dbo].[Table2] WITH NOCHECK ADD CONSTRAINT [FK_column_b] FOREIGN KEY(column_b)
REFERENCES [dbo].[Table1] (column_b)

Reference MSDN.

Publishing Intuitive Top B’s for Every A for Excel users

One of the most common reasons why we would go to the trouble of modelling our data in a cube is performance. However self service access to the data is one of the other huge benefits that allows us to enrich the user experience and free up our workloads.

When a list of dimension members heads into the hundreds or thousands, users naturally seek out the Top N members, or more specifically, give me the Top 10 B’s for every A.

In the following example we want to see what the Top 10 Selling Products are for Every Supplier. If we attempt to create a dynamic set called “Top N Suppliers”, and then browse the model in Excel, we will find the cross join occurs between only 10 suppliers and our product members.

Not only is this not intuitive for the end user but does not meet the requirement adequately either.

Creating a more intuitive set called “Set1 – Top 10 Suppliers for every Product – Sales $” allows us to publish this is workable self service solution.


WITH

SET [Set0] as [Product].[Product Description].Children

SET [Set1] as generate([Set0], crossjoin([Set0].Current, TopCount(NonEmpty([Supplier].[Supplier Description].Children, [Measures].[Sales $]), 10, [Measures].[Sales $])))

SELECT

[Measures].[Sales $] on columns,

NON EMPTY [Set1] on rows

FROM [Product Sales Data]

How about the Top 5 Suppliers for the Top 5 Selling Products ?

SET [Set0] as Head(ORDER(NonEmpty([Product].[Product Description].Children, Measures.[Sales $]),Measures.[Sales $],BDESC),5)

SET [Set1] As generate([Set0], crossjoin([Set0].Current, TopCount(NonEmpty([Supplier].[Supplier Description].Children, Measures.[Sales $]), 5, Measures.[Sales $])))

We can make this even more intuitive by adding the Time Period, in this case, Last 5 years. A set called “Set2 – Top 10 Suppliers for every Product – Sales $ (Top 2 Performing Years)” provides instant access to the top time periods as well. This could work equally well for months, weeks or days.


WITH

SET [Set0] as [Product].[Product Description].Children

SET [Set1] as generate([Set0], crossjoin([Set0].Current, TopCount(NonEmpty([Supplier].[Supplier Description].Children, [Measures].[Sales $]), 10, [Measures].[Sales $])))

SET [Set2] as generate([Set1], crossjoin([Set1].Current, TopCount([Time].[Year].Children, 5, [Measures].[Sales $])))

SELECT

[Measures].[Sales $] on columns,

NON EMPTY [Set2] on rows

FROM [Product Sales Data]

Richard Lees also has another good angle on this using Generate and order.

Thanks to Chris at Stackoverflow.

Member Does Not Exist in MDX Calculations

Granted if an MDX calculation involves a specific named member, then this member should ideally exist in the datamart and be processed in the cube. Nonetheless, there may be the occasion where this isn’t the case and when browsing the calculated measure in your OLAP client will get a #Value! error.

The following code snippet provides an example for how to handle missing member. The approach uses a SET expression to look for the missing member followed by a simple IIF expression in the CREATE MEMBER to determine if this member is missing and how to handle it.

Noted, there are numerous more performance savvy and sometimes cumbersome ways to solve this problem in Analysis Services, however the simple approach is taken here to get you up and running. This will buy you some time to come up with a more sophisticated approach.

/* PreExempt Missing Member */
Create Set CurrentCube.[MissingMemberSet] As
iif(IsError(StrToMember("[Dimension].[Hierarchy].&[MEMBER]")),
{}, {[Dimension].[Hierarchy].&[MEMBER]});

Create Member CurrentCube.Measures.[Calculation on Missing Member]
AS
IIF ([MissingMemberSet].Count > 0,
([Dimension].[Hierarchy].&[MEMBER],Measures.[SomeMeasure]),
0
)
,
FORMAT_STRING = "Currency",
LANGUAGE = 1033,
NON_EMPTY_BEHAVIOR = { [SomeMeasure] },
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Display Folder'  ;

Thanks to Dr. Nick and various posts by Deepak Puri for the inspiration.

POP-BI

Popular Business Intelligence

Category Archives for Model (Dimensional Modelling)