Hi,

First a piece of our databasedesign to make things clear:

Website(id, name, ...)
PageGroup(id, name, website_id, ...)
Page(id, name, pagegroup_id, ...)
PageViewFact(id, page_id, pagegroup_id, website_id, ...)

As you can see we have the following relation PageView -> Page ->
PageGroup -> Website. The facttable is PageViewFact, the rest are
dimensions. As a measure we have the id of PageViewFact. With OLAP we
want to get the number of pageviews for a specific website. This has
to be a total and then per pagegroup (that belongs to the website).
For example:

Total | General | Products | Forms
------+---------+----------+---------------
1000 | 500 | 250 | 250

All listed columns belong to one single website, say the website with
id "2". The thing is that the query we use, results in a list of all
pagegroups in the PageGroup table. Pagegroups that don't belong to the
website, don't have a value though.

We use the following MDX-query:

SELECT {[pageGroupDim].[Pgr Name].Members} ON COLUMNS,
{[Measures].[pvf id]} ON ROWS
FROM PageViewCube
WHERE [websiteDim].[All websiteDim].[2]

Our question:
What is the proper MDX-query to return a result like the given example
above. In other words, only the specific pagegroups, without the
pagegroups from other websites.

Thanks in advance!

Cheers,

Jeroen

Add the "non empty" key word(s) to your axis, like so...

NON EMPTY {[pageGroupDim].[Pgr Name].Members} ON COLUMNS,

tom @ the domain below
www.tomchester.net


"Jeroen Oosterlaar" <j.oosterlaar (AT) hccnet (DOT) nl> wrote