Category Archives: OWB

OWB patarimai #1

  1. Nenaudoti dimensijos atributo, kuris yra išorinis raktas agregavimo funkcijoje ar duomenų išvedimui. Tokiu atveju geriau sukurti dubliuotą atributą.
  2. Iš duomenų šaltinio į OWB įkelti visas lenteles ir visus jų stulpelius (jeigu jų nėra tūkstančiai). Dažniausiai tai apima vienos duomenų bazės ar schemos kontekstą.
  3. Mapping’uose naudoti tik tuos atributus, kurių reikia. Dėl greitumo nepermetinėti visų atributų iš vieno objekto į kitą, o paskui tik sujungti rodyklėmis, kuriuos reikia.

Oracle Warehouse Builder 10g pamokos

Ką tik pasikartojimui įveikiau pradinį OWB 10.2 tutorialą “Setting up the Oracle Warehouse Builder Project with OWB Database 10g Release 2“. Ta dalis su Oracle Workflow Business Intelligence SE1 10g atveju jau instaliuota ir yra schema su OWF_MGR. Naudinga. Eilėje laukia dar keli kiti OWB 10.2 tutorialai

Sekantis – supratau kaip reikia elgtis su OWB Experts Using Experts to Automate Warehouse Builder Tasks with OWB Database 10g Release 2. Pakankamai sudėtingas dalykas, pažengusiems naudotojams, jei norima pilnai išnaudoti galimybes.

Tada dar vienas, kaip pasikurti OWB  aplinką Starting with Oracle Warehouse Builder 10g Release 2. Čia import komanda, kurią galima panaudoti įkeliant xsales.dmp failą

REM xsales.dmp needs to be moved to C root.

cd C:oraclebise1dbbin
imp xsales/xsales@bise1db file=”C:xsales.dmp” full=y

Dar viena pamoka Extracting, Transforming and Loading Data with OWB Database 10g Release 2, parodantis kaip padaryti paprastą užkrovimą su Process Flow. Buvau užstrigęs ties klaida “Error ORA-22804: remote operations not permitted on object tables or user-defined type columns”, bet padariau apėjimą – tarpinę lentelę.

Intermediate table in mapping

Should I Import All Source Database Table Metadata to Oracle Warehouse Builder or One By One

In the beginning of BI project implementation I had this question:

Should I import all source database (Oracle schema) table metadata to Oracle Warehouse Builder? Or, just tables needed at this moment of implementation?

The answer is YES! You should import all tables from source database (Oracle schema). Why? Because BI system eventually should be used ant expanded by customer itself. Not by software development team, which was hired to develop project. By adding all tables from schema, you save yourself from running at client’s place and adding one by one table each time he wants to add extra column to fact or dimension.

When all database tables are in Warehouse Builder, then customer can add the same extra column to dimension or fact by it self.

Transform MSSQL Tables to Views for Input to Oracle Warehouse Builder

This code is written by myself. You can use it to generate views from tables in MSSQL database.

These generated views are input to Warehouse Builder as a source database objects.

The best part of it, that it does a bit of validation and transforms CamelCase notation (which is used in MSSQL database object names) to regular Oracle naming CAMEL_CASE. It does it for table name and column names.

So table name in MSSQL is  IncidentObjects, after running script it becomes RPT_INCIDENT_OBJECTS.

How to use it. Just copy this source and execute in MSSQL database. Script output is another MSSQL script. Run this output to generate views. These generated views then are imported to Warehouse Builder.


DECLARE @view_name nvarchar(100) = ''
DECLARE @table_name nvarchar(100)
declare @col_length integer = 30
declare @view_name_length integer = 30
declare @count int = 1
declare @regexp_pattern nvarchar(1000) = '(((?<=[a-z])[A-Z])|([A-Z](?![A-Z]|$)))'

-- Config section, case sensitive
/*

whole table list
declare @table_list nvarchar(max) = 'Addresses,Area,Cities,Classifiers_Vw'
*/

declare @table_list nvarchar(max) = ''

while (len(@table_list) > 0) -- fake condition
begin
 declare @temp_name nvarchar(100)
 set @temp_name = dbo.fnSplit(@table_list,',', @count)

 --print @count
 --print 'table: ' + isnull(@temp_name, 'previous table was last table.')

 if ((isnull(@temp_name, 'x')) = 'x')
 goto breaking

set @table_name = @temp_name
 set @count = (@count + 1)

/**********************/
 select @view_name = upper('RPT' + substring(upper(ts.c), 1, LEN(ts.c)))
 FROM (
 select dbo.RegexReplace(@table_name, @regexp_pattern, '_$1') AS c
 ) ts

 if len(@view_name) > @view_name_length
 begin
 -- Check if column name lenght doesn't exceed limits
 print '/************************************************************************************/'
 print '/* ERRRORR! View name "' + @view_name + '" length is too long. Exceeds ' + cast(@view_name_length as varchar(2)) + '*/'
 print '/************************************************************************************/'
 end

DECLARE c1 CURSOR READ_ONLY
FOR
select t.COLUMN_NAME, t.DATA_TYPE, t.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS t WHERE TABLE_NAME= @table_name

OPEN c1
 begin
 DECLARE @column_name nvarchar(100)
 DECLARE @column_alias nvarchar(100)
 DECLARE @data_type nvarchar(100)
 declare @view_ddl nvarchar(1000) = ''
 DECLARE @data_length nvarchar(100)

 FETCH NEXT FROM c1
 INTO @column_name, @data_type, @data_length

print 'IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
 WHERE TABLE_NAME = ''' + @view_name + ''')
 DROP VIEW ' + @view_name + '
 GO'
 print ''
 print 'CREATE VIEW '+ @view_name
 print 'AS'
 print 'SELECT'

WHILE @@FETCH_STATUS = 0
 BEGIN

if dbo.RegexMatch(@column_name, @regexp_pattern) = 0
 begin
 -- Not all columns will be in CamelCase, therefore make them CamelCase
 set @column_name = dbo.initcap(@column_name)
 end
 else
 begin
 set @column_name = @column_name
 end

 select @column_alias = substring(upper(t.c), 2, LEN(t.c))
 FROM (
 select dbo.RegexReplace(@column_name, @regexp_pattern, '_$1') AS c
 ) t

 /* Validate column and correct mistakes */
 if len(@column_alias) > @col_length
 begin
 print '/************************************************************************************/'
 print '/* ERRRORR! Column "' + @column_alias + '" length is too long. Exceeds ' + cast(@col_length as varchar(2)) + '*/'
 print '/************************************************************************************/'
 end
 else if @column_alias in ('DATE', 'NUMBER', 'CHAR', 'TIMESTAMP')
 begin
 print '/************************************************************************************/'
 print '/* ERRRORR! Column "' + @column_alias + '" can not be used - oracle reserved word */'
 print '/* Renaming column by adding postfix "_"*/'
 print '/************************************************************************************/'
 set @column_alias = @column_alias+'_'
 end


 /* Append new column to column string */
 if upper(@data_type) = 'NVARCHAR' and @data_length = '-1'
 begin
 set @view_ddl = @view_ddl + 'CAST(' + @column_name + ' as nvarchar(4000)) ' + @column_alias
 end
 else if upper(@data_type) = 'DATETIME'
 begin
 set @view_ddl = @view_ddl + 'CONVERT(varchar, ' + @column_name + ', 120) ' + @column_alias
 end
 else
 begin
 set @view_ddl = @view_ddl + @column_name + ' ' + @column_alias
 end

 set @view_ddl = @view_ddl + ',' + CHAR(10)

 FETCH NEXT FROM c1
 INTO @column_name, @data_type, @data_length

END

set @view_ddl = SUBSTRING(@view_ddl, 0, LEN(@view_ddl)-1) -- Remove comma from last column

 print @view_ddl
 print 'FROM ' + @table_name
 print 'WHERE DELETED = 0'
 print 'GO'
 print ''
 end -- end of cursor block
CLOSE c1
DEALLOCATE c1
end -- end of table list loop

breaking:

Schedules or Multiple Configurations licensing from OWB 10R2

It appears, from reading Oracle Warehouse Builder 11gR2: Feature Groups, Licensing and Feature Usage Management, that schedules or multiple configurations requires Oracle Warehouse Builder Enterprise ETL license.

If you have OWB Core, then scheduling a OWB mapping has to be done using Enterprise Manger Grid Control, Database Control or any other application that can manage Oracle Database Jobs.

Moreover, in the project can be only one Configuration module. That means for switching from development environment to production, you can not just switch default configuration to other predefined configuration. You need to edit DEFAULT_CONFIGURATION and change credentials manually each time.