Dimension Health Check - w/ Oracle as datasource

Topics: Resolved
Jun 7, 2007 at 8:05 AM

Great job guys on putting this project together!

I tested the Dimension Health Check on some dimensions that use SQL Server as a datasource and it worked great.

I tried to use it on several dimensions that have Oracle 10g as the source...and kept getting an OLEDB exception for every attribute that was evaluated. I know the attribute relationships are correct on all the dimensions. Also, these dimensions process without error so I know the datasource and access accounts are OK.

Have you have been able to successfully test the Dimension Health Check on dimensions that are sourced from Oracle?

BTW, here is a copy of the error for one attribute:
Attempt to validate key and name relationship for attribute Hour attribute failed:ORA-01747: invalid user.table.column, table.column, or column specification at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForMultpleResults(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.DataWarehouse.Design.DataSourceConnection.Fill(DataSet dataSet, String sql, CommandBehavior commandBehavior) at Microsoft.DataWarehouse.Design.DataSourceConnection.Fill(DataSet dataSet, String sql) at BIDSHelper.DimensionHealthCheckPlugin.Check(Dimension d) SQL query was: select y.e862c500-1940-4aae-ab85-d8f84db6e120 as REPORT_DATE ,y.e45f0dc5-4e71-4792-969c-b42d0b040401 as REPORT_HOUR_NAME from ( select distinct e862c500-1940-4aae-ab85-d8f84db6e120 = DIM_HOURLY_TIME_VIEW.REPORT_DATE ,e45f0dc5-4e71-4792-969c-b42d0b040401 = DIM_HOURLY_TIME_VIEW.REPORT_HOUR_NAME from NPSROLLUP.DIM_HOURLY_TIME_VIEW as DIM_HOURLY_TIME_VIEW ) as y join ( select e862c500-1940-4aae-ab85-d8f84db6e120 from ( select distinct e862c500-1940-4aae-ab85-d8f84db6e120 = DIM_HOURLY_TIME_VIEW.REPORT_DATE ,e45f0dc5-4e71-4792-969c-b42d0b040401 = DIM_HOURLY_TIME_VIEW.REPORT_HOUR_NAME from NPSROLLUP.DIM_HOURLY_TIME_VIEW as DIM_HOURLY_TIME_VIEW ) x group by e862c500-1940-4aae-ab85-d8f84db6e120 having count(*)>1 ) z on coalesce(y.e862c500-1940-4aae-ab85-d8f84db6e120,'12/30/1899') = coalesce(z.e862c500-1940-4aae-ab85-d8f84db6e120,'12/30/1899')

As you can see, the query that is being sent to Oracle is messed up (GUIDS are being referenced instead of column names)....I know this is an OLEDB exception, but I'm curious as to what is messing the query up...is it OLEDB or ???

Thanks,
Mark Mrachek

Coordinator
Jun 7, 2007 at 5:02 PM
Oracle isn't supported at the moment (obviously). If you would like to help us tweak that query we might be able to support it in future versions.

The GUIDs are necessary because columns may not have unique names... so that gives them unique names.

If you would like to help, please fix that query with as few changes as possible and post back here. Please make sure the WIKI doesn't mess up the display of your query as shown above.
Jun 7, 2007 at 8:39 PM
I looked at the query briefly, one of the first things that I noticed is that the length of the guid exceeds the object name length in Oracle. By default, object names and aliases can't exceed 30 characters in Oracle.

Also, the syntax used to assign column aliases (e862c500-1940-4aae-ab85-d8f84db6e120 = DIMHOURLYTIMEVIEW.REPORTDATE) won't work in Oracle. You will most likely need to use the AS clause...or just follow the column name with the alias.

This is just a start. I suspect there will be additional things that crop up. I can continue to look into this in my spare time. Also, I'll take a look at the source code to see how the changes could be implemented with the least amount of impact.

Thanks,
Mark Mrachek
Jun 9, 2007 at 7:17 AM
Edited Jun 9, 2007 at 7:34 AM
furmangg,

Here is the altered query with 'minimal' changes. If you need further information, please let me know.

BTW, I tried to get the format of the queries to look better, but when the queries are posted, all spaces are removed. All brackets in the original query are hidden and have created links. In addition, all underscores have been removed by the WIKI. I tried to apply the tags 'do not apply formatting'....but those don't appear to be working. Am I missing something?

If you want, I can email you this information directly.



//ORIGINAL QUERY
select y.e862c500-1940-4aae-ab85-d8f84db6e120 as REPORT_DATE ,
y.e45f0dc5-4e71-4792-969c-b42d0b040401 as REPORT_HOUR_NAME
from
( select distinct e862c500-1940-4aae-ab85-d8f84db6e120 = DIM_HOURLY_TIME_VIEW.REPORT_DATE,
e45f0dc5-4e71-4792-969c-b42d0b040401 = DIM_HOURLY_TIME_VIEW.REPORT_HOUR_NAME
from NPSROLLUP.DIM_HOURLY_TIME_VIEW as DIM_HOURLY_TIME_VIEW ) as y
join
( select e862c500-1940-4aae-ab85-d8f84db6e120
from ( select distinct e862c500-1940-4aae-ab85-d8f84db6e120 = DIM_HOURLY_TIME_VIEW.REPORT_DATE ,
e45f0dc5-4e71-4792-969c-b42d0b040401 = DIM_HOURLY_TIME_VIEW.REPORT_HOUR_NAME
from NPSROLLUP.DIM_HOURLY_TIME_VIEW as DIM_HOURLY_TIME_VIEW ) x
group by e862c500-1940-4aae-ab85-d8f84db6e120
having count(*)>1 ) z
on coalesce(y.e862c500-1940-4aae-ab85-d8f84db6e120,'12/30/1899') = coalesce(z.e862c500-1940-4aae-ab85-d8f84db6e120,'12/30/1899')



//ALTERED QUERY
select y.TABYCOL1 as REPORT_DATE ,
y.TABYCOL2 as REPORTHOURNAME
from ( select distinct DIMHOURLYTIMEVIEW.REPORTDATE AS TABYCOL1,
DIMHOURLYTIMEVIEW.REPORTHOURNAME AS TABY_COL2
from NPSROLLUP.DIMHOURLYTIMEVIEW DIMHOURLYTIMEVIEW ) y
join
( select X.TABXCOL1
from ( select distinct DIMHOURLYTIMEVIEW.REPORTDATE AS TABXCOL1,
DIMHOURLYTIMEVIEW.REPORTHOURNAME AS TABX_COL2
from NPSROLLUP.DIMHOURLYTIMEVIEW DIMHOURLYTIMEVIEW ) x
group by X.TABXCOL1
having count(*)>1 ) z
on coalesce(y.TABYCOL1, TODATE('12/30/1899','MM/DD/YYYY')) = coalesce(z.TABXCOL1,TODATE('12/30/1899','MM/DD/YYYY'))


Minimal Changes Include:
1) Replaced GUID with TAB<SubqueryAlias><ColumnNumber> Example: TABYCOL1
2) Changed column alias assignment sytax from 'e862c500-1940-4aae-ab85-d8f84db6e120 = DIM_HOURLY_TIME_VIEW.REPORT_DATE' to 'DIMHOURLYTIMEVIEW.REPORTDATE AS TABYCOL1'
3) Removed the 'AS' clause when assigning the table alias
4) Added the TO_DATE() function so date datatypes can be compared in the join
5) Removed brackets ('' and '')...it's the only way I could get TOAD to run the query. I'm not sure this is necessary to remove the brackets in the OLEDB query that is sent to Oracle. I'll try to test this out when time permits.
Coordinator
Jun 12, 2007 at 10:24 PM
I don't think TODATE() is supported in SQL Server, Would something like CAST('1899-12-30' AS DATETIME) work in both SQL and Oracle. I know dates is one area where Oracle and SQL differ a bit, but I don't have an instance of Oracle to play against.
Coordinator
Aug 26, 2007 at 7:56 PM
Mark-

I have finished coding support for Oracle in the Dimension Health Check feature. I would love for you to test it out on all of your dimensions and make sure everything's perfect.

Note that the current release (1.1.0.1) does not include Oracle support. The next one will. So you'll have to download the source code from the source code tab then compile and deploy it yourself.