Adatis

Adatis BI Blogs

Debug MDX with Generate

On occasion you may find that your MDX queries don’t quite give you the result that you expect, which sometimes may be due to the way in which a certain bit of MDX has changed the context of part of your query. This can sometimes be tough to figure out, especially with functions like Sum() or Filter(), whereby we are looping over a set and then only returning the end product. For example, take the following query, where I’m trying to find out the count of UK male customers who have bought Road Bikes in both the current and the previous year: WITH MEMBER [Measures].[Repeat Customer Count] AS SUM( [Customer].[Customer].[Customer].Members, IIF([Measures].[Internet Sales Amount] <> 0 AND ([Measures].[Internet Sales Amount], [Date].[Calendar Year].PrevMember) <> 0, 1, NULL) ) SELECT {[Measures].[Repeat Customer Count]} ON 0, [Date].[Calendar Year].[Calendar Year] ON 1 FROM [Adventure Works] --Road Bikes, UK, Male WHERE ([Product].[Subcategory].&[2], [Sales Territory].[Sales Territory Country].&[United Kingdom], [Customer].[Gender].&[M]) It returns the following result set, which I know to be incorrect: Unlike debugging either of the axes, where of course we can see what gets returned, debugging examples such as the above might be difficult. The MDX Generate function can help here, as it works in a similar way to Sum(), by iterating over a set and then evaluating an argument once per iteration. The idea is that Generate will build us a new set, by using the looping context of the first set that you pass it. In this case, however, we can use it to return us a concatenated string based on our input set. What I’ve done here is to use the CurrentMember of attributes that I think might be causing problems (Customer, Gender) and then just print them out as a string in order to see if the attributes have the context that I expect. Therefore I’ve replaced the ‘1’ in my original query with the string of: " | " + [Customer].[Customer].CurrentMember.MEMBER_CAPTION + ", " + [Customer].[Gender].CurrentMember.MEMBER_CAPTION The full query and results then become: GENERATE( [Customer].[Customer].[Customer].Members, IIF([Measures].[Internet Sales Amount] <> 0 AND ([Measures].[Internet Sales Amount], [Date].[Calendar Year].PrevMember) <> 0, " | " + [Customer].[Customer].CurrentMember.MEMBER_CAPTION + ", " + [Customer].[Gender].CurrentMember.MEMBER_CAPTION, NULL) ) SELECT {[Measures].[Repeat Customer Count]} ON 0, [Date].[Calendar Year].[Calendar Year] ON 1 FROM [Adventure Works] --Road Bikes, UK, Male WHERE ([Product].[Subcategory].&[2], [Sales Territory].[Sales Territory Country].&[United Kingdom], [Customer].[Gender].&[M]) I can spot straight away that female customers have been returned in 2007, which is due to the fact that sets specified inside the WITH MEMBER section are not evaluated in the context of the slicer. In this case the problem can be solved by using the EXISTING keyword inside the Sum, so that the customers get sliced by the gender attribute: SUM( EXISTING [Customer].[Customer].[Customer].Members, IIF([Measures].[Internet Sales Amount] <> 0 AND ([Measures].[Internet Sales Amount], [Date].[Calendar Year].PrevMember) <> 0, 1, NULL) ) This won’t be applicable in all MDX debugging scenarios, but hopefully it will help someone out.

Managing SSAS Named Sets with Master Data Services Collections

Master Data Services Collections are probably not the first feature that come to mind when you think of the MDS product. The hierarchies and member grids tend to be the core functionality, but as this post will hopefully show, MDS Collections are useful also. Collections are essentially managed lists of members that can come from multiple different MDS explicit hierarchies, or also from another collection. The idea is that this “subset of members” can be maintained in MDS by a business user and then fed to external applications for reporting or other purposes. Analysis Services Named Sets One example of how collections can be used is to maintain Analysis Services named sets. Some named sets, such as a Top 50 Customers, don't require any maintenance, as it’s just the top 50 of all customers, based on a particular measure. On the other hand, sometimes named sets can be static lists, e.g. a list of core products that the user wants to see for reporting. In the latter example, if a user wants the definition of a named set to change, they have to get IT to change the MDX script. MDS collections can help by allowing the user to control the named set definition, reducing the burden on IT. Example Scenario  Here’s an example of how this is done. First of all, the end game is that I have a named set in Analysis Services that is currently just for 3 products. Therefore, a user can easily drop this set into an Excel report to get quick access to the products that are important to them: So the challenge is that we need to find some way of extending this named set without doing it manually. This is where MDS starts to come in, so using the sample Product model that comes with MDS, I’ve created a new collection called Favourite Products, as shown in the image below: If I go and edit the Favourite Products collection in MDS, then I can drag and drop more products into this collection, or remove some existing members. In this case, I’ve chosen to add two new products: C#, AMO and the MDS API So the named set represents the target that we need to get to, whereas the MDS collection that’s shown is the source. To get the contents of the MDS collection to update the named set, one way of doing it is to use the MDS API to pick up the collection members, and then to use AMO in order to write the named set into the MDX script. I’m just doing this in a C# windows application, but you could do it via a batch process, such as SSIS. For this post I’m just going to show the code, so here goes: This post is already starting to feel too long so I’m not going to show the basics of the MDS API. For that, take a look at a good posting by the MDS Team blog here. Also, as anyone who knows me will no doubt agree, I don’t tend to get involved in doing C#, so don’t consider this to be production ready! It should give you an idea of the basics though. Anyway, assuming that we’re now connected to the MDS Web Service, I’ve created the following method that will return the members from the collection: private HierarchyMembers ObtainHierarchyMembers(string entityId, string hierarchyId, string modelId, string versionId) { HierarchyMembersGetRequest request = new HierarchyMembersGetRequest(); HierarchyMembersGetResponse response = new HierarchyMembersGetResponse(); request.HierarchyMembersGetCriteria = new HierarchyMembersGetCriteria(); //State that our hierarhcy type is a collection and that we want collection members request.HierarchyMembersGetCriteria.HierarchyType = HierarchyType.Collection; //Pass in the key search criteria to identify the correct collection in MDS request.HierarchyMembersGetCriteria.ParentEntityId = new Identifier { Name = entityId }; request.HierarchyMembersGetCriteria.HierarchyId = new Identifier { Name = hierarchyId }; request.HierarchyMembersGetCriteria.ModelId = new Identifier { Name = modelId }; request.HierarchyMembersGetCriteria.VersionId = new Identifier { Name = versionId }; request.HierarchyMembersGetCriteria.RowLimit = 50; request.International = new International(); OperationResult result = new OperationResult(); //Return the hierarchy members from the service return service.HierarchyMembersGet(request.International, request.HierarchyMembersGetCriteria, out result); } Before we use the above method, we need to connect to SSAS using AMO. That can be done quite easily with the following code: Server server = new Server(); string connection = "Data Source=.;Catalog=Adventure Works DW 2008R2;"; server.Connect(connection); After we’ve done all the usual error handling associated with connecting to a database, we need to pick up the SSAS database and cube: Database database = server.Databases["Adventure Works DW 2008R2"]; Cube cube = database.Cubes["Adventure Works"]; Now we’re ready to call our ObtainHierarchyMembers method, before looping through it to build the named set: StringBuilder mdx = new StringBuilder("\n//Auto generated named set at " + DateTime.Now.ToString() + "\nCREATE SET CurrentCube.[Favourite Products] AS {"); int count = 1; //Loop through the collection to build the mdx foreach (ParentChild pc in hm.Members) { //Add the members to the MDX string //This references the member by name //It would be possible to reference by member key, but would require more work mdx.Append("[Product].[Product].[" + pc.Child.Name + "]"); if (count < hm.Members.Count()) { mdx.Append(", "); } count++; } mdx.Append("};"); Now we need to insert the named set in the correct place within the existing MDX script, bearing in mind it could already exist: string currentScript = cube.MdxScripts[0].Commands[0].Text; //Find the correct place to insert the named set within the MDX script: int start = currentScript.IndexOf("\n//Auto generated named set at"); int end = 0; StringBuilder newScript = new StringBuilder(currentScript); if (start != -1) { end = currentScript.IndexOf(";", start); //If the named set already exists, remove it newScript.Remove(start, end - start + 1); } else { start = currentScript.Length; } //Insert the named set in the correct place newScript.Insert(start, mdx.ToString()); //Update the cube's MDX script cube.MdxScripts[0].Commands[0].Text = newScript.ToString(); Finally we just need to update the cube in order to write the MDX back to the cube: //Call the update methods to update the cube cube.MdxScripts[0].Update(); cube.Update(); User Reports Now that the cube has been updated, if the Excel report is refreshed, then the two new products will appear in the list: Summary Controlling SSAS named sets like this won’t be for everybody. I’ve certainly worked in a few clients where strict process has to be followed to update this sort of thing, but I can think of other companies that I know where this would be really useful. Managing Analysis Services named sets is just one use for collections. Another example might be managing default multi-select parameters for SSRS reports. As collections are just lists of members that can be extracted easily, what you do with them is up to you!

Master Data Services Training in the UK

This is just a quick post to announce a range of SQL Server training courses, organised (and in some cases delivered) by Chris Webb. To start off there’s a SQL Server course delivered by Christian Bolton in December, followed by an Analysis Services course delivered by Chris in February. I’ll be delivering a Master Data Services course in February, before Chris delivers an MDX course in March. The details for all the courses are: SQL Server Internals and Troubleshooting Workshop - Christian Bolton – 6th – 7th December 2011 The Advanced Troubleshooting Workshop for SQL Server 2005, 2008 and R2 provides attendees with SQL Server internals knowledge, practical troubleshooting skills and a proven methodical approach to problem solving. The workshop will enable attendees to tackle complex SQL Server problems with confidence. Full details and registration here. Real World Cube Design and Performance Tuning with Analysis Services – Chris Webb – February 2012 A two day course that takes real world experience in showing you how to build a best practice Analysis Services cube, covering design issues such as data warehouse design and complex cube modelling. Day two then covers performance optimisation for Analysis Services, including MDX optimisation and cube processing. Full details and registration here. Introduction to Master Data Services with Jeremy Kashel – February 2012 An end to end look inside Master Data Services, this full day course will begin with a synopsis of Master Data Management before moving on to an overview of Microsoft SQL Server 2008 R2 Master Data Services (MDS). The remainder of the course will cover the major MDS topics, such as modelling and business rules, which will include a number of practical exercises. More details and registration for here. Introduction to MDX with Chris Webb – March 2012 The Introduction to MDX course aims to take you from the point of being a complete beginner with no previous knowledge of MDX up to the point where you can write 90% of the MDX calculations and queries you’ll ever need to write. The three day course covers the basics, such as sets, tuples, members to more advanced concepts such as scoped assignments and performance tuning. Full details and registration here.

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

As you may have already seen, Packt have released a new MDX book, namely MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli. The book is written as cookbook, meaning that you can choose the individual ‘recipes’ that apply to whatever problem you’re trying to solve. Each recipe starts off with a section called ‘Getting Ready’, essentially giving you the base query to use, before moving onto ‘How to do it', which covers the main instructions for the recipe. There are then further sections, which explain how the example works and also suggest other functions/concepts that you may want to consider. This sort of cookbook style makes it really easy to follow, each recipe is displayed very clearly in the book. A wide range of MDX problems are covered, from time calculations to context-aware calculations. Not every piece of the MDX functionality is covered, which is to be expected, given the size of the book. It also doesn’t cover the usual introduction to members/sets etc that MDX books tend to cover, but it’s clearly stated that having a working knowledge of MDX is a pre-requisite for the book. I found the copy that I’ve been reading in the Adatis office, but I really do like it, so I’ll definitely be ordering my own copy!

Master Data Services Training

As I’m sure you’ve seen, registration for SQLBits 9 – Query Across the Mersey is now open. This time around I’ll be running a deep-dive training day with Tim Kent on Master Data Services. This will be a full day of training, showing you how MDS can be used to manage the master data in your organisation. We’re going to start by giving an overview of Master Data Management, before moving on to covering the following MDS topics in detail: Installing, configuring, and maintaining Master Data Services Creating and using models Version management Business rules and workflow Importing data into Master Data Services Integrating with other systems Security You can register for the MDS Training day, or one of the other 10 training days, by using the following registration page. Remember, the early bird discount expires at midnight on Friday this week! Update – We are running another MDS course in February 2012. Click here for the details.

Allocations in PowerPivot Using DAX

Although I didn't mention it, the inspiration for my last post was the excellent MDX Solutions 2nd Edition. Whilst flicking through the book a while back, I though it would be interesting to see how DAX and PowerPivot could handle the so called 'Common Calculations and Selections in MDX'. This post continues that theme, focusing on one calculation in particular that's in the aforementioned MDX book, namely 'Unweighted Allocations down the Hierarchy'. Essentially this business problem is dealing with allocating data that is entered at a higher level (e.g. Quarter) down to a lower level (e.g. Day). It varies from business to business what the users actually want to see at the day level in this situation. Some expect to see the Quarter amount, some want to see a blank value and then some users want the amount in the quarters allocated down to the day level. If the expectation is that data should be allocated, then one way of doing the allocation is to use a ratio. In the MDX book, this is achieved by the following MDX: 1.0 / Descendants( Ancestor( [Date].[Calendar].CurrentMember, [Date].[Calendar].[Calendar Quarter] ), [Date].[Calendar].CurrentMember.Level, SELF ).Count If we put this into a query and run it against the Calendar hierarchy in AdventureWorksDW we get the following results: Therefore our quarter-entered value can simply be multiplied by this ratio to get the correct value at each level in the Calendar hierarchy. PowerPivot So the challenge is how to be able to do this in PowerPivot. I started off by looking for some data in AdventureWorksDW that would fit the bill. A good candidate seems to be the FactSalesQuota table, which I imported into PowerPivot along with its related dimensions: Once in the pivot table, I wanted to be able to calculate the correct ratio at the daily and monthly levels. For the daily level this is 1 / 90, as there are 90 days in the Q1 that year, and for the month level it is 1/3 as there are 3 months in the quarter. Given that there's no MDX style Ancestor() function in DAX, I ended up having to have a different calculation at different levels. Something that I learnt from this forum post is that you need to use IF() in order to isolate a calculation at a different level. Therefore the DAX that I came up with was: =IF( COUNTROWS(VALUES('DimDate'[FullDateAlternateKey])) = 1,     1 / CALCULATE( COUNTROWS('DimDate'), ALL('DimDate'[FullDateAlternateKey], 'DimDate'[EnglishMonthName]) ),     1 / CALCULATE( COUNTROWS( DISTINCT( 'DimDate'[EnglishMonthName]) ), ALL('DimDate'[EnglishMonthName]) )    ) The COUNTROWS(VALUES('DimDate'[FullDateAlternateKey])) = 1 allows me to check that we're at the day level, and if we are at the day level, then count the number of days in the quarter. Otherwise, if we're at the month level we calculate the number of months in the current quarter. When this DAX is put into a new measure we get the following results: This produces the same results as the MDX statement. The next step was then to apply this ratio to the Sales Amount Quota for the current quarter. This was achieved using the following DAX: =IF(       COUNTROWS( VALUES('DimDate'[FullDateAlternateKey]) ) < 90,       CALCULATE( Sum('FactSalesQuota'[SalesAmountQuota]),        All('DimDate'[FullDateAlternateKey], 'DimDate'[EnglishMonthName]) ) * 'FactSalesQuota'[Ratio],       Sum('FactSalesQuota'[SalesAmountQuota])    ) The < 90 at the start of the statement allows me to check if we're at the day level or the month level. If we're at either of these levels, then we want to pick up the Quarter-level SalesAmountQuota and multiply by the ratio. Otherwise, at the Quarter and Year levels, we just apply the original SalesAmountQuota value. This produces the following results: Conclusion The use of the MDX Scope statement would have been nice to pinpoint exactly where I wanted the calculation to occur, but then again, the use of IF() keeps it quite simple, which is better for an Excel power user. Whilst MDX is very very powerful, can you imagine a business user getting to grips with MDX statements such as Scope or Freeze? The bottom line is that the DAX calculation produces exactly what I want - the new AllocatedSalesQuota measure is correct at all levels in the Calendar hierarchy. Even with an IF() statement, like everything else in PowerPivot, the calculation performs very quickly.

Entering Dates in PPS Planning Assignments

In the recent PPS Planning projects that I've been involved in, the challenges have often been around subjects such as business rules, hence the often recurring theme of this blog. Recently the tables were turned though, as I was told by a user that they wanted to enter dates into a PPS assignment. I was initially a bit concerned that the Excel add-in may not be able to deliver here - after all its great at capturing numbers, but knowing the rigid structure of the fact tables, I couldn't see how it would manage to store a date. Then I remembered something from my VBA days many years ago - that is that Excel stores dates as a number from 30/12/1899, meaning in theory it should be possible to get dates working in PPS. Thankfully it is possible, as this post explains. Excel Setup The first step to get this working when designing your form template is to set the matrix to have a matrix style of 'none'. If you don't do this, then the built-in matrix styles will over-ride your formatting changes to the required cells. Speaking of formatting, the next step is to format the data entry cells that will contain dates, just using the standard Excel formatting window: Once these few simple steps are done, then the assignment will behave just like any other. As the date is stored as a number, the numeric representation of the date will end up in the fact table just as any other piece of data. Dates in Business Rules Once the numbers are in the fact table, we need to convert them to dates to use them in business rules in some way. We can't do much in PEL unfortunately, so the options are either NativeMDX or NativeSQL. As Analysis Services can pickup some of the VBA functions, it's possible to use the VBA DateAdd() function to convert the stored number back into a date. So in the example below, I'm using the DateAdd() function to convert the number to a date, before comparing the resulting date against another date using the VBA DateDiff() function: WITH MEMBER [Measures].[DateExample] AS VBA!DateAdd("d", [Measures].[Value], "30/12/1899") MEMBER [Measures].[DateDiff] AS VBA!DateDiff("d", [Measures].[DateExample], "01/07/1987") SELECT Descendants([Time].[Monthly].[Year].&[2008],,leaves) ON 0 FROM [Strategic Planning] WHERE ([Account].[Profit and Loss].&[5010], [Measures].[DateDiff], [Entity].[Divisions].&[5003]) Although the above is just a simple example, it should give you the idea of the kind of calculations that can be performed in Analysis Services. It's possible to use these functions via a NativeMDXScript or a NativeMDXQuery. It's a similar story with SQL, as it also has its own DateAdd() function, as shown in the simple select statement below: SELECT DateAdd(d, [Value], '30/12/1899') FROM dbo.[MG_Strategic Planning_MeasureGroup_default_partition] WHERE Scenario_memberid = 4 AND Account_MemberId = 5010 So it's a shame that PEL can't work with dates, but the fact that both the database engine and Analysis Services have a DateAdd function means that it's possible to use dates for logic in both definition and procedural business rules.

NativeMDXQuery Business Rules in PerformancePoint Planning

Having posted about NativeSql business rules a while back, I though that I might as well cover NativeMdxQuery business rules also, especially as there isn't too much documentation available on the web for this subject. NativeMdxQuery is a rule implementation type that can be used with assignment rules in PPS Planning. Rather than writing PEL and having the compiler convert your PEL into SQL or MDX, the idea is that you write the raw MDX directly if you pick NativeMdxQuery. Why Native Rules? When when I posted about NativeSql, I mentioned a scenario or two where using NativeSql was very useful. The advantages to writing a NativeMdxQuery, however, are less obvious. This is especially the case when you consider that a)standard MDX rules in PPS Planning are less restrictive that the SQL rules, and b) that PEL is very efficient - you can write a very concise MDX PEL rule vs writing the equivalent in MDX itself. So what advantages are there? Although a fair amount of MDX functions are included in PEL, it's not possible to use all MDX functions/statements/operators, e.g. Case(), Item(). This is one situation where you might want to write a NativeMdxQuery. Also, the ability to filter and restrict data in a raw MDX statement is far more powerful than the options available in PEL. For example, you can use the Filter()/IIF functions in PEL, but they're quite slow and you're restricted as to where you can put them. If you use MDX, you can use both a WHERE clause to slice the query and/or a HAVING clause for filtering. Finally, when just writing queries from an SSAS cube, a technique that's sometimes used is to create query-scoped calculated members, by using the MDX statement WITH MEMBER. This allows you to have calculation steps in your MDX query, and can effectively be used in one sense as a temporary variable store for calculations. If you're trying to do complex calculations in PEL, you have to assign everything to the 'this' keyword. It's much cleaner to have a block of code where you can define any calculations that the main query depends on. This is what WITH MEMBER will let you do. How to Write a NativeMdxQuery The requirements for a business rule using NativeMdxQuery are that you need to write an MDX select statement, which specifies the target cells that will be written to. Unlike NativeSql statements, you do not need to handle how the data gets inserted - PerformancePoint handles all that for you, as long as you produce the MDX select statement. An example select statement that makes up the entire content of a NativeMdxQuery assignment rule is shown below. The comments show what the query is doing: --[Measures].[Last Year] just makes the rule a bit more readable WITH MEMBER [Measures].[Last Year] AS ([Measures].[Value], [Time].[Monthly].CurrentMember.Lag(12)) CELL CALCULATION queryCalc FOR --This specifices the cells that we are overwriting with an expression or value '([Measures].[Value], [Scenario].[All Members].[Scenario].&[1], {[Time].[Monthly].[Month].&[200901],[Time].[Monthly].[Month].&[200902]}, {[Account].[Profit and Loss].[Level 07].&[5010],[Account].[Profit and Loss].[Level 07].&[5009]}, [BusinessProcess].[Standard].[Level 06].&[8], [TimeDataView].[All Members].[TimeDataView].&[1], Descendants([Entity].[Divisions].[(All)].&[0], ,leaves), Descendants([Currency].[All Members].[(All)].&[0], ,leaves), Descendants([Product].[Product Category].[(All)].&[0], ,leaves))' AS --The 100 is the value that we are giving the cells above 100 --This is the select statement which cells will receive the value above SELECT NON EMPTY ([Measures].[Value], ({[Scenario].[All Members].[Scenario].&[1]}, {{[Time].[Monthly].[Month].&[200901],[Time].[Monthly].[Month].&[200902]}}, {{[Account].[Profit and Loss].[Level 07].&[5010],[Account].[Profit and Loss].[Level 07].&[5009]}}, {[BusinessProcess].[Standard].[Level 06].&[8]}, {[TimeDataView].[All Members].[TimeDataView].&[1]}, {Descendants([Entity].[Divisions].[(All)].&[0], ,leaves)}, {Descendants([Currency].[All Members].[(All)].&[0], ,leaves)}, {Descendants([Product].[Product Category].[(All)].&[0], ,leaves)})) --Ensure we only write to cells with a certain value by using HAVING HAVING ([Measures].[Last Year] > 100000) properties [Scenario].[All Members].Key , [Time].[Monthly].Key , [Account].[Profit and Loss].Key , [BusinessProcess].[Standard].Key , [Entity].[Divisions].Key , [TimeDataView].[All Members].Key , [Currency].[All Members].Key , [Product].[Product Category].Key ON COLUMNS FROM [Strategic Planning] --Filter on a dimension member property by using a WHERE CLAUSE WHERE ([Entity].[Region].&[North]) The points to note about the above statement are: You must connect to the correct cube for the current model; You don't need to include a cell calculation - but it's the way that Microsoft implement business rules that use MDX, and it's hard to see how you would get a rule to be of any use without it; You must include the member keys as properties, otherwise the rule will error. Conclusion Writing MDX is clearly not for all users of PerformancePoint, but does provide the ultimate in flexibility when compared to PEL. Most MDX queries written in PPS will use cell calculations. If you're not used to how these work, or you just want to save some time, remember that an easy way to get started is to use the debug button on an MDX PEL rule in PBM - this will output a query that is similar to the one shown above.

Analysis Services Properties for PerformancePoint MDX Rules

One of the great features of PEL is that you can choose either a SQL or MDX implementation for your rules, depending on what you want to achieve. Whilst the MDX rules are much less restrictive than the SQL rules, they can sometimes run slower, depending of course on how your environment is set up. When the MDX rules do take a long time to run, it's possible that you might see this message: What has essentially happened within Analysis Services is that an object (e.g. a cube or dimension) has been processed and is waiting to commit. Unfortunately this is not possible whilst an existing query is running, so therefore AS waits for the query to complete. It will, however, only wait so long, which is defined by one of the Analysis Services Properties called ForceCommitTimeout. Once this threshold has been reached, then the offending query is canceled, resulting in the error message above. Finding the right balance for the Analysis Services ForceCommitTimeout and the PerformancePoint PAC 'OLAP Cube Refresh Interval' setting is key. If you have set PPS to re-process its cubes too often then you may well see the above message. On the other hand, if you set the ForceCommitTimeout too high, then queries executed whilst the cube is waiting to commit will be made to wait, meaning your big query will get though ok, but other users may see bad performance. Darren Gosbell has written an excellent post here that provides a thorough explanation of ForceCommitTimeout and other related properties.

BI Evening, TVP, 17th July 2008

I'll be giving a talk titled 'PEL Vs MDX' at the UK SQL Server Community's BI Evening on the 17th July at Microsoft's TVP offices in Reading. The content will be geared towards MS BI developers, and will highlight the differences between the two languages, with the aim that those with MDX experience will be able to make a fast start with PEL. Also speaking is Jeremy Kirkup from TAH, on 'Using Excel Services with Analysis Services and MOSS'. If you are just starting to explore delivering BI solutions with Excel Services and SharePoint then there are some issues that it is wise to be aware of in advance. This session will describe some real world lessons gained while creating BI solutions which expose Analysis Services data through the Excel Web Access web part. The Excel 2007 client has first class support for the some of the advanced features of Analysis services such as drill-through.  However, when exposing pivot tables to a browser through the Excel Web Access web part this feature is not available. The session will discuss a couple of approaches to implementing drill-through functionality for EWA based pivot tables using the MOSS SmartPart, AJAX and the Excel Web Services. You can register for the event here.

PEL Business Rule Re-use and Pre-defined What If Scenarios

You’ve built a rule set to satisfy an original set of business requirements for calculating budget, and all is good with the world.  Then, a change order is raised and approved that requires, for want of a better description, a set of pre-defined what if* scenarios to apply to the calculated budget. The pre-defined what-if’s are essentially the same business rule calculations with a slight factor or variance applied so it makes sense to want to re-use the existing rules. Not an unreasonable request. What’s the best way to implement this change request without introducing the burden of duplicated rules and keeping your model clean and well structured? Background This is the rule set in question: These rules utilise an assumption model that contains values such as salary levels, training days, tax assumptions etc and calculate various budget lines based on headcount information collected from a variety of regional cost centres. The rules are pretty straightforward as the Salary rule outlined below shows: scope([Scenario].[All Members].[Budget],[Account].[Detail].[Salary Costs],Descendants([Role].[Role].[All Roles], 0, after),Descendants([Time].[Quarterly].[Year2008], 0, after),Descendants([Region].[Region].[Global], 0, after) ) ; this = ( (Model([Drivers]), [Account].[Detail].[Annual Salary],[Time].[Quarterly].CurrentMember.Ancestor([Time].[Quarterly].[Year]),[Role].[Role].CurrentMember,[Region].[Region].CurrentMember,[Scenario].[All Members].[Budget]) / 4 ) * ( [Account].[Detail].[Headcount], [Time].[Quarterly].CurrentMember, [Role].[Role].CurrentMember, [Region].[Region].CurrentMember,[Scenario].[All Members].CurrentMember ); end scope; Essentially, for each quarter, for each role within each region we are multiplying the appropriate annual salary from the assumption model (divided by 4 for quarters) by the appropriate headcount to give us the budget salary figure. New Requirement Our change order requires a view of the budget as if we were to increase or decrease the headcount for each role within each region by 30%. Our new scenario dimension will look like this: The last two members have been added to cope with this new requirement – you could argue that these members could exist in a separate ‘what-if’ style dimension that could then be applied to any of the scenarios independently. I actually prefer that approach, but for simplicity let’s just limit this explanation to the scenario dimension. Okay, we have our new scenario members, we now need to wire up some rules to calculate these alternate budgets. Design Our first problem is how we can make the existing rules generic across budget scenarios. We have the budget scenario factors (a +30% and a -30%) to work in somewhere. We could hard-code these values into the rules but that would require a rule set per budget scenario thus duplicating all rules. Nasty. We could store the factors in an assumption model against the appropriate budget scenario, this could then be referenced within the rule. That would potentially allow us to re-use the business rules. However, I’m not keen on this approach at all as I feel that the budget factors are fixed, scalar values and to store them in an assumption model is overkill and would potentially require much more implementation effort. So, what option(s) are we left with? Not many, in this situation I would be tempted to create a member property on the Scenario dimension that held the factor against the appropriate member. The business rules could then simply (yeah right, see later!) reference the member property and apply the factor. So, in the case of the main Budget scenario we would have a factor of 1, for Budget +30% Headcount we would store a factor of 1.3 and 0.7 would be set against Budget -30% Headcount. So, on the face of it you can then update each rule to multiply every reference of Headcount by the appropriate factor held in a member property. However, there is a problem. That problem lies with the PEL function ‘Properties’. Unlike the MDX equivalent, the PEL version always returns a string, irrespective of the data type you assign to the member property itself. You cannot multiply by a string value and PEL does not contain any data type conversion functions to overcome this issue. Implementation The workaround: Use a Native MDX implementation. That sounds worse than it is; I’m not suggesting that you re-write all the business rules in MDX, although that is certainly an option. Alternatively, you can add a single MDX business rule to the top of the rule set that re-calculates Headcount, using the value of the member property: scope ( {[Scenario].[All Members].[Scenario].&[402],[Scenario].[All Members].[Scenario].&[501]},Descendants([Time].[Quarterly].[Year].&[2008], 0, AFTER),[Account].[Detail].[Level 02].&[5001],Descendants([Region].[Region].[Level 02].&[5201], 0, AFTER),Descendants([Role].[Role].[Level 02].&[101], 0, AFTER) ); ([Measures].[Value])= ( [Scenario].[All Members].[Budget],[Time].[Quarterly].CurrentMember,[Account].[Detail].[Headcount],[Region].[Region].CurrentMember,[Role].[Role].CurrentMember ) * [Scenario].[All Members].CurrentMember.Properties('OffsetPercent'); end scope; Apart from the fact the MDX rule is very PEL like, notice the last line of the rule. Here the headcount for each role, region and quarter is multiplied by the Scenario member property OffsetPercent. With this in place, the subsequent rules will calculate the budget scenarios based on the appropriate revised headcount value. (Due to the natural solve order of the rules in the rule set). The final step is to update each of the rules scope to include each of the budget scenarios (leaving the main bulk of the rule in place, untouched). Here’s the updated version of the Salary rule we looked at earlier (Modification in green) scope ({[Scenario].[All Members].[Budget],[Scenario].[All Members].[BudgetPlus30PcntHCount],[Scenario].[All Members].[BudgetMinus30PcntHCount]},[Account].[Detail].[Salary Costs],Descendants([Role].[Role].[All Roles], 0, after),Descendants([Time].[Quarterly].[Year2008], 0, after),Descendants([Region].[Region].[Global], 0, after)) ; this = ((Model([Drivers]), [Account].[Detail].[Annual Salary],[Time].[Quarterly].CurrentMember.Ancestor([Time].[Quarterly].[Year]),[Role].[Role].CurrentMember,[Region].[Region].CurrentMember) / 4) * ([Account].[Detail].[Headcount], [Time].[Quarterly].CurrentMember, [Role].[Role].CurrentMember, [Region].[Region].CurrentMember); end scope; For completeness, our modified rules set will look like this:   Notice the new (MDX) rule ‘Headcount’, this will be calculated first, for each of the budget scenarios ensuring the subsequent rules use the appropriately factored headcount figure. The support for Business Rule re-use is limited in PPS-P v1 but as this example hopefully illustrates, with a little thought and application, re-use is occasionally achievable.  I hope there will be better support for re-use in future versions ! --------------------------------------- * Generally, what-if’s are used dynamically; you change a few numbers in a data entry worksheet, re-calculate the sheet and view the results, repeating the process as necessary. This is a great feature and really quite powerful, however, there are some situations where pre-defined what-if’s are required to easily and frequently show standard what-if scenarios.

MDX - 12 Months to Date

Analysis Services 2005 comes with the useful Add Business Intelligence wizard, which will assist you with numerous calculations, including 12 Months to Date. However, I tend to write such calculations myself, especially having found out that the 12 Months to Date MDX generated by the Business Intelligence wizard didn't satisfy a client's requirements. Although the calculation works fine at the Month level in a Date dimension, it doesn't quite work at the day level - and it certainly doesn't take leap years into account. The client's requirement for day level 12 Months to Date was to return an accumulated view of the last 365 days back in the calendar hierarchy. However, for leap years, the calculation should take into account the 29th of February if the current year is a leap year. In order to get this to work, the first step is to modify the Date dimension so that it contains an attribute to signify whether the current day is affected by a leap year or not. In order to do this: 1. Add a bit column to your date dimension table called AffectedByLeapYear (or similar) 2. Run a SQL Update statement to populate your new column: UPDATE dbo.DimTime SET AffectedByLeapYear = CASE WHEN (CalendarYear % 4 = 0) AND (CalendarYear % 100 != 0 OR CalendarYear % 400 = 0) AND DayNumberOfYear >= 60 THEN 1 ELSE 0 END 3. Refresh your data source view  4. Add a new attribute called 'Affected By Leap Year' to your date dimension:   Now we’re ready to write the MDX for 12 Months to Date. In the MDX Script, the first step is to scope on a member called [Time Analysis].[Time Analysis].&[2], which is the 12 Months to Date member in my Time Utility dimension: Scope ( //Scope on 12 months to date [Time Analysis].[Time Analysis].&[2] ) ; Then we need a nested scope on the True member of the 'Affected By Leap Year' attribute. This is really important because it means we can isolate the section of the cube that is affected by the leap year. Also, by using Scope(),  we can avoid complex IIf statements - everything is managed cleanly in the Scope() statement: Scope ( //This statement is key - we scope on the cells that we know are affected by the leap year //This avoids a big and inefficient iif statement [Date].[Calendar].[Date].Members, [Date].[Affected By Leap Year].&[True] ) ; Now we're ready to perform the actual calculation. Seeing as this is for dates that are affected by a leap year, we need to aggregate a set with an extra member, meaning for any dates in a leap year beyond the 28th Feb, we will aggregate 366 days worth of data. Therefore, the following MDX statement is not too dissimilar to the kind of MDX that gets generated by the Business Intelligence wizard, aside from the fact its operating at the day level: This = Aggregate ( //We need to go back an extra day here {[Time Analysis].[Time Analysis].&[1]} * ParallelPeriod ( [Date].[Calendar].[Date], 365, [Date].[Calendar].CurrentMember ) : [Date].[Calendar].CurrentMember ) ; We then have a similar statement for the normal dates, which are any dates prior to the 29th Feb in a leap year, or any dates not in a leap year. The difference here is that the set that get aggregated just includes 365 days worth of data. When we put all these pieces together, we get: Scope ( //Scope on 12 months to date [Time Analysis].[Time Analysis].&[2] ) ; Scope ( //This statement is key - we scope on the cells that we know are affected by the leap year //This avoids a big and inefficient iif statement [Date].[Calendar].[Date].Members, [Date].[Affected By Leap Year].&[True] ) ; This = Aggregate ( //We need to go back an extra day here {[Time Analysis].[Time Analysis].&[1]} * ParallelPeriod ( [Date].[Calendar].[Date], 365, [Date].[Calendar].CurrentMember ) : [Date].[Calendar].CurrentMember ) ; End Scope; Scope ( //Now we scope on the 'normal' unaffected dates [Date].[Calendar].[Date].Members, [Date].[Affected By Leap Year].&[False] ) ; This = Aggregate ( //We are in a normal year (or in a leap before 29th Feb), just go back the //standard 365 days {[Time Analysis].[Time Analysis].&[1]} * ParallelPeriod ( [Date].[Calendar].[Date], 364, [Date].[Calendar].CurrentMember ) : [Date].[Calendar].CurrentMember ) ; End Scope; End Scope ; But we're not finished yet! We now need deal with the other levels in the Calendar hierarchy, namely Half Years, Quarters, Months and Years. These are all dealt with by a relatively simple statement that sets the current member of the respective hierarchy to its last child. So for a month, we pick up the 12 Months Year to Date figure for the end of the month, for the Quarter we pick up the 12 Months Year to Date for the last month in the Quarter etc etc. For example: Scope ( [Date].[Calendar].[Month].Members ) ; //For months, always display data from the last day in the month This = [Date].[Calendar].CurrentMember.LastChild; End Scope; Now you have a 12 Months to Date calculation that works at all levels in the Calendar hierarchy. Perhaps some of this is overkill for your requirements? I have spoken with users in the past who have only requested 12 Months to Date at the Month level - which is obviously a lot simpler. If you're doing a 12 Months to Date calculation, then it almost goes without saying that you have to make it work at the Month level! But as for the other levels, such as Quarter, you may be ok just leaving these as NULL. Certainly the Time Intelligence wizard leaves Years, Half Years and Quarters out, just putting 'N/A'. But all comes down to user requirements - and as we all know - you have to keep the users happy :-)

Basket Analysis using Analysis Services 2005

My job is pretty varied these days :o) Today I've been looking at satisfying some basket analysis type requirements where, for example, we need to determine what other products customers have bought in addition to a known product. We could have used data mining for this but decided not to as, in this case, it literally was a few mouse clicks away from our existing AS2005 cube. The implementation was surprisingly straight forward and query results (admittedly on a subset of the full dataset) very impressive. In an attempt to outline the implementation steps I will add some basket analysis to the Adventure Works sample database to demonstrate how easy it was ! Requirement List all products (and associated profit margin) that have also been purchased by customers who have bought a road bike from the 550-W range. Approach Rather than using data mining to handle queries of this type I want to extend my current cube.  In order for the performance to be acceptable we will adopt a variation on the many-to-many distinct count solution discussed in numerous blogs, white papers and articles. This result of this approach is a new measure group, we'll call it 'Customer Cross Purchase' and a new reference dimension based on product for our 'known product', we'll call this 'Product Filter'  From a relational perspective, the product filter dimension provides a lookup for customers (in Customer Cross Purchase) that have purchased that product. This in turn provides the ability to locate all orders (Internet Sales Fact) for that subset of customers.  Once we know all the orders for that subset of customers we can simply list the distinct products that make up those orders. (Product) Implementation The measure group contains a distinct set of customers and products that will act as the junction table of our many-to-many.  This is a view over the main fact and customer dimension. CREATE VIEW [dbo].[vCustomerCrossPurchase]ASSELECT DISTINCT f.CustomerKey, f.ProductKeyFROM         dbo.FactInternetSales AS f INNER JOIN                      dbo.DimCustomer AS c ON f.CustomerKey = c.CustomerKey Next, the fact table [view] is added to the Data Source View, ensuring the relationships to the customer and product dimension are set up. With the DSV updated, the cube itself can be extended.  A new measure group is created together with a Product Filter reference dimension.  The dimension usage looks like the diagram below.  This ensures the appropriate relationships exist as outlined above The new measure group is mapped to the product filter and customer dimensions, as per our dsv.  Note, this is not done automatically as the real (non referenced) product dimension is selected instead. To complete the picture, the Customer Cross Purchase measure group is used to create a many-to-many relationship between the Product Filter and the main Internet Sales measure group. Testing Once deployed and processed we can test out our modifications to check for some reasonable results. The following MDX query returns a list of all products that have been bought by customers buying  a road bike from their 550-W range in Reading, England. select     non empty [Product].[Product].members on rows,     [Measures].[Internet Gross Profit] on columnsfrom    [adventure works]where(    [Product Filter].[Product Model Categories].[Model].&[Road-550-W],    [Customer].[City].&[Reading]&[ENG]) The query is simple, it lists products on the rows and profit on the columns, the 'where' clause slices by Reading, England and employs the new Product Filter dimension.  The Product Filter dimension has the effect of slicing the main fact table by customers that have bought a bike from the Road 550-W range. So, we can see that apart from the road bikes, a few other accessories have been purchased too.  A quick couple of queries confirm the results. Three customers (above) have bought a road bike from the 550-W range and the other products these customers have bought (below) match our results !

MDX Stored Procedures (Part 1)

UPDATE:  You can find part 2 here, along with a link to download a C# quick start.  A long time ago I realised that inevitably, however well specified the requirements or however tight the deadlines are, clients always throw a curve ball.  To that avail, like many, I adopt an agile approach to project delivery with a keen eye on building frameworks and generic components that improve productivity and flexibility for current and future projects. My current project is no different to any other, we knew the requirements would change as, even from the outset, they were high level and vague.  With little budget to flesh them out, a controlled, iterative approach was essential.  It's a typical BI project, various data sources require consolidating and processing to allow analytics and reporting to assist in strategy and revenue generation. The reporting element was the vaguest of them all, although what did exist immediately eliminated using Reporting Services as the reporting/delivery mechanism.  The required reports were highly interactive and aesthetically challenging enough to stretch Reporting Services functionality to the limit, even without the vagueness busting curve balls out of sight but already primed for deployment. With all this in mind, we decided to use ASP.NET 2.0 web forms for the reporting as this gave us a little more freedom with regard to layout, a high degree of flexibility surrounding interactivity and the ability to quickly react to the ever changing requirements.  Now, with an eye on productivity we decided to build an MDX Helper class (mentioned previously here and here) that would streamline the development process and enable us to react quickly to those inevitable changes. Our own requirements for such a component were as follows: Allow text files containing parameterised MDX to be executed. Must support the execution of multiple queries over a single connection Return a conventional ADOMD cell set object containing the query results Return a typed/untyped ADO.NET DataTable object for binding to native and third party .NET controls Allow the inspection of various server, database and cube properties Lightweight, re-usable and simple to use The public interface of the resultant component looks like this: In it's simplest form, usage is straight forward. Take the following parameterised query stored in file UKSales.txt select    [Product].[Product Categories].[Category].members on rows,    [Measures].[Internet Sales Amount] on columnsfrom    [Adventure Works]where    @Country You return an ADOMD cell set object using the following C# code snippet: // Create an instance of the helper class, setting cube and query informationMDXHelper helper = new MDXHelper("ASTestServer", "Adventure Works DW", "Adventure Works", MDXHelper.MDXResourceFormat.RelativeURL, "UKSales.txt");// Add in the country parameter helper.Parameters.Add("@Country", "[Geography].[Geography].[Country].&[United Kingdom]");// Execute the queryCellSet result = helper.ExecuteCellSet();// Tidy uphelper = null; There are several overrides on the constructor and 'Execute' methods to allow for flexible usage and it's been used in anger now for a good couple of weeks supporting every type of MDX query we've managed to throw at it so far.  It still needs a little refinement and some full-on testing but it has achieved its goal and has helped us easily digest changes to the original requirements.  It has also given us some great ideas for version 2 where the query definitions are stored in the Analysis Services cube definition itself. In a future post I'll delve into the approach and implementation of the helper class to see how it works under the hood.

Analysis Services Stored Procedures - Views

Whilst not changing nappies and singing lullabies, Sacha is developing a .Net mdx helper class that behaves very much like an ADOMD command and runs an mdx query that returns a data table.   "Why not just use ADOMD?" I hear you ask - as Sacha mentions there are a number of reasons such as the fact that the data types are not returned (needed by .Net controls like Chartfx) and top level hierarchies ("All Time" etc ) are not always returned dependant on your query layout.  And in fact Sacha's little helper (ho ho ho) does use ADOMD but rather than use the returned table it parses the XML file that is generated.  This XML is actually quite simple once you get your head around the layout and is in fact very rich containing all sorts of metadata that is not returned in by ADOMD Other nice functionality includes the ability to return a number of cube and database properties (Description etc, last update, last processed); But the functionality I really like is that the class uses a stored query definition which can contain parameters much like a SQL stored procedure.    Sach and I have had a number of conversations over the past few days about why there is no concept of this in AS2005 (or 2008?) and haven't come up with a valid reason.  Surely people out there want to be able to re-use the same mdx queries (with different parameters).  How cool would it be to be able to use "exec mdxsp_GetResults(Param1, Param2)" in various mdx editors.  At current the query defs are stored in text files but we're looking at alternatives such as using msdb or the even the xmla cube def itself. In fact I'm surprised that no-one has done this already - or have they?? Very interested to know if people think this is something worthwhile? NB - if you found this blog looking for the excellent AS Stored procedures project it's here

XML Namespaces and the ADOMD XML Reader Result Set

I'm building a generic helper class to flatten an ADOMD data set to a data table for easier consumption by default and third party .NET controls.  I can't rely on the Load method of the data table, nor the CellSet object as in some cases not all the information is persisted; datatypes and top level hierarchies to name two. To persist all of the information I need to traverse the actual xml dataset returned by the ExecuteXMLReader method of the ADOMDCommand object. I don't use xml enough these days to remember all the nuances with namespaces and it took me a little while to work out what to set the namespaces to in order for the 'SelectNodes' method to work. Below is the code snippet I used to ensure the output from the ExecuteXmlReader method is fully traversable. // Execute the command returning the XML data setXmlReader reader = command.ExecuteXmlReader(); // Load the object into an XML document XmlDocument xml = new XmlDocument();xml.Load(reader); // Create an XML namespace managerSystem.Xml.XmlNamespaceManager nsManager=new XmlNamespaceManager(xml.NameTable); // Add the namepsaces to the managernsManager.AddNamespace("defaultNS","urn:schemas-microsoft-com:xml-analysis:mddataset");nsManager.AddNamespace("xsi",http://www.w3.org/2001/XMLSchema-instance);nsManager.AddNamespace("xsd",http://www.w3.org/2001/XMLSchema); // Return a list of nodes that represent the row informationXmlNodeList rows = xml.SelectNodes("defaultNS:root/defaultNS:Axes/defaultNS:Axis[@name ='Axis1']", nsManager); This kb article helped me out a bit:http://support.microsoft.com/default.aspx?scid=kb;en-gb;q318545 There are some more goodies included in my helper class that I will blog about in the future, once it's stable !

Best BI Blogs

These days if you want to keep up with all the latest news and developments (especially in our small little BI world) you need to follow the blogs of those who are in the know. Here are some of our favourites: An idea or two... SSIS expert Colin Kirkby (and top guy) Cutting edge! Chris Webb's BI Blog MDX Guru! (Thanks for the link Chris :) Christian Wade's Blog A bit quiet recently but still a great archive of posts Darren Gosbell [MVP] - Random Procrastination Australian BI guru and mastermind behind the excellent BIDS Helper Donald Farmer: Doing Data Integration Soft spoken Scot and MS Group program Manager (and fish farmer) some great posts.  Leading the data mining push at MS Establish. Execute. Evolve. Adrian Downes MS BI blog with some great PerformancePoint posts - Adrian co-wrote an excellent BSM book  with Nick Barclay (see below) Hitachi Consulting BI Blog A combined blog from the folks at Hitachi (though mostly Reed Jacobson) Intelligent Insight on PerformancePoint Ben Tamblyn is Solution Channel Development Manager for MS in the UK and has some great insight on what MS are up to in the BPM world JamieMac's Weblog Jamie MacLennan is AS Dev Manager at Microsoft - excellent data mining resource Microsoft Analysis Services Edward Melomed's Blog - The AS Program Manager and one of the original members of the AS team Microsoft Business Intelligence Blog Patrick Husting's blog - some good PerformancePoint posts here. Patrick is another BSM expert Microsoft OLAP by Mosha Pasumansky The one and only.... Molding the Microsoft BI Stack Shameless plug for my business partner Sacha Tomey's excellent BI and .Net Blog Nick Barclay's BI Blog Nick is BSM expert and co author with Adrian Downes (see above) PerformancePoint Server 2007 Troy Scott is posting some of the best PPS blogs at the moment Random thoughts on Microsoft Business Intelligence products Patrice Truong's great SQL BI blog SimonS SQL Server Stuff More general SQL than BI but still a good read SQL BI Marco Russo - his bio is in Italian (but his excellent blog is in English) SQL Server 2005 Business Intelligence Mr Sutha Thiru - he never sleeps! SSIS Junkie Conchango's Jamie Thomson SSIS - if you haven't heard of him you should give up now! Thoughts of a Freelance Dolphin Trainer Steve Mchugh - A bit different but always entertaining and very knowledgeable ;) The Kirkopedia Kirk Hasselden - Kirk was development manager for SSIS and now focuses on Microsoft's MDM outlook Of course there's plenty more so apologies if I've missed anyone - please let me know and I'll add them. When I have a bit more time I'll add these to a link section on the site