Hi,

I have a problem with a dimension relation from my facttable.

My facttable contains both values and NULL values in the field which is
related to my dimension. The dimension is a Snowflake Schema containing two
tables.

The problems is that none of my records with null value in the dimension
relation field is being displayed. What am I doing wrong?

Best regards

Henrik Skak Pedersen.

As I recall you need to do something about it when cleansing in the
sql-environment.

You have to make a 0 dimension value in your staging / subject and change
from null to 0.

Circlecapital ? Jeg har været på HR kursus hos jer for nylig sammen med
Troels Schmidt...

\Michael V.

"Henrik Skak Pedersen" <hsp (AT) circlecapital (DOT) com> skrev i en meddelelse
news:%23HUKmnXPEHA.540 (AT) TK2MSFTNGP11 (DOT) phx.gbl...

Hi Michael,

Ja lige præcis, det var et sjovt sammentræf :-)

I am not quite sure what you mean, could you please explain a bit more. I
have tried to return a value from my facttable instead of the NULL value but
with same result.

Henrik,

"Michael Vardinghus" <michaelvardinghus (AT) notexisting (DOT) com> wrote

To load the cube, AS creates Inner Joins between the fact table and the dimension tables
So all values in the fact table dimension fields, including 0 if you use it, should exist in the dimensional tables to be loaded, otherwise they are ignored
Usually you create a member in the dimension table with a Name such as Not Available or Not Applicable
Then you have the option of updating NULLs in the fact table so that they point to that member, or using a view of the fact table that performs the replacement
HTH
Bria
www.geocities.com/brianaltmann/olap.htm

Hi Brian,

Thanks very much for you reply. Would you fysically create the record in the
dimension table?

Regards,
Henrik.

"Brian Altmann" <anonymous (AT) discussions (DOT) microsoft.com> wrote

ignored.
replacement.

I would...and I did...and you need physically to change it in the fact table
to....

"Henrik Skak Pedersen" <hsp (AT) circlecapital (DOT) com> skrev i en meddelelse
news:eEB0csmPEHA.3708 (AT) TK2MSFTNGP10 (DOT) phx.gbl...

Brian is right. By definition a dimension record has a primary key which is used to join to the fact table. Primary Keys can not be null. This is one reason why it is advantageous to use surrogate keys, so when the business key is null, the surrogate key can be a well formed unique integer (and the subsequent join to the fact table efficient). Make sure you use the surrogate key in the fact table not the business key.

Hi,

Thank you all for all your replies.

I am not so fond about creating a new record in my dimension table, because
then the user would be able to select it in my application. Is there not a
way where I can insert the record dynamically using SQL or in the OLAP
manager when I process the cube?

Henrik.

Yep.

Need to know if the dimension we are talking about here consists of multiple
levels - if it does you will need to add
a dummy dimension record on all levels.

Example of making a dummy dimension at one level:

insert into dim1
(dim1code,dim1name)
select '0', 'Unknown' where
not exists (select '0' from dim1)

Looks a bit awkward but it works....

The example above is for making the dimension in the staging area ..
afterwards you need to transport it to subject which is the
end database for olap - i do it this way:

Insert all records from the staging dimension table to the subject dimension
table - the subject dimension table has a surrogate
key (which I dont' insert - it's just a key counter that is defined at table
definition). This surrogate key is then used to map to the
fact table instead of the original key code.

When filling up the fact table i join to the staging fact and subject
dimension table to replace original dimension codes with new
surrogate key codes.

\Michael V.


"Henrik Skak Pedersen" <hsp (AT) circlecapital (DOT) com> skrev i en meddelelse
news:uJw8d4jQEHA.368 (AT) TK2MSFTNGP09 (DOT) phx.gbl...

Hej Michael,

Thank you very much for your reply.

I have one with multiple and one with a single table.

OLAP is very new to me so I may be missing some basic concepts. So I have
som e questions to your reply.

1) So before you process you cube you transfer the dimension table into a
new dimension table containing just the fields you need + the dummy record?
2) Are you doing that automatically?
3) If I run the code snippet which you have written do I get (0 row(s)
affected). I have done it like this:
insert into FactStrategyFocusArea
(StrategyFocusAreaId, Description)
select '0', 'Unknown' where
not exists (select '0' from FactStrategyFocusArea)
4) So the scenario for a table called StrategyFocusArea containing two
fields StrategyFocusAreaId and Description would be:
DROP TABLE FactStrategyFocusArea
SELECT StrategyFocusAreaId, Description INTO FactStrategyFocusArea
FROM StrategyFocusArea
insert into FactStrategyFocusArea (StrategyFocusAreaId, Description)
VALUES ('0', 'Unknown')

This of course works but is it the best way of doint it or was your idea
different from my solution?

Henrik.

"Michael Vardinghus" <michaelvardinghus (AT) notexisting (DOT) com> wrote