Dimensions, Table Joins, and Missing Elements

I have situation where I'm using a element in SQL table which down the hierarchy.
Such as:

Table1 primary key
|
Table 2 foreign key primary key
|
Table 3 foreign key

The column I using for a dimension is in table 3. Now the problem is that not every row in table 2 is going need to have data in table 3. In other words, all of table 2 primary keys are not necessarily going to have a reference foreign key in Table 3.
Now that is all correct and is the nature of the project I'm working on. In regular SQL, select statements work just fine... meaning that selecting an element in Table 3 would display that element and also make Table 3 act as a filter via an INNER JOIN.

Now the problem is that AS2005 throws an error when processing a cube like this. Now I can make SQL views and use them instead of tables to eliminate this problem. But is that the best way to overcome this? How would I implement something like an INNER JOIN in an AS cube?

Thanks!

AS2005 has several options in dealing with such RI issues, please read the following useful articles on this subject:

http://msdn2.microsoft.com/en-us/library/ms345138.aspx

http://msdn2.microsoft.com/en-us/library/ms170707.aspx

|||That's exactly what I needed to know. Thanks!

Dimensions, Table Joins, and Missing Elements

I have situation where I'm using a element in SQL table which down the hierarchy.
Such as:

Table1 primary key
|
Table 2 foreign key primary key
|
Table 3 foreign key

The column I using for a dimension is in table 3. Now the problem is that not every row in table 2 is going need to have data in table 3. In other words, all of table 2 primary keys are not necessarily going to have a reference foreign key in Table 3.
Now that is all correct and is the nature of the project I'm working on. In regular SQL, select statements work just fine... meaning that selecting an element in Table 3 would display that element and also make Table 3 act as a filter via an INNER JOIN.

Now the problem is that AS2005 throws an error when processing a cube like this. Now I can make SQL views and use them instead of tables to eliminate this problem. But is that the best way to overcome this? How would I implement something like an INNER JOIN in an AS cube?

Thanks!

AS2005 has several options in dealing with such RI issues, please read the following useful articles on this subject:

http://msdn2.microsoft.com/en-us/library/ms345138.aspx

http://msdn2.microsoft.com/en-us/library/ms170707.aspx

|||That's exactly what I needed to know. Thanks!

Dimension with two names for each Key

Hi!

I need to build a dimension that offers two names for each entry.

For example, I have an electronics sparepart, a simple resistor that is called "abc-xy-12345" as company internal part number and also maybe "Resistor 1.0 kOhm 0.5 W" for a human being. Finally, for me as database guy, this part is a 4 byte integer number, I understand this is preferred to using the 15 byte part number. The dimension will have around 10,000 members when fully loaded, if that is important for the decision how to do it.

So I need to have a dimension that allows some of the users need to see the part as part number since they need to do a VLOOKUP in Excel or similar with the data, while the other usergroups needs to build a report upon the data, and they want to see a "self speaking name".

I do not want to build two nearly identical dimensions, what other way can I use to accomplish this?

If I add the order number to the text property of the key attribute and have another attribute on the long clear name, should this attribute be based on the integer for key as well and use name for the text?

Hi Ralf,

maybe the fastest way is create two attribute both with Key = your integer code.

One with Name = Company Part Number Name and the other with Name = Self Speaking Name

Francesco

|||

I was thinking that too, but where do you set the Key attribute to (the one that is displayed with the little golden key) for the dimension onto?

Anyone of the two, does not matter? Or have the ID alone (invisible) as the key as "anchor" and add the two names each as attribute?

|||

Hi Ralf,

the attribute with the little golden key is the attribute that has Set Attribute Usage = Key (right click on the attribute to check). It's used by AS as "unique key" in the dimension and in your case it has KeyColumn = your integer key code column .

Because both your attribute are at the lowest granularity in your dimension (1 member for every integer key code), you can:

use the one with Set Attribute Usage = Key (the one with the little golden key) with NameColumn set to the column you prefer (Company Part Number Name or Self Speaking Name)

then add another attribute (with Set Attribute Usage = Regular) with KeyColumn = your integer key code column and NameColumn = the other description you have.

Francesco

Dimension Displayed

Product Dimension Table

ProdID

Prod A

Prod B

Prod C

Fact Table

Key ProdID Measure

1 ProdA 100

2 ProdB 200

When I process the cube the and drag the measure and Product dim to view the result, the result show as below only ProdA and ProdB only.

The result sure correct and no error.

Just dont know why the ProdC not show as expected at SSAS2000.

I try the setting for the "Show Empty Cells" , It just for showing purpose only, not for permenant at that cube.

Anyone know why ProdC not show at the result as permenantly?

Do I need to do any setting to able ProdC show at the result.

Thanks .

This is not controlled by the cube, it is upto the client tool whether or not it includes empty cells or not. SSAS2000 was the same. By default a lot of browsers do not show empty cells and you have to explicitly turn this option on. There is nothing you can set at the database/cube level to control this.

Difficult SQL Statment

Hello !

I habe 2 Tables

Table1: Orders
Fields: Ordernr, Opieces

Table2: Calloffs
Ordernr, Cpieces

In Table1 ordernr is primary key.
In Table2 the same ordernr can exist often

My problem
If the sum(Cpieces) < Opieces:
I have to create a new virtual calloff
with Cpieces = opieces - sum(cpieces)

Its too high for me.

Please help

Best regards
aaapaulOn 25 Jan 2006 07:50:57 -0800, lvpaul@.gmx.net wrote:

>Hello !
>I habe 2 Tables
>Table1: Orders
>Fields: Ordernr, Opieces
>Table2: Calloffs
>Ordernr, Cpieces
>In Table1 ordernr is primary key.
>In Table2 the same ordernr can exist often
>My problem
>If the sum(Cpieces) < Opieces:
>I have to create a new virtual calloff
>with Cpieces = opieces - sum(cpieces)
>Its too high for me.
>Please help
>Best regards
>aaapaul

Hi aaapaul,

Maybe something like this?

INSERT INTO Calloffs (Ordernr, Cpieces)
SELECT o.Ordernr, o.Opieces - COALESCE(SUM(c.CPieces), 0)
FROM Orders AS o
LEFT JOIN Calloffs AS c
ON c.Ordernr = o.Ordernr
GROUP BY o.Ordernr, o.Opieces
HAVING o.Opieces > COALESCE(SUM(c.CPieces), 0)

(untested - see www.aspfaq.com/5006 if you prefer a tested reply or if
this doesn't do what you want)

--
Hugo Kornelis, SQL Server MVP|||Hi Hugo !

Thanks - it works fine.

INSERT INTO POOL_LIEFERDAT(aufnr,werk,lstueck,ldatum)
SELECT a.aufnr,a.werk,a.voffenstueck -
coalesce(sum(l.lstueck),0),'31.12.2006'
FROM FAKT_AUFTRAG a
LEFT OUTER JOIN POOL_LIEFERDAT l
ON a.aufnr = l.aufnr and a.werk = l.werk
GROUP BY a.aufnr,a.werk,a.voffenstueck
HAVING a.voffenstueck > coalesce(sum(l.lstueck),0)

At the moment I am writing one record with the difference.

But in the future I want to write each time 4 records with

Record1: pieces = coalesce(sum(l.lstueck),0)/4, date = dateXY
Record2: pieces = coalesce(sum(l.lstueck),0)/4, date = dateXY + 1
months
Record3: pieces = coalesce(sum(l.lstueck),0)/4, date = dateXY + 2
months
Record4: pieces = coalesce(sum(l.lstueck),0),/4 date = dateXY + 3
months

I want to distribute the virtual call offs over the next 4 month !

Is it possible to make this with SQL ??

Paul|||I think I have to use a temporary table !

How can I define 4 variables with the date of the first day of the next
4 month ?

var1=1.2.06
var2=1.3.06
var3=1.4.06
var5=1.5.06

Thanks
aaapaul|||On 26 Jan 2006 01:36:09 -0800, lvpaul@.gmx.net wrote:

>Hi Hugo !
>Thanks - it works fine.
>INSERT INTO POOL_LIEFERDAT(aufnr,werk,lstueck,ldatum)
>SELECT a.aufnr,a.werk,a.voffenstueck -
>coalesce(sum(l.lstueck),0),'31.12.2006'
>FROM FAKT_AUFTRAG a
>LEFT OUTER JOIN POOL_LIEFERDAT l
>ON a.aufnr = l.aufnr and a.werk = l.werk
>GROUP BY a.aufnr,a.werk,a.voffenstueck
>HAVING a.voffenstueck > coalesce(sum(l.lstueck),0)

Hi Paul,

Don't use locale-dependent date formats in your code. It will cause
unexpected things to happen when SQL Server misinterprets the date
format you intended. Use yyyymmdd (20061231).

>At the moment I am writing one record with the difference.
>But in the future I want to write each time 4 records with
>Record1: pieces = coalesce(sum(l.lstueck),0)/4, date = dateXY
>Record2: pieces = coalesce(sum(l.lstueck),0)/4, date = dateXY + 1
>months
>Record3: pieces = coalesce(sum(l.lstueck),0)/4, date = dateXY + 2
>months
>Record4: pieces = coalesce(sum(l.lstueck),0),/4 date = dateXY + 3
>months
>I want to distribute the virtual call offs over the next 4 month !
>Is it possible to make this with SQL ??
>Paul

Yes, it's possible - and you don't need a temp table for it.

I'm not sure where dateXY comes from. Is that the date constant
(20061231) in the query above? Will it be a constant in the final query,
or is it taken from some other table?

Also - do you really want pieces to be a quarter of SUM(l.lstueck), or
should it be a quarter of a.voffenstueck - SUM(l.lstueck)?

Assuming that dateXY lives in the Auftrge table:

INSERT INTO POOL_LIEFERDAT(aufnr,werk,lstueck,ldatum)
SELECT a.aufnr, a.werk,
(a.voffenstueck - coalesce(sum(l.lstueck),0)) / 4,
DATEADD(month, Numbers.N, a.dateXY)
FROM FAKT_AUFTRAG a
LEFT OUTER JOIN POOL_LIEFERDAT l
ON a.aufnr = l.aufnr and a.werk = l.werk
CROSS JOIN (SELECT 0 AS N
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3) AS Numbers
GROUP BY a.aufnr, a.werk, a.voffenstueck, Numbers.N
HAVING a.voffenstueck > coalesce(sum(l.lstueck),0)

(If you already have a numbers table, you can use that insted of the
derived table).

If dateXY is fixed, you can simply use a derived table with the four
precalculated dates instead of a numbers table and the DATEADD function.

NOTE: Queries are untested. See www.aspfaq.com/5006 if you prefer a
tested solution.

--
Hugo Kornelis, SQL Server MVP|||Hallo Hugo !

Thank you. Its fine.

I read a very interesting article about sql and datetime at
www.insidesql.de. I will use the unseparated format in the future.

I will check your SQL-Statment.

Paul|||All right now. Thank I have learned new possibilities:

Paul

My code:

declare @.dat1 as datetime

-- dat1 = 1. Tag vom nchsten Monat
set @.dat1 =
dateadd(month,1,(CAST(CONVERT(char(8),CURRENT_TIME STAMP,112) as
datetime) - Day(CURRENT_TIMESTAMP)+1))

INSERT INTO POOL_LIEFERDAT(aufnr,ldatum,lstueck,werk)
SELECT a.aufnr,dateadd(month,numbers.n,@.dat1) as ldatum,(a.voffenstueck
- coalesce(sum(l.lstueck),0))/4 as lstueck,a.werk
FROM FAKT_AUFTRAG a
LEFT OUTER JOIN POOL_LIEFERDAT l
ON a.aufnr = l.aufnr and a.werk = l.werk
CROSS JOIN
(SELECT 0 AS N
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3) AS NUMBERS
-- Achtung verursacht FEHLER WHERE not(l.werk is null)
GROUP BY a.aufnr,a.werk,a.voffenstueck,numbers.n
HAVING (a.voffenstueck > coalesce(sum(l.lstueck),0))
Order by a.aufnr|||On 27 Jan 2006 00:55:43 -0800, lvpaul@.gmx.net wrote:

>Hallo Hugo !
>Thank you. Its fine.
>I read a very interesting article about sql and datetime at
>www.insidesql.de. I will use the unseparated format in the future.
>I will check your SQL-Statment.
>Paul

Hi Paul,

I was going to point you to Tibor Karaszi's ultimate guide to the
datetime datatype, but I see that a German translation of it is
available at the insidesql site - probably exactly the article that
you're refering to!

--
Hugo Kornelis, SQL Server MVP|||On 27 Jan 2006 06:22:41 -0800, lvpaul@.gmx.net wrote:

>All right now. Thank I have learned new possibilities:
(snip)
>-- Achtung verursacht FEHLER WHERE not(l.werk is null)

Hi Paul,

What do you mean with the comment? Do you mean that you get errors with
the WHERE clause included, or that you get errors if you exclude it?

With this where clause, the LEFT OUTER JOIN is in effect reduced to an
INNER JOIN. This means that you will get correct information for rows
with at least one corresponding row in POOL_LIEFERDAT, but if you have a
FAKT_AUFTRAG with no rows yet in POOL_LIEFTERDAT, you won't get any rows
for it in the INSERT.

Removing the WHERE clause means that you'll also get four rows for each
FAKT_AUFTRAG with no corresponding POOL_LIEFERDAT.

Tch!

--
Hugo Kornelis, SQL Server MVP

differnce between a column that s a primary key and a column that s a "key/index with Isuni

Hi,

Please, What s the differnce between a column that s a primary key and a column that s a "key/index with Isunique=true"?

Thanks a lot.

EDIT

A Primary cannot be Null but Unique key can be Null. Check the links below for the documentation.

http://msdn2.microsoft.com/en-us/library/ms181043.aspx

http://msdn2.microsoft.com/en-us/library/ms191166.aspx

|||

Another difference is that if you use a UNIQUE constraint as a target for a foreign key reference you must explicitly reference the columns.

For instance, if you have a table "TableA" that has a primary key "TableA_PKCol", you can target that as a foreign key from table "TableB" with something like:

Code Snippet

Alter table TableB

add constraint FK_TableB__TableA

foreign key (TabkeA_PKCol)

references TableA

However, if you have a table "TableJ" that has a unique key "TableJ_UQCol", you must explicitly name that column to target this column as a foreign key target from "TableK" with something like:

Code Snippet

Alter table TableK

add constraint FK_TableK__TableJ

foreign key (TableJ_UQCol)

references TableJ (TableJ_UQCol)

Differential rows

I am using SSIS to replicate data from an AS400 mainframe to a SQL destination. I am using a lookup column to see if the primary key is duplicated and if not, it will INSERT the row. This is all working fine. What I need to know is can I also use the Lookup transformation to look for differential data and then UPDATE the row? The primary key of the table will never change, however the data might and I need the package to recognize that this is a modified row on the mainframe and that the same row on the SQL destination server needs to be updated.

Thanks for any useful information.

I believe that I found a solution to my own problem. It looks like I need to use the Slowly Changing Dimension transformation. So far in running the test data, this is exactly what I need.

Differential rows

I am using SSIS to replicate data from an AS400 mainframe to a SQL destination. I am using a lookup column to see if the primary key is duplicated and if not, it will INSERT the row. This is all working fine. What I need to know is can I also use the Lookup transformation to look for differential data and then UPDATE the row? The primary key of the table will never change, however the data might and I need the package to recognize that this is a modified row on the mainframe and that the same row on the SQL destination server needs to be updated.

Thanks for any useful information.

I believe that I found a solution to my own problem. It looks like I need to use the Slowly Changing Dimension transformation. So far in running the test data, this is exactly what I need.|||

Thank you so much for the answer to your own question! I've been using lookup and conditional split transformation to check if non-key columns have been modified. Writing an expression to compare all columns in a 40-column table has been making me go crazy. SCD does it all for you! In the literature I had in hand SCD is always used in the data warehouse context. Since I'm using IS for the data migration (only), I've never considered it as a transforamtion that could be of any use to me.

Different behavior between table designer and properties sheet.

When the foreign key constrain is in place, why does
table designer allow to make that field allow nulls, but
property sheet doesn't? Is there specific reason to have
them behave inconsistently?
Thanks you,
IKI'm not certain which properties sheet you are referring to but most people
take it for granted that Enterprise Manager has a number of slightly quirky
features. In common with many others, I tend to avoid using EM and make
schema changes in TSQL code.
--
David Portas
SQL Server MVP
--|||Hi David,
In EM, "Table Properties" screen is shown by double click
on table or right-click/Properties. "Design Table" -
right-click/Design Table. As for me, it is a flaw in EM
Design Table feature to allow nulls for the field with
foreign key constraint, thus allowing orphan records in a
child table.
Best Regards,
IK
>--Original Message--
>I'm not certain which properties sheet you are referring
to but most people
>take it for granted that Enterprise Manager has a number
of slightly quirky
>features. In common with many others, I tend to avoid
using EM and make
>schema changes in TSQL code.
>--
>David Portas
>SQL Server MVP
>--
>
>.
>|||> As for me, it is a flaw in EM
> Design Table feature to allow nulls for the field with
> foreign key constraint, thus allowing orphan records in a
> child table.
No, that feature is by design. In ANSI/ISO Standard SQL, all columns can be
nullable except Primary Keys. That includes Foreign Keys. You can decide
either to allow or disallow NULLs depending on your requirements.
--
David Portas
SQL Server MVP
--|||That explained a lot, thank you. And I wish EM would be
more consistent in enforcing those standards across all
its features.
Best Regards,
IK
>--Original Message--
>> As for me, it is a flaw in EM
>> Design Table feature to allow nulls for the field with
>> foreign key constraint, thus allowing orphan records
in a
>> child table.
>No, that feature is by design. In ANSI/ISO Standard SQL,
all columns can be
>nullable except Primary Keys. That includes Foreign
Keys. You can decide
>either to allow or disallow NULLs depending on your
requirements.
>--
>David Portas
>SQL Server MVP
>--
>
>.
>

Different behavior between table designer and properties sheet.

When the foreign key constrain is in place, why does
table designer allow to make that field allow nulls, but
property sheet doesn't? Is there specific reason to have
them behave inconsistently?
Thanks you,
IK
I'm not certain which properties sheet you are referring to but most people
take it for granted that Enterprise Manager has a number of slightly quirky
features. In common with many others, I tend to avoid using EM and make
schema changes in TSQL code.
David Portas
SQL Server MVP
|||Hi David,
In EM, "Table Properties" screen is shown by double click
on table or right-click/Properties. "Design Table" -
right-click/Design Table. As for me, it is a flaw in EM
Design Table feature to allow nulls for the field with
foreign key constraint, thus allowing orphan records in a
child table.
Best Regards,
IK

>--Original Message--
>I'm not certain which properties sheet you are referring
to but most people
>take it for granted that Enterprise Manager has a number
of slightly quirky
>features. In common with many others, I tend to avoid
using EM and make
>schema changes in TSQL code.
>--
>David Portas
>SQL Server MVP
>--
>
>.
>
|||> As for me, it is a flaw in EM
> Design Table feature to allow nulls for the field with
> foreign key constraint, thus allowing orphan records in a
> child table.
No, that feature is by design. In ANSI/ISO Standard SQL, all columns can be
nullable except Primary Keys. That includes Foreign Keys. You can decide
either to allow or disallow NULLs depending on your requirements.
David Portas
SQL Server MVP
|||That explained a lot, thank you. And I wish EM would be
more consistent in enforcing those standards across all
its features.
Best Regards,
IK
[vbcol=seagreen]
>--Original Message--
in a
>No, that feature is by design. In ANSI/ISO Standard SQL,
all columns can be
>nullable except Primary Keys. That includes Foreign
Keys. You can decide
>either to allow or disallow NULLs depending on your
requirements.
>--
>David Portas
>SQL Server MVP
>--
>
>.
>