Defining your own Functions in XQuery

Hi Kent,
The Stylus team is right here - but maybe just a little more detail
will help.
DataDirect XQuery supports user-defined functions, and it works with
Microsoft SQL Server. Since Stylus uses the DataDirect XQuery
implementation to connect to databases from many vendors, the
limitations of the Microsoft XQuery implementation do not affect us.
Also, there are a *lot* of things that Microsoft SQL Server does not
support in XQuery. Our tutorials, like most XQuery tutorials, show let
clauses in FLWOR expressions, even though SQL Server does not support
it. The article you comment on is about XQuery, not about the
limitations of the Microsoft implementation, and there *are*
full-featured implementations of XQuery that work efficiently with
Microsoft databases.
JonathanHello jonathan.robie@.gmail.com,

> The Stylus team is right here - but maybe just a little more detail
> will help.
Sure, they are right that 3rd party products do support such features. But
they aren't helpful if and when they give users false expectations about
what SQL Server 2005 does natively. I don't think its so much to ask them
to help the community understand when they do postings like this, where that
technology and what SQL Server supports diverge.

> DataDirect XQuery supports user-defined functions, and it works with
> Microsoft SQL Server. Since Stylus uses the DataDirect XQuery
> implementation to connect to databases from many vendors, the
> limitations of the Microsoft XQuery implementation do not affect us.
Frankly, I'm glad a product like yours is on the market and I've long apprec
iated
the fact that Data Direct has refrained from this kind of "edu-adversting"
in this particular newsgroup so as to not muddy the waters. Well, until now,
but hey, I guess I encouraged you to say something at least. :)

> Also, there are a *lot* of things that Microsoft SQL Server does not
> support in XQuery. Our tutorials, like most XQuery tutorials, show let
> clauses in FLWOR expressions, even though SQL Server does not support
> it. The article you comment on is about XQuery, not about the
> limitations of the Microsoft implementation, and there *are*
> full-featured implementations of XQuery that work efficiently with
> Microsoft databases.
*Lot* sometimes feels like an understatement. And again, I don't have a prob
lem
with tutorials that XQuery specific. But what I do take exception to is simp
ly
dumping thinly veiled ads in this group, getting folks excited about somethi
ng
and then not being around to clean up the mess when that goes splat. Believi
ng
that an ounce of prevention is worth more than a ton of cure, is it really
asking too much for the Stylus Studio folks to help see good expecations
at the outset?
I'd hope not.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Defining Non-Summable Measures in Cube

Hi all. I am new to SSAS 2005 and MDX in general. My team is building a data warehouse and thus far have the ETL (SSIS) part all done and am now moving on to the SSAS portion of the project. We have a cube built however, when browsing it via pivot tables, we noticed that the measures that are not additive, for example, "Average Log Length", show up as essentially sums over a given time period.

At first we thought that we needed to define the AggregateFunction for the specific measure to be AverageOfChildren. But that didn't work. The source data all has values of say 14.2, yet the average for a given day comes out to 2034.43! Clearly not an average.

This makes these non-additive measures completely useless in the cube.

Can anyone tell me what I am doing wrong? And how to correct it?

Any help would be much appreciated.

Thanks!

Jeff Ptak

Is the Type property of the time dimension specified as Time? The AggregateFunction semi-additive measure specification only applies to the Time dimension in your cube and will not work at all if no dimension is specified as a Time type.|||

This has come up before and the following is taken from this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=548837&SiteID=1

the AverageOfChildren is semi-additive, ie. it acts like a "sum" across all dimensions except time. For example, in Adventure Works if you add an AverageOfChildren measure on the OrderQuantity field of FactSalesSummary, its value matches that of [Measures].[Order Quantity] at the leaf Date level. But for July 2001 as a whole, [Measures].[Order Quantity] = 966, whereas the AverageOfChildren measure = 31 (which is 966 divided over 31 days of July).

Have you tried creating standard sum and count measures and then dividing the sum by the count? This should do the trick and you can hide the sum and count if they are not otherwise needed.

|||Yes. Set as Time.|||I have already read that post. I think I have it setup correctly, but the AverageOfChildren doesn't do what I want despite browsing by my Date dimension (which is configured as the "Time" type).|||

No, as that post explains, AverageOfChildren does not average across all dimensions, it sums across all other dimensions and only averages across the time dimension. It is not really well named, in fact in some of the wizards now it comes up as "Average over time".

Calculating a standard average of the values in the fact table can be done in a couple of easy steps.

1. Create a measure using a SUM aggregation

2. Create a second measure using a COUNT aggregation

3. Create a calculated measure that is (1) / (2)

|||

One last question. Are you deploying to Enterprise edition? Semi-additive measures are only supported in Enterprise.

http://www.microsoft.com/sql/editions/enterprise/comparison.mspx

|||

Yes. Deploying to Enterprise edition.

I'll try the sum/count thing. From what I have read, that seems to be common work-around to this problem.

Thanks!

|||It's not really a work-around, the averageOfChildren is meant to solve a different, more difficult class of calculation.|||

I figured this out. Simply set up a calculation for each non-summable measure that is:

Measure / Count

I made sure the AggregateFunction for the measure is set to SUM and I used the Count measure that the cube wizard inserted into my measure group (which has its AggregateFunction set to COUNT).

I would have thought that there was an easier way. I now have over 600 calculations in my my cube! And that's only for one process area of my facility.

Thanks for all your help.

Defining Non-Summable Measures in Cube

Hi all. I am new to SSAS 2005 and MDX in general. My team is building a data warehouse and thus far have the ETL (SSIS) part all done and am now moving on to the SSAS portion of the project. We have a cube built however, when browsing it via pivot tables, we noticed that the measures that are not additive, for example, "Average Log Length", show up as essentially sums over a given time period.

At first we thought that we needed to define the AggregateFunction for the specific measure to be AverageOfChildren. But that didn't work. The source data all has values of say 14.2, yet the average for a given day comes out to 2034.43! Clearly not an average.

This makes these non-additive measures completely useless in the cube.

Can anyone tell me what I am doing wrong? And how to correct it?

Any help would be much appreciated.

Thanks!

Jeff Ptak

Is the Type property of the time dimension specified as Time? The AggregateFunction semi-additive measure specification only applies to the Time dimension in your cube and will not work at all if no dimension is specified as a Time type.|||

This has come up before and the following is taken from this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=548837&SiteID=1

the AverageOfChildren is semi-additive, ie. it acts like a "sum" across all dimensions except time. For example, in Adventure Works if you add an AverageOfChildren measure on the OrderQuantity field of FactSalesSummary, its value matches that of [Measures].[Order Quantity] at the leaf Date level. But for July 2001 as a whole, [Measures].[Order Quantity] = 966, whereas the AverageOfChildren measure = 31 (which is 966 divided over 31 days of July).

Have you tried creating standard sum and count measures and then dividing the sum by the count? This should do the trick and you can hide the sum and count if they are not otherwise needed.

|||Yes. Set as Time.|||I have already read that post. I think I have it setup correctly, but the AverageOfChildren doesn't do what I want despite browsing by my Date dimension (which is configured as the "Time" type).|||

No, as that post explains, AverageOfChildren does not average across all dimensions, it sums across all other dimensions and only averages across the time dimension. It is not really well named, in fact in some of the wizards now it comes up as "Average over time".

Calculating a standard average of the values in the fact table can be done in a couple of easy steps.

1. Create a measure using a SUM aggregation

2. Create a second measure using a COUNT aggregation

3. Create a calculated measure that is (1) / (2)

|||

One last question. Are you deploying to Enterprise edition? Semi-additive measures are only supported in Enterprise.

http://www.microsoft.com/sql/editions/enterprise/comparison.mspx

|||

Yes. Deploying to Enterprise edition.

I'll try the sum/count thing. From what I have read, that seems to be common work-around to this problem.

Thanks!

|||It's not really a work-around, the averageOfChildren is meant to solve a different, more difficult class of calculation.|||

I figured this out. Simply set up a calculation for each non-summable measure that is:

Measure / Count

I made sure the AggregateFunction for the measure is set to SUM and I used the Count measure that the cube wizard inserted into my measure group (which has its AggregateFunction set to COUNT).

I would have thought that there was an easier way. I now have over 600 calculations in my my cube! And that's only for one process area of my facility.

Thanks for all your help.