Hi all,

I am fairly new to cubing, even though i have created a few simple cubes in
the past.
However, I have a question with regards to a problem I am experincing when
adding in 3 similar dimensions and would like some advice on how to resolve
the problem.

I have the following dimensions (plus others but not listing all of them).
(Star schema)
Operator (userid)
OperatorCloser (userid)
OperatorResolver(userid)

Fact = Operator, OperatorCloser and OperatorResolver and then my measures.

The data for all 3 of these are the same but different in eachcase.
e.g. User1, User2, User3 OR User2, User2, User1 etc...

Now, when i have just 1 dimension (operator) then all is good. But , when i
bring in a second dimension (OperatorCloser or Resolver) my records counts
are hugely reduced.
I understand why in the fact that the joins used by Analysis Services will
perform a join on the Users at all all levels across all dimensions...

I hope this is clear? Basically i need to know how i can have all 3
dimensions and still return all my rows from my fact table, i guess almost
and outer join of some sort (in sql )

Regards
Immy

Immy, this is really a SQL issue. You need to create views for each of those
dims, and join those seperate views to their respective columns in the fact
table.

In other word, create the following views:

-Operator
-OperatorCloser
-OperatorResolver

...as select * from the dim table. Yes, their data is exactly the same, but
because they're seperate instantiations of the data, you can join them to
different columns in fact without any issues.

Yukon can handle this without the seperate views, but for now, in 2000,
here's how to get around it.

Good luck.

- Phil


"Immy" wrote:

Thanks for your response Phil.

The way you have suggested is what I alreay have in play.
I have the exact views as you've described and I would expect them to work
too, but they're not.
The row count from my fact table is almost 50% less than when I have only 1
of these dimensions in use.

It's almost like AS performs a full join on ALL views/tables at query time
which is limiting my result set.

Confused!
Immy


"SQL McOLAP" <SQLMcOLAP (AT) discussions (DOT) microsoft.com> wrote

Immy,

AS does indeed do full inner joins. One common way to handle this is to
setup a dimension member called something like "Unspecified" or
"Unknown". I then either map to these members during the transformations
or using a view over the fact table that coalesces nulls to the key for
these members.

Note: AS2k5 has a built-in feature to handle this situation.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <O0Uy5Ag3FHA.2676 (AT) TK2MSFTNGP15 (DOT) phx.gbl>,
imtiaz_ullah (AT) hotmail (DOT) com says...

As Darren points out, the best approach (if you need to catch this from a
business requirement point of view) is to load AS through views and have the
view do an outer join which changes the FK pointer to the proper "unknown"
member. However, I feel compelled to point out that in most all cases, this
is caused by a data quality problem (your situation might be different, but
I still assert that it is normally related to data quality). And inproving
the data quality is a function of the ETL process -- not the AS engine. Most
folks run into this when they try to directly link their AS system with the
OLTP system without implementing a full-blown ETL process. You really should
be fixing this as part of your ETL.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Darren Gosbell" <xxx (AT) xxx (DOT) com> wrote

I agree Dave! This was my initial response to the customer.
I hear all the suggestions and will try to use them. Thanks for all the
responses.

Immy

"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote