Hi All,

I have a question here that is both related to Reporting Services and
Analysis Services (2005), but more so Analysis Services, so I'm posting
in this group. To be honest, I don't know if what I want to achieve is
possible, or even makes sense, so I wanted to get some
opinions/suggestions. Here's the scenario:

I have several reports in RS 2005, all of which are based on my AS
Cube. For the most part, the reports are simply to show aggregated data
from the cube. However, on each report there are several
calculations/forecasts/trends/etc. To simplify things, I'll just focus
on one report. This report will be based on a matrix, using all the
days in 1 month as the rows. For the columns, there is a column for
each sales division. The data to be displayed is determined by a report
parameter. Options include # of Sales, Profit, Cost, $ of Shipments,
etc., etc. This is all determined in the MDX query so that when data is
retrieved from the cube, it always gets put into an aliased field, so
only that field gets displayed in the main body of the report.

Ex.
WITH MEMBER [Measures].[Data] AS (
IIF(@pField="shipments", [Measures].[Num Shipments],
IIF(@pField="revenue", [Measures].[Revenue],
IIF(@pField="cost", [Measures].[Cost],
IIF(@pField="profit", [Measures].[Profit],
........
)))) SELECT NON EMPTY { [Measures].[Data] } ON COLUMNS,
........

The problem however, is when it comes to forecast & trend calculations.
The user has the option of selecting a total of 11 different options
for the pField parameter, and each trend calculation for each different
measure is a single "calculated member" in the cube. Since we have a
good number (so far 23 and counting) of different calculations in the
cube, that means for each one we have 11 different ones; one for each
potential measure. Because of this, the number of calculated members is
growing to be quite unmanageable (i.e. 11 for same period last year, 11
for same period last month, etc). That being said, this is what I'm
hoping is possible: When the user selects a value for the pField
parameter on the report, the report can somehow tell AS what measure it
wants, and then the calculated member can determine what measure we're
dealing with. That way, for each different calculation, we only have
one calculated member in AS that can dynamically determine what measure
to base itself on. If that was the case, we'd then be back down to only
23 calculated members, which would make life *much* easier in AS. It
would also make the reports much cleaner, as there wouldn't have to be
so much logic in the report.

Again, I don't know if this is possible, but if anyone has any ideas on
this, I would really appreciated their input. Thanks very much in
advance,

Martin McCarthy

Similarly, there is another set of calculations for Trends that this
could impact significantly. On a different report, it shows the current
values as indicated on the above report example, and also shows the
same for the last 11 months broken down by months (i.e. totals for
current month -1, current month -2, etc.). In the AS Cube, there is a
calculated member that looks something like this:

(ParallelPeriod([Dim Time].[Year - Month - Day].[MonthNumberOfYear], 1,
[Year - Month - Day].CurrentMember), [Measures].[Num Shipments])

There are then a whole bunch more for virtually the same thing, except
the period # changes from 1 thru 11, as per how many months to go back.
This is all repeated yet again, one for each measure. It would be great
if the number of periods in this example could be dynamic, as well as
what measure to use. Since I have such a ridiculous number of
calculated members in the cube, that leads me to believe I'm doing
something wrong....

Hi Martin,

At least some of what you're describing: "(i.e. 11 for same period last
year, 11 for same period last month, etc)" could be simplified using
Time Intelligence. Each such calculated member could apply across all
measures:

http://msdn2.microsoft.com/en-us/library/ms175440.aspx
Wizard

The time intelligence enhancement is a cube enhancement that adds time
calculations (or time views) to a selected hierarchy. This enhancement
supports the following categories of calculations:

Period to date.

Period over period growth.

Moving averages.

Parallel period comparisons.

You apply time intelligence to cubes that have a time dimension. (A time
dimension is a dimension whose Type property is set to Time).
Additionally, the time attributes of that dimension must also have the
appropriate setting (such as, Years or Months) for their Type property.
The Type property of both the dimension and its attributes will be set
correctly if you use the Dimension Wizard to create the time dimension.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,

Thank you very much for the information. I've added the "Time
Intelligence" just as described in the msdn article you've identified,
but I can't seem to get it to do anything useful. I followed the steps
in the wizard and added month over month growth and year over year
growth, selected the appropriate measures, and completed the wizard.
However, when I deploy the cube, I don't see new measures as I was
expecting to. In my Time dimension, I see a new group for Calculations,
but I don't think I understand how these are to be used. I was
expecting to see new calculated measures, but I guess this is not the
case? Also, can these "time intelligence" enhancements handle more than
1 period back, or are they limited to just that? I'm hoping it's not
limited, because if that's the case, the second report I mentioned
above is going to be a nightmare in the cube.

What is a reasonable number of calculated members to have in the cube?
(I realize this will vary depending on requirements, but generally
speaking....) Is having a few hundred ridiculous or common?

Thanks again,

Martin

Hi Deepak,

Thank you very much for the information. I've added the "Time
Intelligence" just as described in the msdn article you've identified,
but I can't seem to get it to do anything useful. I followed the steps
in the wizard and added month over month growth and year over year
growth, selected the appropriate measures, and completed the wizard.
However, when I deploy the cube, I don't see new measures as I was
expecting to. In my Time dimension, I see a new group for Calculations,
but I don't think I understand how these are to be used. I was
expecting to see new calculated measures, but I guess this is not the
case? Also, can these "time intelligence" enhancements handle more than
1 period back, or are they limited to just that? I'm hoping it's not
limited, because if that's the case, the second report I mentioned
above is going to be a nightmare in the cube.

What is a reasonable number of calculated members to have in the cube?
(I realize this will vary depending on requirements, but generally
speaking....) Is having a few hundred ridiculous or common?

Thanks again,

Martin

Martin,

These Time Intelligence calculated members are set up on the Time
dimension, so you would use them in conjunction with existing measures -
they can potentially apply across different measures, but you have to
check their scope in the cube MDX script. There are more details in this
SQL Server magazine article:

http://www.sqlmag.com/Articles/Print...rticleID=46157
The Business Intelligence Wizard makes time analysis a snap

Mosha Pasumansky,
Robert Zare
InstantDoc #46157
June 2005
...
To adjust the number of periods back, one approach would be to create a
"Period" dimension just for making such a selection. The Time
Intelligence calculated member definitions could then incorporate the
"Period" selection.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,

I understand now how the Time Intelligence is supposed to work. Thanks
for that article by Mosha. It explains it much better than the SQL
Server help.

While this approach does help to simplify things, it still doesn't
address one of my main concerns. After talking the problem over with
another developer, I realize didn't really describe my initial question
that well. Basically, because it is possible for the user to select so
many different combinations of things on the reports, we have a ton of
logic in the reports, as well as a ton of calculated members in the
cube. Further, a good many of the reports are quite similar, (but
different enough to be a separate report), and as such, once this phase
of development is complete, these reports are going to be an absolute
nightmare to maintain because there is so much logic in the MDX queries
of each report (over 100 nested IIF statements). Obviously the MDX in
the reports can be completely parameterized, but is there any way to
take that parameterization to the cube level? That way, all the logic
would reside in the cube, making future maintenance much quicker and
easier. Also, if I was able to pass parameters all the way down to the
cube level, I could significantly reduce the number of calculated
members in the cube, further simplifying maintenance. Is this at all
possible?

Thanks,

Martin

Hi Martin,

Not sure that I understand what you mean by "take that parameterization
to the cube level" - are you referring to generating multiple cubes,
based on user parameters?

But taking the bigger picture - when your users have such diverse and
dynamic requirements, why use static reports as the end-user tool? Why
not let users "self-service" with a flexible true OLAP front-end, which
can also help end-users generate the necessary MDX? Then you simply get
out of their way, rather than designing so many static reports.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,

Actually, what I meant by "take that parameterization to the cube
level" is can we somehow pass parameters from RS to the calculated
measures within the cube to use in that MDX/assembly code and then use
those parameters just as you normally would in the MDX code in a
report?

I really wish we could do like you suggest and let the users create
their own reports using Report Builder. However, we have very specific
requirements we must follow, as the customer wants the users to have a
certain base structure (much more so than simply a report model) and
then they just choose the appropriate options. I have experimented with
Report Builder, but it's just not going to be acceptable to the
customer.

Thanks again,

Martin

Martin--

Hope you don't mind me popping my question in here...but I *think* we
might be on the same track. I'm trying to avoid doing all of the
calculations from the Cube Editor to keep my processing time
low......so I'm gonna jump in as I think Deepak is making reference to
this very idea:


*****But taking the bigger picture - when your users have such diverse
and
dynamic requirements, why use static reports as the end-user tool? Why
not let users "self-service" with a flexible true OLAP front-end, which

can also help end-users generate the necessary MDX? Then you simply get

out of their way, rather than designing so many static
reports.**********

YES YES YES!!! This is what I am trying to do, but am having a lot of
difficulty finding good reference samples...

Here is my issue:

My Issue Example:

Dimensions=OrderAgent, TransactionDate, TotalCalls, OrderCount,
OrderTotal, CancelledCount, CancelledTotal

Within Web Pivot Table Query:

FILTER=TransactionDate

COLUMN=TotalCalls, OrderCount, OrderTotal, CancelledCount,
CancelledTotal

ROW=OrderAgent



What I want to do is create, within the query (NOT within Cube Editor)
2, additional columns. One for Conversion Rate, where
Conversion=OrderCount/TotalCalls as a percentage AND one for Cancel
Rate, wher Rate=CancelledCount/OrderCount (percentage)

I see how to create a new column with an expression, however, I want to
reference specific column values within this calculation.

So far, I'm only seeing info about how to do this in Cube Editor--but I
want my end users to be able to create their own Cube Reports with this
kind of functionality...I don't want to have to process the entire cube
every time.

Any help much appreciated!