I have a fact table with a user id and a report id, date/time, etc.

i have a user table with the userid, name, etc.

i have a report table with the reportid, userid, and title. (users have
many reports, and dont share reports with other users).

i want to see what reports were created over time.

so ive made the cube that has the reportid as the measue.

it has the report time, the userinfo, and the pageinfo as dimensions.

the problem is it doesnt restrict the report dimension to the current user
id.

so you end up with every single report matched to the user, where 90% of
them are blank values since they dont belong to that user.

Still new at this stuff, hopefully im just overlooking something simple.

Thanks.

Hi,
if one user creates n reports and a report can only
belong to one user, you could consider creating one
single dimension containing users and reports like:

Report_Id | report | user

and create a single dimension off this.

HTH lutz Morrien

what i found yesterday was the NON EMPTY keyword while using an mdx query.
It works great, only pulling the data thats really needed.

My new concern is how the cube builds.

Users will never share reports. If the cube builds every combination, then
99% of the cube would be blank.

It sounds like thats what you are suggesting. Creating a dimension that
holds the report and the user.

Does that mean create a dimension with 2 levels? userid then reportid, or
reportid then userid?

I think when i tried that i was given a warning about a child having less
values than the parent...




"Lutz Morrien" <Lutz.morrien (AT) ocb (DOT) com.nonotthisbit> wrote

Since the relation between user and report is 1:n, User
would be top level and report would be below that level,
I guess.

That would make the nonempty obsolete.

HTH, Lutz Morrien