Tomislav Piasevoli

Business Intelligence Specialist

Oct 042011
 

 

MVP logoHi all,

here’s just a short info that I have received the Microsoft MVP Award again, my third in a row. Naturally, I’m very happy about it :-) .

As always, stay tuned for interesting presentations, blog posts and/or magazine articles in the upcoming period. And thank you for following and appreciating my work!

 

Aug 122011
 
MDX Cookbook

Hi all,

if you’ve been wondering where have I been hiding all these months, the answer is here – I was writing a book for Packt Publishing Ltd. The book, named MDX with Microsoft SQL Server 2008 R2 Analysis Service: Cookbook, is finally out and available on publisher’s site. Soon, it should be available on Amazon and in other popular book stores.

For those of you who prefer the e-book over the paperback edition, it’s good to know that the PDF version already exists on publisher’s site, while the ePub and Mobi are coming soon. Same is with the Kindle Edition on Amazon.

UPDATE (27th of August): On Amazon US, the paperback edition is available here, the Kindle edition here.

Looking forward to receiving your feedback :-)

Apr 182011
 

Two weeks ago I was in Rovinj doing presentation (and a small BI workshop) at the biggest yearly gathering of IT geeks in Croatia – WinDays 11. The conference was organized by Microsoft Croatia. The title of my presentation? “Business Intelligence Semantic Model”, or BISM, for short.

Here’s the abstract of that session in English/Croatian:

The next version of SQL Server brings several news related to Business Intelligence. This session will focused on one of them – the new semantic model inside SQL Server Analysis Services (SSAS) known as BISM. We’ve already had a chance to see the Business Intelligence Semantic Model (BISM), athough not exactly under that name. It was PowerPivot with its VertiPaq storage and DAX calculations. The next version of Analysis Services will enable the use of the BISM in addition to the already existing UDM. What it means to BI implementers, what it means for end users? The presentation will focus on explaining what BISM really is and compare it with the existing UDM. If you’re interested in cubes, PowerPivot and similar BI stuff, come and see what the future brings.

Sljede?a ina?ica SQL Servera donosi nekoliko novosti vezanih uz Business Intelligence. Ovo predavanje fokusirat ?e se na jednu od njih – novi semanti?ki model unutar Analysis Services (SSAS) poznat pod kraticom BISM. Business Intelligence Semantic Model (BISM) smo ve? imali prilike vidjeti iako ne pod tim imenom. Radilo se o PowerPivotu i njegovom modelu spremanja podataka koji po?iva na vrsnom VertiPaq engine-u i uporabi DAX kalkulacija. Sljede?a ina?ica Analysis Services omogu?avat ?e, pored dosadašnjeg modela poznatog pod kraticom UDM, korištenje BISM modela. Što to zna?i za implementatore BI rješenja, a što za korisnike? Tema predavanja bit ?e objašnjenje BISM-a i njegova usporedba s postoje?im UDM-om. Zanimaju li vas kocke, PowerPivot i sli?ne BI stvari, do?ite na predavanje da vidite što budu?nost nosi.

In case you’re interested, the presentation is available here:

Looking forward to your feedback.

 

Oct 042010
 

I’m happy to receive the Microsoft MVP Award again. I’m also proud of being enlisted among well-known SQL Server MVP bloggers on the Microsoft site. The honor that’s been given to me I intend to justify in the year to come as well.

In this moment I’d like to express my gratitude to everyone who trusted in me so far and therefore gave me a chance to share my knowledge one way or the other, in various places, occasions and formats. An even deeper one goes to all of you, followers of my work, for being the precious support in my endeavors.

Thank you all!

Jun 272010
 

Hi everyone,

just a short notice for all of your that I’ve moved my blog to a new platform and I’ve finally made use of the domain in my possession. I must say I feel much comfy here with all those gadgets and settings. Yes, I’m running it on my own. Can’t help it, the geek in me likes to have control and fine-tune everything.

All the posts including code, pictures, comments, links, dates and other settings are transfered successfully. Some things still need to be done, but the blog is functional and running and my vacation is starting :-) .

The old blog is still on, but has its comments closed. Its content will be updated to include the corresponding links on this blog. Finally, to keep only one version of truth, posts in the old blog will be deleted after some time, but their titles will remain. Inside each post in the old blog I will put a direct link to corresponding post on this blog. In short, they will become shortcuts and stay such in future.

Please, update your links and favorites that refer to my old blog when you find the time. Thank you.

If you’re interested in performing the same operation I made, drop me an e-mail. I might also post an article about it.

Hope you’ll like the new environment. My apologies for any inconvenience I caused by this decision.

Jun 092010
 

Today is the 2nd anniversary of my engagement in MSDN SSAS forum which is a good excuse to summarize activity because somehow I’ve had a feeling that I did much less while spending definitely equal amount of time if not more. Was it really so?

If we don’t measure, we … well, definitely can’t tell for sure – might be a free interpretation of the famous quote made from the technical perspective. In blog articles I usually try to perform an analysis which then results in a conclusion. In this one what I’ll do is a self-analysis with data gathered from various sites combined with some of my own.

Forum stats are as follows: 1250+ posts, 333+ answers, 6200+ points. As mentioned in advance, last year’s focus was supposed to be different – less forum activity, more of other types of engagement. And so it was. My forum contribution was reduced to only 25% of previous year’s posts (which had made me felt less active), but the percentage of correct answers increased relative to that (66% of previous year’s answers). Not bad. I wish to think it means only one thing – that I’m improving my BI skills by doing more with less. This observation, deduced from a quick measurement, helped instantly :-) .

So what were those other activities?

Well, I wrote about a dozen technical articles in my blog (300% increase, 150% if we track exactly the same period because I started blogging in October), 4 articles for local magazines two of which were published, volunteered at 1 foreign conference, had 1 presentation for the local user group, 3 sessions at local conferences and 1 lecture at local university. Again a surprise. I spent a lot of time on these activities, partially because they were not routine to me, partially because they required more time than forum did (in form of preparation). For example, to blog it takes one night (for a good technical article), to lecture or write a magazine article it takes few days up to a week, to prepare a session for a conference it takes three weeks. In contrast, to answer a question on forum it takes approximately few hours of engagement (a rough estimate, spans from few minutes to several days; it varies a lot). Once put together like this, it made sense and again caused my perception to become more positive.

Then, I travelled a lot. I’ve been precisely twice around the globe in last two years with YoY (year-over-year) index of 200% in mileage and 150% in number of days. I’ve been to my very first MVP Summit and I’ve visited 5 conferences in total where I learned a lot and met interesting people. What I’m most happy about is the fact that on some of those trips I had a pleasure of sharing the experience with my family, which, now that I come to mention it, will increase in September :-) .

There were some downs as well. PowerPivot and DAX appeared while Mosha said goodbye to BI at the same time. Yes, that casted a big shadow of uncertainty on future of SSAS and MDX, the things I like the most. Furthermore, as long as PowerPivot and DAX are tied to Excel with no APIs or similar, to a BI developer it is nothing but a peep show. Seriously, there are so many possibilities what we could do with it, programmatically! Anyway, I suppose things will inevitably change and therefore I’m learning this technology intensively. Even found a niche to blog about which amuses me – comparing DAX with MDX and T-SQL.

I also missed Microsoft BI conference in New Orleans and failed to present at some foreign conferences lately. I’ll try my luck again, but this won’t be an area I’ll concentrate the most. Next year that will be my blog.

In the following year I’ll continue blogging and I’ll increase the quantity of blog articles even more. That’s an activity I see myself in. Free topics, writing when inspired, cooking ideas in between the posts, fast delivery and not depending on anyone or anything else. I’ll probably change the platform soon. I’ve been delaying this for some time, but now that (already poor) stats are gone, it’s a clear case for me. As you can see, I like the stats very much. They are a measurement of silent feedback – how many people visited and perhaps read an article. And as we saw earlier, being able to see the results of that measurement is directly linked to feeling of worthiness.

Lower priorities are exams, learning and having a session or two at user group meeting or local conference. However, a recent opportunity to finally finish my master degree in economics might conflict that making those activities even less important because there might not be enough time for everything.

We’ll see which way the story went in a year from now review.

May 152010
 

Boyan Penev recently blogged about a strange behavior which manifests when ordering two related sets thereby mentioning a thread in SSAS forum answered by Deepak Puri. I found this intriguing and decided to reply directly, but the explanation kept growing, so I finally cut everything out and put it as an article on my blog instead. After all, this way my chances of being enlisted in his blog roll should increase 8-).

Here’s the problem in short. The first query works, the second doesn’t. The difference? The second query has related hierarchies while the first one doesn’t.

WITH
SET [RowSet]    AS [Product].[Subcategory].Levels(1).Members
SET [ColumnSet] AS [Date].[Calendar].Levels(1).Members
SELECT
     Order( [RowSet], [ColumnSet].Item(0), BDESC ) ON ROWS,
     [ColumnSet]                                   ON COLUMNS
FROM [Adventure Works]

 

WITH
SET [RowSet]    AS [Product].[Subcategory].Levels(1).Members
SET [ColumnSet] AS [Product].[Category].Levels(1).Members
SELECT
     Order( [RowSet], [ColumnSet].Item(0), BDESC ) ON ROWS,
     [ColumnSet]                                   ON COLUMNS
FROM [Adventure Works]

 

Now, some of you may have already guessed this has to be a case of attribute relations and yes, I admit you’re right. Nevertheless, I think an analysis would be beneficial, after all Boyan asked for it.

First, let’s make a small but important distinction. Those queries look like they have been written by a T-SQL developer. Why am I saying it? Because in T-SQL, ORDER is typically (although not necessarily) based on a column, be it numeric, date or string. A set being returned is ordered based on the values of that column.

In MDX, ORDER() is based on an expression which means that the second argument is automatically converted to a tuple (ColumnSet.Item(0)), and then a value of it: (ColumnSet.Item(0)).Value. Let’s see how those two MDX queries might look like in T-SQL.

use AdventureWorksDW2008;

with
t as
(
select
    SubCategory = s.EnglishProductSubcategoryName,
    CalendarYear = d.CalendarYear,
    ResellerSales = sum(f.SalesAmount)
from dbo.DimProductSubcategory s
    join dbo.DimProduct p
        on p.ProductSubcategoryKey = s.ProductSubcategoryKey
    join dbo.FactResellerSales f
        on f.ProductKey = p.ProductKey
    join dbo.DimDate d
        on d.DateKey = f.OrderDateKey
group by
    s.EnglishProductSubcategoryName,
    d.CalendarYear
)
, pvt as
(
select * from t
pivot( sum(ResellerSales)
       for CalendarYear in ( [2001], [2002], [2003], [2004], [2006] ) ) as pvt
)
select * from pvt
order by
– sort A
[2001] desc;
– sort B
/*
(select sum(ResellerSales) from t
        where t.SubCategory = pvt.SubCategory and
              t.CalendarYear = 2001) desc;
*/

 

with
t as
(
select
    SubCategory = s.EnglishProductSubcategoryName,
    Category = c.EnglishProductCategoryName,
    ResellerSales = sum(f.SalesAmount)
from dbo.DimProductSubcategory s
    join dbo.DimProduct p
        on p.ProductSubcategoryKey = s.ProductSubcategoryKey
    join dbo.FactResellerSales f
        on f.ProductKey = p.ProductKey
    join dbo.DimProductCategory c
        on c.ProductCategoryKey = s.ProductCategoryKey
group by
    s.EnglishProductSubcategoryName,
    c.EnglishProductCategoryName
)
, pvt as
(
select * from t
pivot( sum(ResellerSales)
       for Category in ( [Bikes], [Components], [Clothes], [Accessories] ) ) AS pvt
)
select * from pvt
order by (select sum(ResellerSales) from t
          where –t.SubCategory = pvt.SubCategory and
                t.Category = ‘Accessories’
         ) desc;

 

They return exactly the same result as those two MDX queries. Btw, who says MDX is not elegant and easy?

Now, let’s analyze them.

The first query is ordered by column [2001]. We said that MDX uses expressions when sorting, so we’ll try to do the same using an expression. We can comment [2001] DESC and un-comment what’s below “sort B”, but the result won’t change.

In the second query I deliberately skipped sorting by a column (ORDER BY [Accessories] DESC) and used the version with an expression in order to get the false result. The column version would yield the correct, expected result. While we might have thought sorting in MDX behaves that way, the truth is it doesn’t. MDX has some specifics and we need to use the expression version in order to show them.

Important thing to notice is that in both queries there’s a join which binds subcategories (disregard it being commented in the second query at the moment, act like it’s still there). We can imagine this as being the first phase in setting the condition of those correlated sub-queries.

The next phase would be to expand this by specifying the argument of the Order() statement in MDX. In case of a Date dimension, that was year 2001. Since that is a different dimension, nothing has changed in our initial condition.

In the second query, there was a related hierarchy (same dimension) which came after the initial phase, so it forced everything so far to adjust to it (which btw might be seen as a third phase). You can imagine this third phase as limiting the join further by adding conditions like this:

and exists (select ProductCategoryKey from dbo.DimProductCategory
            where EnglishProductCategoryName = pvt.SubCategory)

 

When the related hierarchy is “below” the current one, the initial join could be considered as removed (instead of bringing this extra EXISTS which makes no sense if you analyze what’s inside). Therefore I commented it in the query. In fact, I manually did what SSAS engine implicitly does to joins from the initial phase when a related hierarchy comes in the second phase and when the relation is 1:N, or in OLAP terms – below granularity.

When it is “above”, the initial join is just more limited (now it makes sense to use EXISTS).

In DAX it would be something like the ALL() function (for analogies between MDX, T-SQL and DAX I suggest reading my previous blog article).

Err, one more thing, the join is based on keys rather than names, but for simplicity reasons I haven’t included them in queries.

Now let’s get back to OLAP and it’s multidimensional space.

Cube’s space is N-dimensional which means each coordinate should be N-di
mensional too. SSAS formula engine fills the missing parts using the current members of non-mentioned hierarchies. But who are they?

Based on members explicitly specified in a coordinate, the engine implicitly (the way a trigger fires in T-SQL) finds members on related hierarchies. They become current members of those hierarchies. For non-mentioned dimensions (not hierarchies), the engine picks current members which again are “current” because of some previous context set by an expression, function, slicer or the cube itself.

In the first example year 2001 sets the context of that expression. All the hierarchies of Date dimension implicitly shift their current members accordingly (which is not important in this case, but let it be known). Since the Product and others dimension were not mentioned in that expression, we traverse to previous contexts (backwards) in order to find their current members. The Filter() function sets the immediate previous context. For Product dimension, Product.Subcategory hierarchy to be precise. – How come? – Well, Filter() is sort of a loop, which means we iterate over members in its set (the first argument) which in turn become “current” in that expression, one by one. Other dimensions are not important in this case but the process of identifying current members might be visualized likewise.

In the second example Accessories category sets the context. Its presence automatically shifts Product.Subcategory current member to a related position. – Where? – To its root member. – Why? – Because relation is 1:N. If it were N:1, it would shift to a single related member. Since it can’t find one, it shifts to its root member which is a way of saying “I can not find a direct relation to a single member, therefore I’ll move to a place where I don’t stand in a way, a neutral position that doesn’t change the (explicit) coordinate, though complies to the obligation of me being present in the coordinate”.  – Let me think it through a bit … OK, I see. But how come the current member of Product.Subcategory hierarchy didn’t stay too? I mean, he was in Filter() function too, as the first argument. – Correct, but they are not treated the same. – Why’s that? – Layers! – What? – Calculation layers. In form of a stack. On top there’s only Accessories because only that was explicitly there in the expression and that expression is the last thing evaluated in the Filter() function. – Why is that the last thing? – Well, first you need to set a loop. Then you evaluate the expression for each member, right? As you would do in programming, loop is outside, assignments and calculations are inside the loop. Therefore, Product.Subcategory set (and its current members in a loop) is one layer below in this stack. Whoever is NOT on top, or shall we say BELOW the layer being currently evaluated might be implicitly shifted. Might, because it depends whether he’s related or not. If he is, as in the second example, he will shift, but if he isn’t as in the first example, nothing will happen. He’ll come unchanged. In turn, he might determine who’ll be shifted among his “relatives” in layers below his. And so on. The winner takes it all approach among “relatives” and the winner is the “relative” being explicitly in the coordinate, on top of the others. – Oh, I see. Current and lower layers you say … Wait, I got an idea! In order to make them equal, we should bring them on the same layer, right? – Exactly! – And how do you suggest we should do that? – The expression. That determines the top layer in this case so both of them should be there. – Ok, but how? – Simple, by providing a tuple. We should expand the existing condition with .CurrentMember of the first set.

Here are both the false and the correct MDX for the second query, to emphasize their differences.

WITH
SET [RowSet]    AS [Product].[Subcategory].Levels(1).Members
SET [ColumnSet] AS [Product].[Category].Levels(1).Members
SELECT
     Order( [RowSet], [ColumnSet].Item(0), BDESC ) ON ROWS,
     [ColumnSet]                                   ON COLUMNS
FROM [Adventure Works]
 

WITH
SET [RowSet]    AS [Product].[Subcategory].Levels(1).Members
SET [ColumnSet] AS [Product].[Category].Levels(1).Members
SELECT
     Order( [RowSet], ( [ColumnSet].Item(0),
                      
[RowSet].CurrentMember ), BDESC ) ON ROWS,
     [ColumnSet]                                          ON COLUMNS
FROM [Adventure Works]

 

Forcing the current member is like saying “I want my current member to stay and not get implicitly converted to other members. I’m forcing this coordinate and I know what I’m doing, return me the value of this explicitly requested intersection (aka coordinate)!”.

Now, return to the second T-SQL query and un-comment the join. You’ll get the correct result because this time you explicitly forced the join. Or shall we say you prevented this join from disappearing in T-SQL just as you prevented the current member in MDX from changing because of attributes relations in that dimension. Interesting?

Finally, it wouldn’t hurt to specify the same tuple in the first MDX query too. I mean, the result would not change, they would stay correct. So, if you, the reader of this article, are searching for the expression which works in any scenario, use the one with the tuple.

Important thing to remember. In MDX you can almost always omit current members in your expressions. Yet, when dealing with related hierarchies, as in this example, specifying them or not makes a world of difference.

The question remains – why there is this attribute relations mechanism in OLAP? Well, that might be a topic for some other article. For now let’s just say that it keeps the multidimensional space smaller and consistent. And although it behaves like a wormhole, it’s here to help. Without it, we might get lost out there.

May 032010
 

Recently I delivered a session at local conference organized by Microsoft Croatia – WinDays¹º. The title of the presentation was: “DAX vs MDX vs T-SQL”.

As its name suggests, the presentation is oriented towards SQL and BI developers (for change) instead of Excel power-users and compares DAX calculations with T-SQL and MDX expressions. The idea behind, besides improving my T-SQL skills and learning DAX, was to build a mental model of what DAX functions and their combinations actually do by finding their best analogies in other languages. Since there are more than a hundred DAX functions, I’ve concentrated primarily on those that use relations. On one hand, they are probably the most difficult group of functions in DAX. On another, they should be the closest to SQL and BI developers’ mindset.

If you’re wondering why T-SQL and MDX, the reason is simple. PowerPivot uses in-memory column-oriented relational database technology and was developed by SSAS team (together with Excel team). “Relational” means we have every reason to compare DAX with T-SQL. And since SSAS team was involved, MDX comes naturally. Hereby I wish to emphasize that the comparison shown in the presentation was based on imaginary or potential analogies in T-SQL and MDX and not the real implementations built into PowerPivot engine. In the same manner, the summary highlights showing strengths and weaknesses of each language is based on personal impressions. Regardless of that, I believe the presentation might be of great help for many.

The presentation (translated in English) is published on my blog including all the related files. You can find it here.

Looking forward to your feedback.

Jan 232010
 

 

Average among siblings, as a calculated measure, is typically used to compare the result of each member in a group where the group is formed from his siblings. In case of a known hierarchy, the core expression is relatively easy.

(
[<Dimension name>].[<Hierarchy name>].CurrentMember.Parent,
 
[Measures].[<Measure name>]
)
/
Count(
      NonEmpty(
               [<Dimension name>].[<Hierarchy name>].CurrentMember.Siblings,
               [Measures].[<Measure name>]
              )
     )

<> represent placeholders.

The expression says – take the parent’s value and divide it by the count of siblings that have results (only those, not all of them). Of course, we should add testing for zero as well as testing for empty values. Also, we could use sum of siblings instead of parent’s value because at least one member has no parent (root member). Or we should test whether current member has a parent and if not, then only use the sum of siblings version of expression. Might be faster on large dimensions. For simplicity, we won’t cover it here, not to complicate too much.

iif(
    IsEmpty( [Measures].[<Measure name>] ),
    null,
    iif(
        Count(
              NonEmpty(
                       [<Dimension name>].[<Hierarchy name>].CurrentMember.Siblings,
                       [Measures].[<Measure name>]
                      )
             ) = 0,
        null,

       Sum(
           [<Dimension name>].[<Hierarchy name>].CurrentMember.Siblings,
           [Measures].[<Measure name>]
          )
        /
       Count(
             NonEmpty(
                      [<Dimension name>].[<Hierarchy name>].CurrentMember.Siblings,
                      [Measures].[<Measure name>]
                     )
            )
        )
    )

But if the requirement is to make that expression work for any hierarchy (no matter whether that dimension only or for any hierarchy in a cube), we have to reach for the Axis() function.

In case of Excel 2007 as a front-end, the following code should be put into MDX script. Only one thing will be dependant on your cube and that is the first calculated measure. There you should provide a measure for calculation of average among siblings. In this code, Sales Amount from Adventure Works 2008 was used, but as I said, any measure will do. The rest of the code is cube independent, meaning, it will work in any cube and for any hierarchy.

Create Member CurrentCube.[Measures].[Original Measure] AS
    [Measures].[Sales Amount]
;

Create Member CurrentCube.[Measures].[Where are measures in Excel] AS
    case
        when NOT IsError(Extract( Axis(0), Measures ).Count) then 0
        when NOT IsError(Extract( Axis(1), Measures ).Count) then 1
        else -1
    end
;

Create Member CurrentCube.[Measures].[Where are non-measure rows] AS
    iif(
         NOT IsError(Axis(1).Count) AND
             [Measures].[Where are measures in Excel] < 1,
         1,
         iif(
              [Measures].[Where are measures in Excel] <> 0 AND
              NOT IsError(Axis(0).Count),
              0,
              null
            )
       )
;

Create Member CurrentCube.[Measures].[Average Measure] AS
    iif( IsEmpty([Measures].[Original Measure]),
         null,
         iif( IsEmpty([Measures].[Where are non-measure rows]),
              [Measures].[Original Measure],
              iif( Axis([Measures].[Where are non-measure rows]).Item(0).Item(0)
                       .Hierarchy.CurrentMember.Level.Ordinal = 0,
                   [Measures].[Original Measure],       
                   Sum(Axis([Measures].[Where are non-measure rows]).Item(0).Item(0)
                           .Hierarchy.CurrentMember.Siblings,
                       [Measures].[Original Measure])
                   /
                   Count(NonEmpty(Axis([Measures].[Where are non-measure rows]).Item(0).Item(0)
                                      .Hierarchy.CurrentMember.Siblings,
                                  [Measures].[Original Measure]))                               
                 )
            )
       )
, FORMAT_STRING = ‘Currency’
;

As you can see, we define a calculated measure Original measure to be used throughout the other part of the code. Next, we test where Excel 2007 has put the measures. They could be on rows, columns or slicer (WHERE part). Excel uses only 2 axes and a slicer, doesn’t use axis(2) aka pages like OWC.

We test by counting measures extracted from an axis and testing whether that produces an error. If so, measures are not there. If they are not on rows or columns, then they must be in slicer. When we have only one measure, Excel puts that measure in slicer. When there are more measures, they are either on rows or columns, depending on other hierarchies in query.

Once we know where the measures are, we test where is a hierarchy that we need an average for. Although it seems sometimes that hierarchy is on one particular axis (let say rows), Excel tricks us with display and generates MDX with that hierarchy on columns for example. Not always, but sometimes. That’s why we need to test where that hierarchy is. Btw, we’re interested in the first hierarchy that appears on rows, that is the mode of work. And we return the average among siblings result for its members.

The next calculation follows. If there is something on rows in query and measures are on columns or slicer, then we have the position of our hierarchy (it is positioned on rows). If the measures are not on columns but something else is (that’s the scenario where Excel swaps axes), then take position 0 as the place where our hierarchy is. As said, hierarchy is actually on columns although visually it looks like it’s on rows. In all other scenarios take null as result.

Finally, we come to the main expression. As explained in the simple example above, we must test whether the original measure was empty. If so, we must provide the same for our calculated measure not to include that row in result when NON EMPTY was used on axes. Then we test where that hierarchy of choice is. In case of null, which means there is no hierarchy in the query other than measures, we provide the original value since the average of a single member is the same member’s result. In the main part of this expression we test for root member and provide the same value (orig
inal value) since that’s an average for him. In all other cases the expression comes down to sum of siblings over count of siblings with result (hence NonEmpty() part). Without handling empty members, our average would be false.

In the end, we take care of format string and test if it works.

That’s it.

Here’s an example of it in action.

image

The article was inspired by this thread.

Nov 152009
 

A short remark for those of you who read my blog and happen to be in the neighborhood this week.

I’ll be attending Sinergija 09 conference in Belgrade, Serbia, which starts on Tuesday, 17th of November and ends on Thursday, 19th of November, 2009. While I’m there, I’d love to meet some of you working on Microsoft BI platform and have a chat or two regarding that. The best chance to find me is, of course, one of the rooms with SQL Server topic. However, that’s not guaranteed, because there are few other areas (here’s the Agenda) that look tempting too. Anyway, the picture in my profile might help in recognition.

See you there.

Kratka obavijest za one koji prate moj blog i nalaze se ovoga tjedna negdje u blizini.

Ove godine posje?ujem Sinergija 09 konferenciju u Beogradu, koja po?inje 17.11., a završava 19.11.2009. godine. Volio bih upoznati neke od vas koji se bavite Microsoft BI platformom i razmijeniti rije? ili dvije na tu temu dok sam tamo. Najve?a šansa da me na?ete je, naravno, u nekoj od prostorija s temom SQL Server. Ipak, to nije garancija, jer neke od ostalih tema (evo rasporeda predavanja) zvu?e više nego interesantno. U svakom slu?aju, fotografija u mom profilu pomo?i ?e u raspoznavanju.

Vidimo se tamo.