Hi, I have a problem designing a cube in SSAS 2000. I would like to know, if
it's possible, how can I define an outer join between a fact table and a
dimension table (left outer join or right outer join). In the Cube Editor I
can only make a join, an inner join, and I don't want to lose any information
from the fact table.

Thanks

no, not possible

you MUST insure a good data quality to load the cube correctly.

The easy way:
add an "unknown" member in the dimensions where you'll don't have
information and convert "null" values to the "unknown" value in the fact
tables.
in my case I lways reserved the key column "-1" to handle unknown members
and my fact tables are loaded with -1 instead of null values, which result
in a good data quality, a fast load of the cube and a better control of the
data presented.

AS2005 can do it by himself, not found members are can be converted
automatically to unknown members.



"Ray" <Ray (AT) discussions (DOT) microsoft.com> wrote

yeah you simply write a view to do this and then you scan the larger
view instead of the dimension table

we've got 500 cubes here; 20 servers and 10-20 olap developers.

some cubes take a long time to process; but it's better for a bunch of
reasons.

can you show me how you are exactly doing this?

Are you modifying the underlining table of the dimension to add an unknown
column?

I need to use this method to duplicate a left join functionality.

I have to add another dimension to a cube but it is causing changes amount
sum measures which is not what is wanted.

Hello,

Can you explain further?


you wrote:

The easy way:
add an "unknown" member in the dimensions where you'll don't have
information and convert "null" values to the "unknown" value in the fact
tables.
in my case I lways reserved the key column "-1" to handle unknown members
and my fact tables are loaded with -1 instead of null values, which result
in a good data quality, a fast load of the cube and a better control of the

data presented.

Are you modifying the underlying table of the dimension tables?

adding the "-1" key in the dimension table can be done in the table himself
or in the DSV.
instead-of using the table, create a named view and do a union all
select col1, col2, col3.... from table
union all
select -1, 'Unknown', 'Unknown'....

for your fact table, add a new calculated column where you do something
like:
newcolumnkey = isnull(mycolumnKey,-1)
(you convert nulls to -1)

and you have do the cleansing without changing the tables, but I reconnad to
clean your fact and dimension table.
When I load my datawarehouse, I always replace NULL values by "-1"
I prefer this because I can control the labels more easier and with more
detail then AS can do.



"Sonya" <sam (AT) samNOSPAM (DOT) com> wrote

You have to add a record to each of your dimension tables for which you
may have "unknown" values. (I like to use the same key, often -1 for
all the "unknown" members)

Then you either write the key value of this unknown member (eg. -1) into
the fact table as you are loading fact records. Or you could put a view
over your fact table which uses COALESCE() or ISNULL() to convert null
dimension key values into the value for the "unknown" member (eg. -1)
and then use the view, instead of the raw fact table in your cube.

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <18ece85996e944b182377581fbc8db65 (AT) ureader (DOT) com>,
sam (AT) samNOSPAM (DOT) com says...

The fact table is clean but I got a new business rule to include another
dimension table and when I do it creates duplicates. When I run the query QA,
I used left join to remove the problem but I can't do this in AS. I was
trying to come up with a way to perform this action.

"Jéjé" wrote:

ooohhh.... its a many-many issue.

in AS2005 there is an easy solution:
create a fact which contain the duplicated rows, and the other fact which
is NOT linked to the new dimension you want (so there is no duplicate)
create a new group of measures based on the new fact, link the new dimension
to this measure group (create only 1 and hidden measure "countofrow" if you
don't want to do anything else with this measure group)
in the dimension mapping you see the new dimension linked to the new measure
group and not linked to the old measure group.
now, click to link the new dimension to the old measure group and select the
"many many" relationship.
its done.

read this:
http://www.sqlserveranalysisservices...mensionsV2.htm


"SAM" <SAM (AT) discussions (DOT) microsoft.com> wrote

How is this accomplish in AS2000? We will be migrating in a few months but
until then I need to get this to work because this continually to come up and
I need to be able to use outer joins in the cubes.

"Jéjé" wrote: