Aggregations accross time periods

Aggregations accross time periods

Post by NCS » Sun, 09 Dec 2001 01:02:27



We use the following MDX query in a report:

SELECT NON EMPTY { [Location].[Location Nm].Members } ON COLUMNS,
{[Measures].[Employees Served]} ON ROWS
FROM [DEMAND] WHERE ([Company].[American International Group],
[Time].[Q3-2001])

The measure [Measures].[Employees Served] is a distinct count aggregation
defined in the cube.  How could we run a similar query that would allow the
user to specify a date range that does not coincide with a particular time
cell.  For instance, what if the user wanted [Measures].[Employees Served]
for the same criteria as above, but the time period is Nov-2000 through
Mar-2001?

Thanks

 
 
 

Aggregations accross time periods

Post by George Spoffor » Sun, 09 Dec 2001 00:30:15


You need to roll your own distinct count calculation in MDX.


> We use the following MDX query in a report:

> SELECT NON EMPTY { [Location].[Location Nm].Members } ON COLUMNS,
> {[Measures].[Employees Served]} ON ROWS
> FROM [DEMAND] WHERE ([Company].[American International Group],
> [Time].[Q3-2001])

> The measure [Measures].[Employees Served] is a distinct count aggregation
> defined in the cube.  How could we run a similar query that would allow the
> user to specify a date range that does not coincide with a particular time
> cell.  For instance, what if the user wanted [Measures].[Employees Served]
> for the same criteria as above, but the time period is Nov-2000 through
> Mar-2001?

> Thanks

--
George Spofford
Microsoft MVP
Chief Architect / OLAP Solution Provider
DSS Lab


 
 
 

1. how to program for period-to-period comparisons ?

Hi

What's the best way to generate period-to-period comparisons (e.g. for
variance analysis) where you want to generate 2 columns (with different
names, but otherwise similar data-type & values).  For example,

    SalesCurrent    SalesPreviousYear    Difference
    -------------    -------------------    -----------

The difference column, I can handle in the report writer; that part's easy
enough.

I was going to generate the "current" and "previous" columns from
parameterized stored procedures, since, assuming large data volumes, I don't
want to simply limit a huge, generic View on the (e.g. VB) client front-end.

In Access (with small data volume) I could join two queries in a third
query; but, Transact_SQL doesn't want parameters in Views (bummer  <G> ) ...
and I want to limit the initial recordsets, rather than use a parameterized
stored procedure (step 2) on huge, generic Views (step 1).

Although I have access to SQL Server 2000 (as an MSDN Developer), the data
resides on SQL Server 7 (precluding UDFs ? ).

I'm about to attempt this with "table variables" (i.e. declare variables of
data-type "Table"), but can't find examples in my books ("Guru's Guide to
Transact-SQL"/Ken Henderson, "Programming VB6"/Francesco Balena or the
"Hitchhiker's Guide to VB & SQL Server"/Bill Vaughn).  So, it occurred to me
that I might be approaching it all wrong?

The (spreadsheet) need to generate period-to-period variance comparisons is
so common that I was surprised to find NO Transact-SQL examples on it !!!

thanks, in advance, for whatever insights ...

Michael Coughlin

2. dts'ing constraints only

3. Facts that only exist in PERIODS of time

4. SQL Svr 2000 quoted_identifier problem

5. Time periods in a star schema?

6. Locking problems with VB 5.0

7. Precalculated values and time periods

8. SQL2K, Outlook & Security

9. Time Dimension for Fiscal Calendar with 13 periods

10. Parallel Periods and Relative Times ????

11. Consolidating Contiguous Time Period Rows into one!!!

12. Lock Request Time out Period ..

13. Should time period be datetime?