SQL BI

Multiple Hierarchies from SQLCAT

marcorusso — Mon, 21 Apr 2008 23:52:16 GMT

I'm still late with blog reading and sometimes I discover interesting things one or two months later than the original posting. I just read the SQLCAT analysis of the several solutions available to handle multiple parent-child hierarchies within a single dimension. The multiple hierarchies pattern described in my many-to-many paper has been used (with some variations) and now I have the "SQLCAT certification" that this model is faster than others!

I think there is space for improvement in this area and the many-to-many space is still a relatively unexplored space. If you have experience adopting those models, please share your knowledge - and if you are shy, write me directly and I'll post the interesting data.

Cross-posted from SQLBlog! - http://www.sqlblog.com

Many-to-Many Session at European PASS Conference 2008

marcorusso — Fri, 11 Apr 2008 15:50:27 GMT

I'll be a speaker at the European PASS Conference 2008 next week in Dusseldorf. I will talk about advanced dimensional modeling using many-to-many relationship. The content is based on "The many-to-many revolution" paper I wrote two years ago. If you will attend that conference and you already used some of the models I described in the paper, I will be happy to get direct feedback from you.

My session is scheduled on Wednesday at 14:30. Unfortunately, I will not have much time after the session because I will have to go to the airport - for this reason, contact me in advance if you want, I'll be at the conference starting from Monday.

Cross-posted from SQLBlog! - http://www.sqlblog.com

LINQ to extract object permission from SSAS

marcorusso — Fri, 28 Mar 2008 17:01:31 GMT

Yesterday one customer of mine encountered the issue described in KB933836 and posted also by Chris Sells.

The issue is that the deployment of a SSAS database always overwrite object permissions defined on database objects like cubes. The "keep existing roles" setting of deployment wizard does not preserve those information. I needed to solve the issue and I simply made a simple program that creates an XMLA file with object permissions that I need to restore after database deployment. I used LINQ and XML Literals in Visual Basic - I wrote a book about LINQ and I have to find a reason for this, but I really think that LINQ saved my time.

I will write a more complete article about this issue one day, may be... by now, if you have the same issue, you can contact me to get the code I wrote. If you don't need it, look at the readability of LINQ in a case like this.

If I will receive some request, I will consider to publish this simple tool.

Cross-posted from SQLBlog! - http://www.sqlblog.com

PASS Europe 2008

marcorusso — Wed, 20 Feb 2008 17:05:22 GMT

It's a shame I haven't blogged for two months, but I've been really (I mean - REALLY) busy finishing the upcoming Microsoft Programming LINQ book. Now I'm coming back to my BI world and the first news is that I'll speak about SSAS models based on many-to-many relationships at the European PASS Conference 2008, April 14-16 2008, Neuss, Germany (Europe, of course...). For those of you who want to know what I'm going to talk about... you can read The Many-to-Many Revolution paper I wrote more than one year ago. It is based on SSAS 2005, but there is nothing to add with SSAS 2008 too.

In reality I and Alberto developed some variation of the base models I described in the paper. In particular, we had great success with the Balance Reclassification model (sorry, we still don't have an article about it) that allows defining multiple parent-child hierarchies on a balance sheet, also using sign rollup operator (which is not natively supported if you have a many-to-many relationship with a parent-child hierarchy). Unfortunately, we desisted using it in a Gb-sized fact table for performance reason. But it is working well in many other typical scenarios, where balance sheet fact table has a granularity that gives a lower size of the fact table.

Let me know if this is an argument (Balance Reclassification model) that is interesting for you - I could give some anticipation in a blog post or in a short article.

Cross-posted from SQLBlog! - http://www.sqlblog.com

Many-to-Many Dimensions: Query Performance Optimization Techniques

marcorusso — Sat, 22 Dec 2007 21:51:02 GMT

More than one year ago I published a paper about design patterns for many-to-many dimension relationships. Since then, I built other models and I got more experience about possible performance optimizations. Unfortunately, I still hadn't time to write about it and this topic requires verbose document and analysis to be reproducible and understandable by everyone. Luckily, now there is a white paper (Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques) downloadable from Microsoft site that explore this world and offers an interesting set of scenarios and possible optimizations, with numbers that explain when, what and how you can expect to optimize a model based on one or more many-to-many dimension relationships.

I already read the document and it is pretty good. Unfortunately, in the real world there are still complex scenarios that cannot be optimized with the techniques used here. I hope that in future versions the SSAS engine will provide some more advanced optimizations for similar cases. One of the first step could be simply automating the optimizations made "by hand" like the Matrix Relationship Optimization shown in the paper. Another would be the simplification for defining efficient aggregations when many-to-many relationships are involved (now you could create a lot of aggregations that are unused when M2M are involved, and sometimes users query a cube only using M2M relationships - you have to tune the aggregations manually).

Cross-posted from SQLBlog! - http://www.sqlblog.com

Free Introducing LINQ book

marcorusso — Sat, 22 Dec 2007 16:54:07 GMT

Usually I write in this blog about Business Intelligence without too much consideration for "traditional" access to SQL Server. However, in a parallel universe I also wrote a book about LINQ and now that we are in full Christmas mode, I hope you may be interested that you can download the Introducing Microsoft LINQ book for free!!

I wrote this book with Paolo based on Beta 1. Some contents are now outdated, but general concepts and 80% of the syntax is still valid. And, remember, now it's free, so you shouldn't claim too much! We are currently working on a more advanced and complete book about LINQ (Programming Microsoft LINQ), which is expected to be available by mid-May 2008.

Have a good read, a Merry Christmas and a Happy New Year!

Cross-posted from SQLBlog! - http://www.sqlblog.com

Real Report Builder 2008 improvements for Analysis Services

marcorusso — Wed, 19 Dec 2007 05:38:23 GMT

Brian Welcker (who is leaving SSRS team - good luck Brian!) has announced a long explanation of the Report Builder 2008 positioning.

For me (and all of you who live in the multidimensional business...) the most important part of the post is this simple sentence:

[...] However, for folks building reports against Analysis Services cubes, auto-generating a model is no longer required.

This is really a good news who will drive Report Builder adoption in SSAS shops. I still haven't tested Report Builder 2008 very much, but this news will help me to find the time to do it.

Cross-posted from SQLBlog! - http://www.sqlblog.com

Generate date dimension that support working days calculation

marcorusso — Wed, 28 Nov 2007 05:45:55 GMT

I just wrote a T-SQL query based on CTE that generates support information to calculate working days in a period. Simply look at the WorkingDaySequential measure.

In the real world:

The Holidays CTE could be replaced by a real tabl with user-inserted data.
The temporary [#Calendar] coule be a real table, providing necessary data access from DM

and probably much else.

The following code is provided "as is", without any warranty about its behavior.

SET STATISTICS IO ON
GO

SET DATEFIRST 7  -- Default is 7 (US / Sunday) 
GO

DECLARE @StartYear AS INT 
DECLARE @EndYear AS INT 

SET @StartYear = 2006 
SET @EndYear = 2015 

WITH    Holidays
          AS ( -- Italian Holidays (use year 1900 for recurrencies dates)
               SELECT   Date = CAST('19000101' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('19000106' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('19000425' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('19000501' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('19000602' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('19000815' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('19001101' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('19001208' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('19001225' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('19001226' AS DATETIME)
               UNION ALL
       -- Holidays changing date each year
               SELECT   Date = CAST('20060417' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('20070409' AS DATETIME)
             ) ,
        Years
          AS ( SELECT   YYYY = @StartYear
               UNION ALL
               SELECT   YYYY + 1
               FROM     Years
               Where    YYYY < @EndYear
             ) ,
        Months
          AS ( SELECT   MM = 1
               UNION ALL
               SELECT   MM + 1
               FROM     Months
               WHERE    MM < 12
             ) ,
        Days
          AS ( SELECT   DD = 1
               UNION ALL
               SELECT   DD + 1
               FROM     Days
               WHERE    DD < 31
             ) ,
        DatesRaw
          AS ( SELECT   YYYY = YYYY,
                        MM = MM,
                        DD = DD,
                        ID_Date = YYYY * 10000 + MM * 100 + DD,
                        DateString = CAST(YYYY * 10000 + MM * 100 + DD AS VARCHAR),
                        Date = CASE WHEN ISDATE(YYYY * 10000 + MM * 100 + DD) = 1
                                    THEN CAST(CAST(YYYY * 10000 + MM * 100 + DD AS VARCHAR) AS DATETIME)
                                    ELSE NULL
                               END
               FROM     Years
               CROSS JOIN Months
               CROSS JOIN Days
               WHERE    ISDATE(YYYY * 10000 + MM * 100 + DD) = 1
             )
    SELECT  DatesRaw.*,
            DayOfWeek = DATEPART(dw, DatesRaw.Date),
            CalendarDaySequential = CAST(DatesRaw.Date AS INT),
            WorkingDay = CAST(CASE DATEPART(dw, DatesRaw.Date)
                                WHEN 1 THEN 0 -- Sunday
                                WHEN 7 THEN 0 -- Saturday
                                ELSE 1
                              END AS BIT)
    INTO    #Calendar
    FROM    DatesRaw
    LEFT JOIN Holidays recurring
            ON recurring.Date = DATEADD(Year, 1900 - YEAR(DatesRaw.Date), DatesRaw.Date)
    LEFT JOIN Holidays fixed
            ON fixed.Date = DatesRaw.Date
GO
-----------------------------------------------------------------------------------------------------------
SELECT  *,
        WorkingDaySequential = ( SELECT COUNT(WorkingDay)
                                 FROM   #Calendar wd3
                                 WHERE  wd3.CalendarDaySequential <= wd1.CalendarDaySequential
                                        AND wd3.WorkingDay = 1
                               )
FROM    #Calendar wd1
ORDER BY ID_Date
GO

Cross-posted from SQLBlog! - http://www.sqlblog.com

SQL Server Virtualization

marcorusso — Sun, 18 Nov 2007 16:08:23 GMT

I just read the SQL Server Performance in a VMware Infrastructure 3 Environment performance study published by VMware (thanks to virtualization.info for the link).

Often I've been asked if it is a good move having a SQL Server in production in a virtual machine. As every consultant, my first answer is "it depends". And I really think this, it's not only a way to avoid the question!

If we look at an OLTP application, then my personal answer is that it could be a good move, especially if my hardware is so much larger than the one necessary to handle the daily workload. Numbers offered by VMware performance study are pretty good, considering that they are working on a reasonably hardware (not a monster one) that you can find in the real world. However, this paper simulates an OLTP scenario and not a Data Warehouse one. One of the signs for this assumption is the presence of a RAID 5 data storage, which could be a real bottleneck for ETL activities (and this is a good topic for another post in the future - I will not discuss about it here).

Until now, I always prefer working on a real hardware for both production AND especially development/test environments for a BI solution, at least for the back-end part (SQL/SSIS/SSAS). The reason is based on economics: a minimum VM for this kind of activities requires at least 4 cores, better if 8 cores (as a minimum), and at least 8 GB of RAM. Especially during execution of SSIS packages and process of SSAS cubes, all resources (I/O, memory and CPUs) are stressed at their limits. While a VM could be handled in a production environment just to balance resources between daily operations and night batches (but it would be like having a server that is turned off during the day - sometimes you can afford it, sometimes not), I'm not comfortable in using a VM like this for development. The reason is that the workload affects all concurrent VMs and if you virtualize a server, it is supposed that you keep other servers on the same machine.

At the end, until now I've found more affordable having dedicated servers for development instead of using virtualized ones. I know that this situation could change in the future. Virtualization technology is fast evolving and (more important) hardware cost for multiple cores and large-RAM-provided servers will be so much cheaper that the "entry-level" server in a data center will be too powerful to be used at the maximum of their capacity many data warehouse installations.

What is your experience here? Do you have a development environment for your data warehouse completely virtualized or not? What do you think about it? I'd like to get feedback on this.

Cross-posted from SQLBlog! - http://www.sqlblog.com

DefaultMember, subcubes and non-aggregatable attributes

marcorusso — Tue, 02 Oct 2007 04:35:41 GMT

Today I discovered that DefaultMember might result in a member other thant the default dimension member.

Reading documentation, I got the idea that DefaultMember would be always the default member defined for a dimension into a cube. In reality, the default member could change into a subcube that excludes the original default member from a calculation. This is important because writing MDX Scripts you cannot anticipate any possible use of your cube... and you should carefully consider when DefaultMember is the real appropriate syntax on a case-by-case basis. You have to be particularly careful whenever you use DefaultMember on a non-aggregatable attribute.

Let's look an example.

In Adventure Works you can write the following query.

WITH MEMBER Measures.DiffActual 
     AS (Scenario.Scenario.CurrentMember, Measures.Amount) 
        - (Scenario.Scenario.DefaultMember, Measures.Amount)
SELECT 
{ [Scenario].[Scenario].[Forecast], 
  [Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM [Adventure Works]

This query provides this result:

I'm not interested in the real meaning of this query. This is only an excuse to see the DefaultMember in action against a non-aggregatable attribute. The DiffActual calculated member has a formula that use Scenario.Scenario.DefaultMember. The default member for Scenario.Scenario attribute is Actual. The DiffActual row shows the difference between the scenario in column and the Actual scenario.

Now, if we encapsulate the previous query into a subquery that apparently should return the same members, we can write this query.

WITH MEMBER Measures.DiffActual 
     AS (Scenario.Scenario.CurrentMember, Measures.Amount) 
        - (Scenario.Scenario.DefaultMember, Measures.Amount)
SELECT 
{ [Scenario].[Scenario].[Forecast], 
  [Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM 
  (SELECT 
    { [Scenario].[Scenario].[Forecast], 
      [Scenario].[Scenario].[Budget] } ON COLUMNS
FROM [Adventure Works])

Before executing this query, what is the expected result? First time I thought "it should be the same", but in reality it is different, like shown here:

What happened? Simply, the SSAS calculation engine needs a default member included in the "context" - when you use a subquery, you are defining a subset of the cube space that must have an existing default member for each attribute - if a default member is outside of the subcube, it is replaced by another member of the same attribute.

In the case we are exmining, the default member of the subcube becomes Forecast. This default member depends on the order of members of the same attribute used in the subcube. If we invert Forecast and Budget members only in the subcube, the default member becomes Forecast instead of Budget, changing the calculated member result (based on DefaultMember syntax).

WITH MEMBER Measures.DiffActual 
     AS (Scenario.Scenario.CurrentMember, Measures.Amount) 
        - (Scenario.Scenario.DefaultMember, Measures.Amount)
SELECT 
{ [Scenario].[Scenario].[Forecast], 
  [Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM 
  (SELECT 
    { [Scenario].[Scenario].[Budget], 
      [Scenario].[Scenario].[Forecast] } ON COLUMNS
FROM [Adventure Works])

This is the result for the query above:

To avoid this issue you can use a specific member instead of the keyword DefaultMember. In our example, the solution is:

WITH MEMBER Measures.DiffActual 
     AS (Scenario.Scenario.CurrentMember, Measures.Amount) 
        - (Scenario.Scenario.[Actual], Measures.Amount)
SELECT 
{ [Scenario].[Scenario].[Forecast], 
  [Scenario].[Scenario].[Budget] } ON COLUMNS,
{ Measures.Amount, Measures.DiffActual } ON ROWS
FROM [Adventure Works]

With this syntax, any use of this query as a subcube in another query will not have the side effects we have seen before.

Why this is so important? Simply because Excel 2007, often used as a client by end users, makes heavy use of subqueries in MDX generated by PivotTable. I had to substitute all DefaultMember in MDX Scripts with well-known member names (this also affects my DateTool dimension, which I will update soon with this and other improvements).

Final note: a special thanks to Mosha Pasumansky for illuminating me on hidden secrets of DefaultMember. Mosha also said that using the member name of default member (instead of DefaultMember keyword) improves query performance.

Cross-posted from SQLBlog! - http://www.sqlblog.com

MDX Studio

marcorusso — Sun, 23 Sep 2007 12:41:55 GMT

My backlog makes me late on a lot of announcements, but I'd like to post a link to the new MDX Studio by Mosha Pasumansky, because this is a very useful tool when you need to dig into an MDX query issue.

There is a forum dedicated to the tool on SSAS Info website.

The SkyDrive directory where you can find CTP releases is this: http://cid-74f04d1ea28ece4e.skydrive.live.com/browse.aspx/MDXStudio

It's not a beta, it's an alpha and there is space for a lot of improvements - I'm going to try it to give my feedback too.

Cross-posted from SQLBlog! - http://www.sqlblog.com

MDX query with clear cache in a single statement

marcorusso — Sun, 16 Sep 2007 14:09:10 GMT

Darren Gosbell describes a useful technique to clear cache and execute an MDX statement using a single MDX command window. It's very very useful when you're doing performance tuning of your MDX script!

Cross-posted from SQLBlog! - http://www.sqlblog.com

SSAS scale-out with SAN snapshot

marcorusso — Thu, 13 Sep 2007 03:10:51 GMT

Microsoft released a short whitepaper about how scaling-out queries to a SSAS database leveraging on SAN snapshot features. It's an interesting read if you have a SSAS database with many users and don't have the time to copy a database on several servers.

Cross-posted from SQLBlog! - http://www.sqlblog.com

DateTool dimension improvement

marcorusso — Mon, 10 Sep 2007 03:18:15 GMT

I fixed an issue of the DateTool dimension I presented in a previous post. The DateTool was unusable on OWC 11, which is the component used by BI Dev Studio and SQL Server Management Studio to browse a cube. I only added two statements in MDX Script that assign the original value when a selection of all years / all months is made. It seems that OWC 11 defines what members will be shown in the pivot table with a different technique than the one used by Excel 2007. The new version of the DateTool is 0.6.0.0 and is downloadable here.

Cross-posted from SQLBlog! - http://www.sqlblog.com

Patterns of dimensional modeling design

marcorusso — Thu, 06 Sep 2007 15:48:00 GMT

Alberto posted one of the patterns we use designing a dimensional model (this one is about the discretization of a measure into a dimension). We'd like to get some feedback about this solution: is there someone who used similar patterns? We consider this design a good model for its flexibility (we used it in financial environments where desired clusters of measures changes but we don't want to reprocess the whole fact table that is very very large).

Any feedback is welcome!

Cross-posted from SQLBlog! - http://www.sqlblog.com