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.

Upgrade Oracle Business Intelligence Standard Edition One 10g

Metalink document ID 743916.1

 

Upgrading Oracle Business Intelligence Standard Edition One to higher versions of the underlying components (Doc ID 743916.1)

“Customers may upgrade to the latest 10.1.3.x versions of the BI components by running the BI Enterprise Edition 10.1.3.x Installer. They will be alerted that there is an installation already present and prompted to preserve the existing configuration. They should choose that option and the installer will leave the existing configuration (to include Repository, Web Catalog, etc) in tact and install the newer versions of the components. Note that when upgrading, all BI components must be upgraded to the same version. Customers may upgrade to the latest versions of Oracle Database 10g or Oracle Warehouse Builder 10g by applying the supported appropriate patches as needed. A note on platform support — Oracle BI SE One is available for Windows platforms only. Oracle does not support Oracle BI SE One on other platforms. A note on licensing — By license, customers are restricted to using BI Server, BI Answers, BI Dashboards, BI Publisher, BI Administration Tool, Oracle RDBMS, Oracle Warehouse Builder Core ETL functionality. Note that the BI SE One installation installs all of the BI components — those listed above as well as Delivers, Office Plug-in, etc. If customers wish to use those additional components they are required to upgrade to a BI Enterprise Edition license.”

so yes, you can upgrade BI to 10.1.3.4.1 but no, you can only upgrade the database to 10.2.0.5 not 11g (if you want support).

The direct link is this:

[https://metalink3.oracle.com/od/faces/secure/km/DocumentDisplay.jspx?id=743916.1|https://metalink3.oracle.com/od/faces/secure/km/DocumentDisplay.jspx?id=743916.1]

If you don’t have a metalink login, then basically you can’t have the document. Also, I won’t send this out over email since that’d be illegal:

“You may not use, disclose, reproduce, transmit, or otherwise copy in any form or by any means the information contained in the Materials
for any purpose, other than to support your authorized use of the Oracle programs for which you hold a supported license from Oracle, without the prior written permission of Oracle. “

Something I wanted to post a long time ago, but continuously forgot. This is to finish the recurring question “can we upgrade to 10.1.3.4 or something”:

https://metalink3.oracle.com/od/faces/secure/km/DocumentDisplay.jspx?id=743916.1

Cheers,
C.

 

http://www.rittmanmead.com/2010/11/obi-standard-edition-one-updated-for-11g/

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:

Komentarai MS SQL duomenų bazėje

Duomenų sandėlio kūrimą dažnai prilyginu “reverse engineering” procesui, kai reikia suprasti, kas ir kodėl buvo padaryta.

Komentarai DB lygyje yra labai didelė pagalba. Dėl to man patinka dirbti su Oracle. Komentarus paprasta įterpti, perskaityti. Yra daug nemokamų įrankių, kurie sugeneruoja dokumentaciją pagal komentarus. MySQL irgi panašiai, kiek pamenu.

Microsoft SQL atveju..na tai tiesiog užknisa. Net 2008 versijoje nėra būdo paprastai įterpti komentarą. Pripažįstu, kad su MS mano patirtis nedidelė, bet komentuoti juk neturi būti sudėtinga, ar ne? MS’e reikia kurti kažkokius papildomus atributus, kažkur įterpinėti ir t.t. Oracle be to patinka dar tai. Atsidarai lentelę IDE pagalba redagavimo režime ir šalia stulpelių matai komentarus. O MS’e? Tuščia. Net nėra tokios galimybės. Siutina!

Po to, įtikink jei gudrus kolegą, kuris projektuoja DB ant MS, kad komentarus rašyti yra sveika.

Štai čia pasimato įmonių filosofijos, kultūros skirtumai. Vienos yra “data oriented”, kitos “nežinau kur oriented” 🙂

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.

Kaip importuoti egzistuojančias lenteles į Oracle Warehouse Builder

Į Oracle Warehouse Builder 10g importuoti lenteles tenka, kai lentelės sukuriamos per SQL skriptus, o ne per OWB įrankį. OWB11g versijoje labai panašiai vyksta lentelės importas. Daroma prielaida, kad lentelė jau yra duomenų sandėlio schemoje.

Atsidarome Design Center. Ant duomenų sandėlio duomenų bazės modulio spaudžiame dešinį ir Import…

image

Atsidariusiame lange spausti Next>

image

Palikti pažymėta tik Tables ir “Use a synonym to lookup objects”. Bus atrinktos tik lentelės. Spausti Next>

 image

Išskleisti šaką “Tables” ir sąraše surasti norimą lentelę. Šiuo atveju TIME_DIMENSION

image

Permesti pažymėtą lentelę į dešinę pusę ir spausti Finish

image

Lentelė įkeliama

image

Įkėlimo būsenos langas. Spaudžiamas OK

image

Lentelė atsiranda duomenų sandėlio modulio lentelių sąraše.

image

Dabar ją reikia sukonfigūruoti, kad nebūtų iš naujo diegiama į schemą. Apsisaugome nuo sukurtos lentelės ištrynimo ar perrašymo. Ant lentelės spaudžiame dešinį ir Configure…

image

Nuimame varnelę ties Deployable. Ši lentelė bus matoma ir galima naudoti OWB projekte, bet ji nebus diegiama iš OWB įrankio, nes ji buvo sukurta per SQL skriptus.

image

Enterprise Manager and Oracle Workflow Manager to Oracle Business Intelligence Standard Edition One 10g

It is possible to manually install Enterprise Manager Database Control and Oracle Workflow Manager to Oracle Business Intelligence Standard Edition One 10g.

  • To install Oracle Workflow Manager you need to start Oracle Universal Installer. Modify current installation.
  • To install Enterprise Manager Database Control you need to use console application emctl.