MS SQL Server OLAP - AggregateFunction and PeriodtoDate

Author

AggregateFunction and PeriodtoDate

Karthik Rao

2005-12-24, 7:23 am

Hi:
1. AggregateFunction and Semiadditive measures:

I have a set of products (Category and Subcategory). These products
all have MarketPrices that change very often, though not everyday.

I used LastNonEmpty on the price Measure, and this works ok for a
single product. However, Prices get summed up across Product
Subcategories and categories. This gives me an error when I calculate
the value of the inventory at total of subcategories and total of
categories.
I have tried setting nonLeafDataVisible to false, I could not figure
fro BOL how to get visualTotals running.
2. PeriodToDate Errors
Am trying to calculate the duration in days for each level of
inventory. If I had a inflow of 100 pcs on 1Dec05 and bought another 50
on 20Dec05, then I would like to calculate that the age of the first
hundred on 20Dec05 is 19 Days. I tried DateDiff and PeriodtoDate
functions, but got argument mismatch errors in both cases. Stuff like
DateDiff procedure expects a Date variable, and the PeriodtoDate
function expects to receive a Level Expression, not a Heirarchy
expression etc.

Could someone please help me on this?

Thanks a lot
Karthik

Karthik Rao

2005-12-28, 3:24 am

Help. somebody? Please?

Deepak Puri

2005-12-28, 11:24 am

Hi Karthik,

Can you provide more details of your time hierarchy, and of the approach
that you were trying to use? When trying to compute the difference
between dates in a hierarchy, you may be able to use the MDX Rank()
function, rather than DateDiff():

http://msdn2.microsoft.com/en-us/library/ms144726(en-US,SQL.90).aspx[color=darkred]
Rank (MDX)

Returns the one-based rank of a specified tuple in a specified set.

Syntax

Rank(Tuple_Expressio
n, Set_Expression [ ,Numeric Expression ] )
Arguments

Tuple_Expression
A valid Multidimensional Expressions (MDX) tuple expression that
specifies the tuple to be ranked.

Set_Expression
A valid MDX set expression that specifies the set that contains the
specified tuple.

Numeric_Expression
A valid MDX numeric expression.
...[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Karthik Rao

2005-12-28, 1:24 pm

Thanks, Deepak.
I just got Datediff to work after all. All it needed was a capital D
for the date interval. Is that expected behavior?

On the more crucial Semi-additive part, I have the following Dimensions
and Measures:
Measures :
1.Transactions (OwnerId, ProductID, TranID, InflowQty, OutflowQty,
InflowCost, OutflowPrice,DateTim
estamp)
2. PriceHistory(Product
Id, effectiveDate, UnitPrice)
and I have calculated measures like Balance, Duration(where I use
DateDiff) etc

Dimensions
1. Owner(OwnerID, Name)
2. Product (Category, Subcategory, ProductID, Name)
3. Time (date, week, month, Quarter, Year, with YWD and YQMWD
heirarchies)

I made PriceHistory.UnitPrice a semiadditive measure of type
LastNonEmpty. That works for me, as long as I am working with a single
product. The Problem is that UnitPrice gets aggregated for all
Subcategories, and for all Categories.

I have a Calculated Measure InventoryValue which is equal to
(OwnerID. CurrentMember,ProdId
. CurrentMember, Date.CurrentMember,
Measures.Balance)*(ProdID.CurrentMember, Date.CurrentMember,
PriceHistory.UnitPrice). This goes bad because UnitPrice Got Summed
across Products.
InventoryValue is the sum of all InventoryValues, not the (sum of
Balances) x (Sum of Prices).
I want Sigma(a*x) and not Sigma(a) * Sigma (x) to put it in
mathematical notation.

How do I get My inventory value to sum the right way?

Thanks a lot for your help.

Karthik Rao

2005-12-28, 1:24 pm

I got the dateDiff Function working, but I still cannot get my date2
value. Please see table below, where Inventory inflows/outflows occured
on the TranDates mentioned. Now I need to use DateDiff to calculate
Duration.

Duration for the first Transaction for a given owner, and a given
ProdId should be zero. The second transaction occurs 2 days later, the
third, 4 days after the second and so on.
TranDateTime TranDat
e Duration
Prod_1 2005-11-01 00:00:00 11/1/2005 0
2005-11-03 00:00:00 11/3/2005 2
2005-11-07 00:00:00 11/7/2005 4
2005-11-11 00:00:00 11/11/2005 4
2005-11-15 00:00:00 11/15/2005 4

When I use Time.PrevMember, Measures.Trandate, I get the following
results , which are offset. So is I use this value for Date2 in the
daeDiff function, I get a lot of junk.

TrandateTime TranDate Date.PrevMember,
Measures.Trandate
(Date1) (Date2)
Prod_1 2005-11-01 00:00:00 11/1/2005
2005-11-02 00:00:00 11/1/2005
2005-11-03 00:00:00 11/3/2005
2005-11-04 00:00:00 11/3/2005
2005-11-07 00:00:00 11/7/2005
2005-11-08 00:00:00 11/7/2005
2005-11-11 00:00:00 11/11/2005
2005-11-12 00:00:00 11/11/2005
2005-11-15 00:00:00 11/15/2005
2005-11-16 00:00:00 11/15/2005

Deepak Puri

2005-12-29, 3:24 am

Hi Karthik,

I can't find capital "D" documented as a valid interval:

http://msdn.microsoft.com/library/d...y/en-us/dnvbade
v/html/ performingsimplecalc
ulations.asp[color=darkred]
...
Table 2.5: Possible Interval Settings for DateAdd

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
...[color=darkred]

Since UnitPrice is only valid at the ProductID level, try summming
InventoryValue up from that level, like:
[color=darkred]
Sum(Existing [Product].[ProductID].[ProductID].Members,
Measures.Balance * PriceHistory. UnitPrice)[color=dar
kred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Karthik Rao

2005-12-29, 7:23 am

Thanks a lot Deepak.
Datediff - Well, I dont know if LocaleId has something to do, but the
only thing that worked for me was a capital D.

I have a calcuated member that calculates the Inventory Value, and this
is at ProdID level, and is a daily time series, bcos the UnitPrice is a
timeseries. This value at ProdID level uses a formula like
measures.Balance*Time.Date, PriceHistory.UnitPrice. These values are
required,m and are coming out ok.

SSAS automatically sums up to category and subcategory, but does
Sigma(InitPrice) * Sigma (Balance) instead of Sigma
(UnitPrice*Balance).

I dont understand your formula. Will it give me both the raw figures
and the Aggregations at Category and Sbcategory?

What is the meaning of ProductID.ProductID.Members? I have seen this
used, and have also seen stuff like CurrentMember.PrevMember, but
havent found help on what that means.

I also havent been able to figure out the prob I have with the date2
value ( in my prev post in this thread.

Thanks for your help.
Can we MSN or soemthing if you wnat more details?

Karthik Rao

2005-12-29, 7:23 am

Hi Deepak:
I just used the existing () function that you suggested and it works
perfectly, thanks a heap.

I am looking to learn more about SSAS, but not much available,
especially BOL :-/. Could you please suggest good sources of gyan?

Deepak Puri

2005-12-29, 8:24 pm

Hi Karthik,

You should step through the AS 2005 tutorials, in addition to browsing
BOL, if you haven't already.

There are diverse and evolving sources of info on AS 2005 - Mosha's
MSOLAP web-site compiles links to resources:

http://www.mosha.com/msolap/

Chris Webb's blog lists some AS 2005 books here - but check availability
dates if your need is immediate:

http://spaces.msn.com/members/cwebb...2005/11/18.aspx

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Karthik Rao

2005-12-30, 3:23 am

The first thing I did was the tutorial, but frankly, it does nothing
beyond giving you an intro to the Environment. Any attempts to dig
deeper are useless, most stuff in BOL does not have examples, and even
the descriptive text is so sketchy.

I have just started using Mosha's site.

Thanks once again, Deepak.

Sponsored Links