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!

Different row delimiters in same connection manager

I have a situation where two CSV dmited types are read in. One file row type could be CRLF while another could be CR. Seeing how the only difference between these file types is the row delimiter, I would like to use one conn manager. Is it possible? Ideas?

Try this:
create a string variable Delimiter.
In Connection Manager window select your csv connection and look at its properties.
Open Expressions form and add an expression to the property RowDelimiter setting it to the variable User::Delimiter
Then you should be able to progamatically switch from one delimiter to another (toggling the variable value) before starting to load from the csv.
(i cannot try now this solution, take is as a hint)
|||

Unfortunately, this solution will not work. The RowDelimiter property is used only initially when no flat file connection columns are defined. After the columns get created the actual row delimiter is a column delimiter of the last column. You cannot do the same trick with the column delimiter because it is not expressionable.

Are you only concerned about the redundancy (with the two connection managers), or you had in mind more elegant data flow (with a multi flat file connection and one flat file source in a loop, perhaps)? I would like to find out more about your motivation to merge those two connection managers.

Thanks,

|||I am looking mainly to cut down the redundancy. There are 100+ inbound conns that need to support CRLF and CR returns, and it would end up being way too redundant to create a seperate manager for each return.|||

I can settle with just looking for CR for row delimiter. So, is there a way to maybe pad (or ignore) any LF's?

|||You can try that. The additional LF character would wrap up to the start of the first column of the next row. You should be able to clean it up using the Derived Column transform. However, I suspect it will make a headache with the incomplete last row, which would consist of a single LF character.

The solution I would go with is to have a Script Task before the Data Flow and preprocess files in it -- to unify the row delimiters.

Thanks,|||

Yah, I thought about doing this, however, won't this drain performance by having to essentially read/write the file twice (keeping in mind that some of these files can be rather large in size)? How would you handle the pre-processing in script?

Thanks.

|||

It will certainly have some performance impact. You should probably do some tests to verify how acceptable the cost is for the added manageability of your packages.

In the script, I would look for CR character and if the next one is not LF I would insert it. If you can be sure about the files with expected initial format ({CR}{LF}), you can skip those.

Thanks,

Different behavior in 2000 and 2005 for same query

Hi there,

We are in the process of upgrading from SQL Server 2000 to 2005. During testing we came across the following situation.

To reproduce the issue you can do the following

Create this structure in a 2000 and 2005 server instances

CREATE TABLE test

(a int,

b varchar(30))

INSERT INTO test

(a, b)

VALUES (1, '2')

INSERT INTO test

(a, b)

VALUES (3, '3a')

INSERT INTO test

(a, b)

VALUES (4, '4')

Then run the following statement in both servers:

UPDATE test

SET a = ltrim(rtrim(b))

WHERE b NOT LIKE '%a%'

AND ltrim(rtrim(b)) <> a

In 2000 this last statement will execute with no problem and it will update one row, whereas in 2005 the following error message is given:

Msg 245, Level 16, State 1, Line 20

Conversion failed when converting the varchar value '3a' to data type int.

By looking at the execution plan it seems that 2005 first tries to evaluate ltrim(rtrim(b)) <> a and then excludes those rows containing a whereas 2000 first excludes those rows containing a and then evaluates the different than condition.

I know fixing this instance itself is easy but I’m more concerned about having to rewrite many more stored procedures where we find this same scenario; is there any setting that can be changed to avoid this?

Any guidance is much appreciated.

Thanks!

You could change the database Compatibility level to 80 (SQL 2000) and determine if that helps.

There is a significant difference between the versions query processing.

And SQL 2005 allows less implicit conversions (aka, careless programming that often contravenes security).

I suspect you will need to make the changes to avoid implicit conversions as soon as possible.