"Optional" Page Axis Dimension in MDX

"Optional" Page Axis Dimension in MDX

Post by Chet Crome » Wed, 20 Feb 2002 05:49:59



I'm developing a web application that lets our users analyze their data
using Analysis Services. Currently, it takes an MDX statement with COLUMN
and ROW axes and turns it into a simple HTML table. Now, I am in the process
of adding a PAGE axis to the MDX statement. All it's really going to do is
save some round-tripping to my web server and back; instead of receiving a
single X-Y table, the user will receive several (based on the number of
Pages) tables all at once. They'll be able to flip back and forth between
the different pages of data without reloading the entire page, which is
going to save a lot of time because of the speed of our WAN and all the
client-side script that's already in the page.

What I'm wondering is, is there a MDX function I can use to create a page
axis, even if one isn't specified? (#4 below) This new page-axis feature is
going to be real nice, but I won't ALWAYS want to use it. Sometimes I may
still only want ROWS and COLUMNS. Due to all the looping used to make the
HTML table(s), it's going to take a lot of (mostly duplicate, but slightly
different) code to create the 2 dimensional output (1 table) vs. the 3
dimensional output (multiple tables based on pages).

1. Here's something like my typical 3 dimensional MDX statement (will work
fine)
SELECT [Product].[Category].Members ON COLUMNS,
[Location].[State].Members ON ROWS,
[Date].[Year].Members ON PAGES
FROM [Product Cube]

2. But I may just want a two dimensional table, normally MDX'ed like this:
SELECT [Product].[Category].Members ON COLUMNS,
[Date].[Year].Members ON ROWS
FROM [Product Cube]

3. Now I realize I can do this by simply using a "spare" dimension at the
[All] level to get the 3 dimensions:
SELECT [Product].[Category].Members ON COLUMNS,
[Date].[Year].Members ON ROWS
[Location].[All] ON PAGES
FROM [Product Cube]

4. But what I'd "like" to do, because I don't always want to specify a page
dimension if I don't want on, yet I'd like to keep my code clean, is:
SELECT [Product].[Category].Members ON COLUMNS,
[Date].[Year].Members ON ROWS,
'Constant Expression' ON PAGES
FROM [Product Cube]

I don't know if there's anything I can use for "Constant Expression" in MDX,
and that's really what I'm looking for.

 
 
 

"Optional" Page Axis Dimension in MDX

Post by George Spoffor » Wed, 20 Feb 2002 05:12:30


Every axis needs at least one tuple, and that tuple has to come from some
dimension not otherwise put onto rows, columns, slicer, or other axes. #3 or the
m*equivalent is the only way to go.

As far as having separate code paths for handling 3 axes vs 2, can a user go
ahead and assign all dimensions to rows and columns, so that none are left for
pages? If so, you'll need the 2-axis code and 3-axis code anyway.


> I'm developing a web application that lets our users analyze their data
> using Analysis Services. Currently, it takes an MDX statement with COLUMN
> and ROW axes and turns it into a simple HTML table. Now, I am in the process
> of adding a PAGE axis to the MDX statement. All it's really going to do is
> save some round-tripping to my web server and back; instead of receiving a
> single X-Y table, the user will receive several (based on the number of
> Pages) tables all at once. They'll be able to flip back and forth between
> the different pages of data without reloading the entire page, which is
> going to save a lot of time because of the speed of our WAN and all the
> client-side script that's already in the page.

> What I'm wondering is, is there a MDX function I can use to create a page
> axis, even if one isn't specified? (#4 below) This new page-axis feature is
> going to be real nice, but I won't ALWAYS want to use it. Sometimes I may
> still only want ROWS and COLUMNS. Due to all the looping used to make the
> HTML table(s), it's going to take a lot of (mostly duplicate, but slightly
> different) code to create the 2 dimensional output (1 table) vs. the 3
> dimensional output (multiple tables based on pages).

> 1. Here's something like my typical 3 dimensional MDX statement (will work
> fine)
> SELECT [Product].[Category].Members ON COLUMNS,
> [Location].[State].Members ON ROWS,
> [Date].[Year].Members ON PAGES
> FROM [Product Cube]

> 2. But I may just want a two dimensional table, normally MDX'ed like this:
> SELECT [Product].[Category].Members ON COLUMNS,
> [Date].[Year].Members ON ROWS
> FROM [Product Cube]

> 3. Now I realize I can do this by simply using a "spare" dimension at the
> [All] level to get the 3 dimensions:
> SELECT [Product].[Category].Members ON COLUMNS,
> [Date].[Year].Members ON ROWS
> [Location].[All] ON PAGES
> FROM [Product Cube]

> 4. But what I'd "like" to do, because I don't always want to specify a page
> dimension if I don't want on, yet I'd like to keep my code clean, is:
> SELECT [Product].[Category].Members ON COLUMNS,
> [Date].[Year].Members ON ROWS,
> 'Constant Expression' ON PAGES
> FROM [Product Cube]

> I don't know if there's anything I can use for "Constant Expression" in MDX,
> and that's really what I'm looking for.

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

ISVs ? IT organizations: Find out how DSS Lab can speed your development!

 
 
 

"Optional" Page Axis Dimension in MDX

Post by Chet Crome » Wed, 20 Feb 2002 22:04:05


That's kind of what I thought. I've actually got one dimension that is
ALWAYS available only to myself (as the developer). It's a dimension that
determines which franchise the data is for (and I don't let franchises view
other franchises data). Normally I put this in as a slice in the WHERE
section, but I guess I could rather easily put it in the PAGE section if
there is no page dimension specified.

Thanks for the confirmation.

Chet


> Every axis needs at least one tuple, and that tuple has to come from some
> dimension not otherwise put onto rows, columns, slicer, or other axes. #3
or the
> m*equivalent is the only way to go.

> As far as having separate code paths for handling 3 axes vs 2, can a user
go
> ahead and assign all dimensions to rows and columns, so that none are left
for
> pages? If so, you'll need the 2-axis code and 3-axis code anyway.


> > I'm developing a web application that lets our users analyze their data
> > using Analysis Services. Currently, it takes an MDX statement with
COLUMN
> > and ROW axes and turns it into a simple HTML table. Now, I am in the
process
> > of adding a PAGE axis to the MDX statement. All it's really going to do
is
> > save some round-tripping to my web server and back; instead of receiving
a
> > single X-Y table, the user will receive several (based on the number of
> > Pages) tables all at once. They'll be able to flip back and forth
between
> > the different pages of data without reloading the entire page, which is
> > going to save a lot of time because of the speed of our WAN and all the
> > client-side script that's already in the page.

> > What I'm wondering is, is there a MDX function I can use to create a
page
> > axis, even if one isn't specified? (#4 below) This new page-axis feature
is
> > going to be real nice, but I won't ALWAYS want to use it. Sometimes I
may
> > still only want ROWS and COLUMNS. Due to all the looping used to make
the
> > HTML table(s), it's going to take a lot of (mostly duplicate, but
slightly
> > different) code to create the 2 dimensional output (1 table) vs. the 3
> > dimensional output (multiple tables based on pages).

> > 1. Here's something like my typical 3 dimensional MDX statement (will
work
> > fine)
> > SELECT [Product].[Category].Members ON COLUMNS,
> > [Location].[State].Members ON ROWS,
> > [Date].[Year].Members ON PAGES
> > FROM [Product Cube]

> > 2. But I may just want a two dimensional table, normally MDX'ed like
this:
> > SELECT [Product].[Category].Members ON COLUMNS,
> > [Date].[Year].Members ON ROWS
> > FROM [Product Cube]

> > 3. Now I realize I can do this by simply using a "spare" dimension at
the
> > [All] level to get the 3 dimensions:
> > SELECT [Product].[Category].Members ON COLUMNS,
> > [Date].[Year].Members ON ROWS
> > [Location].[All] ON PAGES
> > FROM [Product Cube]

> > 4. But what I'd "like" to do, because I don't always want to specify a
page
> > dimension if I don't want on, yet I'd like to keep my code clean, is:
> > SELECT [Product].[Category].Members ON COLUMNS,
> > [Date].[Year].Members ON ROWS,
> > 'Constant Expression' ON PAGES
> > FROM [Product Cube]

> > I don't know if there's anything I can use for "Constant Expression" in
MDX,
> > and that's really what I'm looking for.

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

> ISVs ? IT organizations: Find out how DSS Lab can speed your development!

 
 
 

1. MDX error: dublicate dimensions across (independent) axes: when calculating a query axis

When I run the query

with member [Measures].[Total Qty] as 'Sum(YTD(),[Measures].[Qty])'
select
    {[MeasureTime].[Hour].members} on columns,
    {[Measures].[Qty]} on rows
from Manufactured
Where ([MeasureTime].[Year].[1999].[Quarter 4].[November].[18])

I get the message "Unable to open cellset. OLAP server error: Formula error
(dublicate dimensions across (independent) axes: when calculation a query
axis)". Why ?

It works without the Where statement but I have to use a Where statement to
only get data from one day. How can I solve that ?

Eva

2. OLE DB in sql - server using asp

3. max of ("...","...","..")

4. 25014-NC-Raleight/Durham-ORACLE-DBA Skills-Clinical Trials-Oracle DBA

5. Stored Procedure with Optional "Where" Parameters

6. Problem seeking for records with ADO and SQL Server, Please Help

7. Error "optional features not implemented"

8. Position Open (Programmer / Analyst)

9. error "optional features not implemented"

10. Error:"optional feature not implemented"

11. Mensaje "Optional feature not implemented (#00)"

12. Error"optional features not implemented"

13. Error "optional features not implemented"