Adatis BI Blogs

SQL Server 2005 Cumulative Update 8 for Service Pack 2

Microsoft have just released Cumulative Update 8 for SQL Server 2005 Service Pack 2. Amongst the changes are a few fixes for small problems in Analysis Services, such as: Infinite recursion occurs in the CalculationPassValue function in SQL Server 2005 Analysis Services; A data source view that connects to an Oracle database generates incorrect relationships when you develop a Microsoft SQL Server 2005 Analysis Services project by using BIDS; The MeasureGroupMeasures function does not work correctly in SQL Server 2005 Analysis Services; All the MDX queries that are running on an instance of SQL Server 2005 Analysis Services are canceled when you start or stop a SQL Server Profiler trace for the instance. Details the above fixes and many more can be found here, as well as details of how to download the update. As usual, you can only apply the hotfix if you have Service Pack 2 installed. Details of all SQL Server 2005 builds released after Service Pack 2 can be found here.

XLCubed Version 4 Released

XLCubed Version 4 was recently released, adding support for increased data visualisation via MicroCharts, as well as enhanced web publishing. I've been using XLCubed since version 1 and have always found it both powerful and easy to use. All the standard functionality that you'd expect from an OLAP/Analysis Services Excel add-in exists and is incredibly intuitive, yet there's plenty more if you want to dive deeper. In particular, I've always liked the following features: Wealth of right click functionality, such as remove only, keep only, propagate across sheets; Drag and drop pivoting - alter your report quickly without having to open toolbars or menus; Converting grids to formula - Allows you to easily create disjoint reports using Excel formulas; Linking grids - Means that an action in one grid (such as a drill down on a dimension) can automatically perform the same operation on multiple linked grids. As mentioned, version 4 sees the inclusion of the interesting MicroCharts feature to the XLCubed grids. This essentially means that you can choose to display just numbers, a graphical MicroChart visualisation of the numbers, or both (see below). The MicroCharts do not hinder the core functionality, such as drill down or pivoting, and can be turned on for any of the measures. There's a lot more functionality worth looking at, you can find out more at

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 :-)

Setting up PerformancePoint Monitoring Design Role Security

There's lot of info out there about setting up the application pool identity so that you can set up data sources for dashboards (For example).  However there seems to be very little about the various roles that are used in the Dashboard Designer and how to set them up. If you try and connect to the Dashboard designer without appropriate access it will let you open the application and try and create data sources etc but you'll just get an error message like the one below when you try and connect to a server. Granting Permissions is a bit hidden away if you don't know where to look and you need to be logged as an existing monitoring server administrator to do this: Click on the office icon top left and then the Options button. On the Server tab of the new window that pops up you'll see a Connect button. Enter the server name e.g. http://servername:40000/WebService/PmService.asmx Click the Connect button - if you're not an existing Monitoring Server admin (or a local admin on the monitoring box) you'll get an error message here If all is well the Server Options and Permissions buttons will get enabled Click Permissions and then Add on the next window and you'll see the window at the centre of the image below. Enter the user name and select the role you want to put that user in. If you want to put someone in two different roles (e.g. Power Reader and Data Source Manager) just add them twice. There are four different roles available: Admin. (unsurprisingly) full rights over the server to build dashboards, administer security etc.  Members of the (windows) administrator group on the Monitoring server are automatically put in this group. Data Source Manager. Create, delete and publish data sources on the monitoring server. Creator. Create any dashboard element (and delete any that they own - see below). Power Reader. Read-only access to everything. Finally, there is a further level to the security that allows you to assign any domain user to have rights to a particular dashboard element without them having to be members of any role on the server. To do this open the element that you wish to amend the permissions for and select the  Properties tab.  At the bottom you'll see a Permissions section.  In here you can add any domain user as an editor or reader. Don't forget to click publish after you've changed the security for any dashboard element. Happy Day of the Dead!!

Multi-dimensional modeling (ADAPT)

Everybody hates documentation but this is a really neat, free tool for modeling multi-dimensional databases and is particularly suited to MSAS 2005.  ADAPT™ (Application Design for Analytical Processing Technologies) from Symmetry Corporation is a Visio stencil.  Example of a time dimension is shown below: There's a white paper that's worth a read to get you started

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 !

SQL User Group BI Evening

Chris Webb has already mentioned this but looks like registration for the next UK SQL BI user group evening is now open (Look for events on the right).  Edenbrook's Mark Hill (SQL 2008) and IMG's Suranjan Som (Data mining) are presenting. If that's not enough to get you there then free beer and pizza should do the trick :)

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

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

Analysis Services XMLA to clear query cache

To get a realistic measurement of how your query tuning improves query response time you should start from a common point.  To help with this you should clear your query cache before running a queries after making improvements (Unless you are cache warming of course !).   Here is some XMLA script to clear the cache for a specified database. <Batch xmlns="">   <ClearCache>       <Object>          <DatabaseID> database id </DatabaseID>          <CubeID> cube id </CubeID>       </Object>    </ClearCache> </Batch>   Replace database id and cube id with the details for your cube.  Notice these are ids, not names.  Look in the Analysis Services properties windows to find the ids.