Incorporate t-SQL Merge Operation in SSIS

SQL Server introduced in SQL Server 2008. It is very useful statement specially for data warehousing environment. So, it is very likely that you will need it in SSIS project. The can be two solution to incorporate in SSIS.

First One:

Using the merge join operator, do the followings:

1. Merge Join - Source Left join Destination
2. Conditional Split -
• Destination ID is null - "OLEDB Destination"
• Destination ID is not null - "OLEDB Command". Use update operation within "OLEDB Command"

Second One:

Using the "Slowly Changing Dimension" operator, do the followings -

1. Add the "Slowly Changing Dimension" and configure it using wizard
2. Using OLEDB Connection, choose the destination table and set its ID as "Business key"
3. After clicking next, select all other destination columns and select the Change Type as "Changing attribute"
4. Keep all others default.

If you compare these two figures, you will find that how easy it is. The SCD really does a lot for you. It encapsulates sort, merge join and conditional split and auto generate the OLE DB command. So, no programmer forgets to use it instead of using the first option or calling script task from SSIS and make the code less programmer friendly.

If the table size is large, then the first option should be used. On the other hand, if the table size is small, performance of first and second option is almost same. For detail, please read this article -
http://chrisjarrintaylor.co.uk/

Oracle Bulk Collect & PL/SQL Collection: Ultimate Way to Speedup Fetching Cursors

PL/SQL should be used only when the work cannot be performed using SQL as SQL engine is faster than PL/SQL engine. Cursors reside at the heart of PL/SQL and it is one of the most eminent reason of slow performance of PL/SQL. But most of the time you can speedup the cursor fetching for large dataset using Bulk Collect.

This option enables you to retrieve multiple rows of data in a single request. The retrieved data is then populated into a collection variables. This query runs significantly faster than if it were done without the bulk collect. The operations on collection variables are in memory operations and thus much much faster. There are three types of PL/SQL collection variables - Nested Tables, VArrays and Associative Arrays.

Here I will show you an example of bulk collect using nested table to speed up fetching. In the sample code, I will use sys_refcursor instead of declaring any explicit cursor. It is a week type cursor available from Oracle 9i. There is also another predefined cursor from Oracle 9i which is refcursor_pkg.strong ref_cursor. Here is the PL/SQL code:

declare

TYPE type_base_rec IS RECORD (cx NUMBER, event_date DATE,
balance NUMBER);
--instead of creating any type,
--I use record and it will be available only within PL/SQL block
TYPE type_base_tbl IS TABLE OF type_base_rec;
ref_cur_base SYS_REFCURSOR;

begin
OPEN ref_cur_base FOR 'select c1,c2,c3 from Tab';
FETCH ref_cur_base BULK COLLECT INTO base_data;
CLOSE ref_cur_base;
end;
/

You can use limit option to limit the number of rows to be fetched. Like -

FETCH ref_cur_base BULK COLLECT INTO base_data limit 5000;

Now, you can use the collection methods on the collection variables. Most of the time, you need count, First, last, trim, extend, delete or indexing like base_data(i). A common example where bulk collect can speed up your operation is writing files from database.

OWB Dimensional Modeling to Oracle RDBMS Object Translation

OWB Dimensional Modeling to object translation - This is a crucial thing in data warehousing. The reasons are -

• You should know the objects which will be modified during ETL operation. It will be helpful for sql tuning and monitoring. You can understand execution plan well and do the necessary things.
• It is also necessary from DBA perspective. Not only from storage perspective, is it also necessary to utilize parallelism.

In this discussion, we mainly focus on ROLAP. This is because for MOLAP most of dimensions and facts are managed by Oracle itself within the Analytical Workspace (AW). At first we start with dimension. A dimension will be translated in the following objects -

• Table - Table will be available in Physical Binding. One dimension may create more than one table depending on implementation. If you choose star implementation, just one table will be created and if you choose snowflake implementation then for each of the level, one table will be created. Note that, in dimensional modeling you have to create at least one level for each dimension.
• Level & Hierarchy - As mentioned in previous paragraph, every dimension has at least one level. For each dimension, one dimension object must be created. This object has the levels and hierarchy. Hierarchy is optional. If your dimension has hierarchy then it will be reflected in hierarchy of dimension object.
• Surrogate Key - It is available in attributes tab. For each surrogate key, one sequence will be created.
  

It is well known that in data warehouse there are three types of slowly changing dimension (SCD). If you are using Type 2 or Type 3 SCD, then you have to define mapping for the dimension. In this case, there will be different input table which maps data to dimension output table. The mapping is always converted to pl/sql procedure.

Next comes the fact table. As we discuss ROLAP model, the fact translation is simple.

• One table is created for each fact.
• For each dimension, one bitmap index will be created.
• For each dimension, one foreign key will be created also.

Multi-dimensional OLAP:
Within the context of the relational database, the AW is a Large Object (LOB) that is a multidimensional data type. It is stored in a column in a relational table and this table will be a partitioned table. The name of the relational table will be AW$workspacename. All dimensions and facts are maintained internally within this LOB column.

Data Warehous Metadata Management in OWB

Metadata is all the information in the data warehouse environment that is not the actual data itself. We have operational source system metadata including source schemas and copybooks that facilitate the extraction process. Once data is in the staging area, we encounter staging metadata to guide the transformation and loading processes, including staging file and target table layouts, transformation and cleansing rules, conformed dimension and fact definitions, aggregation definitions, and ETL transmission schedules and run-log results. Even the custom programming code we write in the data staging area is metadata. So, metadata management is a crucial thing in any Data Warehouse.

Oracle Warehouse Builder gives the following facility for metadata management -

1. Source Metadata:
   This includes importing of source metadata, data profiling and attribute analysis. OWB has all these capabilities fully. OWB has Database module, File module and Data Profiling module. File module can sample data. Data profiling module is very much helpful for attribute analysis and to get direction of your data modeling.
   
   
2. Staging Metadata and Transformation:
   Staging data is designed through Database module. Mapping of staging data can be designed using can built in components (e.g. joiner, table operator, etc.) of mapping. Staging data can be designed through custom programming code also. For this instead of mapping, you have to use transformation. In custom programming, you have to maintain proper comments to maintain metadata. So, in this case, you yourself have to maintain the metadata.
   
   
3. Process flow & Schedule Metadata:
   Process flow is maintained by Process Flow module. By this module you can design the sequence of process flow in GUI which is very much comprehensible. You can document success flow, error flow and dependency of processes in this module. Schedule metadata is managed by Schedule/Calendar module. Here you define a schedule and assign this to one or many process flow.
   
   
4. Dimensional Modeling Metadata:
   This metadata is also maintained by Database module. If you design dimension and cube (ROLAP/MOLAP) by OWB, then all your dimensional modeling metadata will be maintained within OWB. Here the dimensional modeling will not be so much visible as you find in ER studio or similar tool but the modeling for a fact will be well documented in tabular form. That is for a cube you can see the dimensions and their levels in a tab, measures and aggregation in other tabs.
   
   
5. Data Linage and dependency Metadata:
   This is another nice feature of OWB. Here you can see the linage and impacts of objects (like tables, file, dimension or cubes) under database module. Linage of an objects shows you from which objects this object has been derived and impact shows you which objects are dependent on this object. This is very helpful for change management. You can plan the impact of change of any object by managing this metadata.
   

These are the overall metadata management facilities in OWB.

Basic Administration Steps of Oracle 11g Warehouse Builder

In Oracle Warehouse Builder (OWB) ETL consists of two main areas:

1. Mapping (or Data Flow Diagrams)
2. Processes (or process flow diagrams which contain multiple mappings)

So, Administration also consists of two parts.

Detail information will be found at http://docs.oracle.com/. Here is the summary -

Basics (This is for Mapping)

After installing OWB, you will get two users - OWBSYS, OWBSYS_AUDIT. You have to unlock these users.

1. Create Repository (which is called workspace in Oracle 11g) and make a user as an owner using Ropository Assistant
2. Administrate the OWB using the repository browser. For this type of administration, use the user who is also owner of the repository. To use repository browser, you have to start the listener -
   
   • start the OWB browser listener - owb_home\owb\bin\win32\startOwbbInst.bat
   • stop the OWB browser listener - owb_home\owb\bin\win32\stopOwbbInst.bat
   • start the Web Repository Browser - owb_home\owb\bin\win32\openDB.bat
3. Register other users to this repository
4. Start/Stop Control Center Service when necessary
   

Extended (This is for process flow)

1. Enable Integration with Oracle Workflow i.e. Installation of Oracle Work Flow. In Oracle 11g, the installation s/w is provided. But for previous version you will need Oracle Companion CD. Location of Installer for Oracle 11g - ORACLE_HOME/owb/wf/install> wfinstall.bat
2. After installation a user will be created - OWF_MGR. Register the Work flow user, OWF_MGR in workspace - OWB_USER & execute any procedure
3. Control Center Agent (CCA) manages deployment and execution of the mappings that are based on Code Templates. CCA is not part of the Control Center service hosted within the Oracle Database. It is very much useful for Control Center Agent topology. In non-Oracle database host, this is the only part of OWB that can be used.

You can see also http://gerardnico.com/wiki/owb/owb.

If you want to create and maintain Analytical Workspace (AW) in your database, then you must have two more roles. They are - OLAP_USER and OLAP_DBA. OLAP_USER role allows the user to create AW in its own schema. But for other, you have to utilized OLAP_DBA role.

Analytic SQL Function: A Solution for Well Managed ETL in ROLAP

Analytic SQL is not at all a new feature of Oracle. It is available from Oracle 8i. Still it is very much useful in data analysis specially if your data mart is designed on Relational OLAP. But if you are proficient with analytic tool like Analytic Workspace Manager, then you may not need to use analytic SQL so much.

I found analytic SQL very much useful in several data mart ETL. Here I will show an example how it will simplify you SQL. Suppose, you want to calculate the best performance location of every customer from the sales report. Its very simple if you use analytic SQL. Here is it -

select distinct cx, first_value(location) over (partition by cx order by rev desc,location) location
from (select cx,location,sum(cost) rev
from sales group by cx,location)

At first step, you get the aggregate data. Then run your analytic SQL on this aggregate data. This is because, analytic function works well on aggregate data. In the above example, the steps are same. At the beginning get the customer and location wise revenue.

Then partition your data based on customer and in each partition sort the data primarily based on revenue, then based on location in descending manner. So, the first row of each partition must be the highest revenue considering location wise segregation. If two location contains location wise maximum revenue, then get the location whose name comes first in lexical sorting.

Here is a link for the beginner of analytic sql function -

http://www.oracle-base.com/.

I have found AVG, FIRST_VALUE, LAST_VALUE, RANK, ROW_NUMBER, CORR, NTH_VALUE to be most useful. Specially, ROW_NUMBER removes the limitation of rownum and it can be used for data paging.

Replacing of complex LIKE with REGEXP_LIKE from Oracle 10g

Oracle Database 10g offers four regular expression functions. You can use these equally in your SQL and PL/SQL statements. Among these REGEXP_LIKE is the most useful. Like LIKE operator it returns boolean values. Thus this can be easily used in WHERE and HAVING clause. The header of this function is -

REGEXP_LIKE(source, regexp, modes)

To know more about this function visit http://www.oracle-developer.net/.

Also here is a recommended site for regular expression http://www.regular-expressions.info

The third parameter is optional and it is rarely used.

Here I will show you how it makes the query simple and faster -

'^(880|88018)?7777[0-9]{0,2}$' - this regular expression matches every starts with 880 or 88018 or none of these, then 7777 then ends or ends after one or two digits. So it matches the following strings -

8807777, 880187777, 7777, 77776, 777758.

So, if you want to query a database table with this filter on a column, you can write the following query -

select * from table where regexp_like(colA, '^(880|88018)?7777[0-9]{0,2}$')

or

select * from table where colA = '7777' or colA='8807777' or colA='880187777'
or colA like '7777_' or colA like '8807777_' or colA like '880187777_'
or colA like '7777__' or colA like '8807777__' or colA like '880187777__'

Thus regexp_like() function makes the query simple, comprehensive and easy to manage. In some case you may find it is absolutely hard to write query using like where that can be easily written using regexp_like().

High Level Steps of Creating Dash board in BO XI R2

Here are the steps -

1. Build a metic universe & Export it to performance management

• This universe need not have any calendar.
• All the measures must have a self join i.e. under where clause the event date should be mentioned between start date and end date.
  Example - DATE_ID between @Prompt('Begin_Date','D',,mono,free)
  and @Prompt('End_Date','D',,mono,free)
• select clause should not contain any aggregate function like sum, avg, etc.
• Import the universe in performance management -
  Performance management>Setup>System Setup>Universes>Add Universe
• Select the time dimension as you required from time configuration -
  Use Performance management>Setup>Time Config
  

2. Create/Edit Metrics

• Create the Metrics -
  Performance management>Dashboard Manager>Metrics>
  
• Select proper calculation interval
• Check "Stop at current period" to stop calculation till today. This helps you to see the dashboard till today.
• After creation of Metrics, don't forget to refresh it. Otherwise, you will not be able to see the data.
  
• Use purge and refresh after editing the calculation period.
  

3. Create Metric Analytics

• Create the analytics -
  Performance management>Dashboard Manager>Metric Analytics>
  
• Use "Save as" to save it in proper folder
• Use proper render style and graph properties
  

4. Create A dashboard

• Add a dashboard
• Save the dashboard in proper directory
  

5. Place the Metric Reports on dashboard

• Open the dashboard in the directory.
• Select customize then pick the analytics that you want to see in your dashboard
  

Solution of File-based Data Collection in Exadata

When I use Oracle Exadata Database in 2010, it was the first instance of this awesome product in South Asia. The biggest problem that we face during Data Warehouse implementation on this machine was that Oracle forbid us to collect file-based data on Exadata. This is because -

• There is no space available in local disk of the RAC instances as this may create problem such as no space available or instance high availability.
  
• External storage of Exadata are used as Automatic Storage Management (ASM). I.e., from Oracle Enterprise Linux (OEL) these are raw disks and the oracle database engine manage these storage. So, these storage cannot be accessed in conventional way.
  

The solution of this problem can be solved in two ways -

1. Read External table using DB Link
2. Store data in a database table using sql loader then read the table using DB Link. The problem in this solution and its previous solution is that you cannot use the parallel processing of Exadata
   
3. Use Database File System (DBFS). This is the ultimate solution as in this case external tables can be accessed in parallel manner. So, the extreme performance of exadata can be utilized.

Actually configuring DBFS is fully part of System & DB Admin job. They configure the DBFS server in Exadata and client in the staging area OS. We, the ETL team, mainly use the client of DBFS. Here are the steps -

1. At first, run the mount script in client which connects to DBFS service (not database service) in Exadata. This script actually run the DBFS client with the following parameter -
   
   dbfs_client dbfs_user/dbfs_pass@SRV_DBFS -o allow_root -o direct_io /ORABASE/DBFS_DIR
   
   
2. After execution of above script mount point in DBFS server will be available under mount point of DBFS client. Note that the original name of this mount point can be different in DBFS server. As an example in our case,
   
   In DBFS server the mount point is /u01/app/DBFS_DIR/
   In DBFS client the mount point is /ORABASE/DBFS_DIR/
   
   So, now if you create a directory in DBFS server under the server mount point, the directory will be available in same name under DBFS client mount point. The system & db admin will ensure that both server and client should be in same user group so that no privilege related problem will appear during accessing the directories within DBFS mount point.
   
   
3. One advice - please collect the files in the client's own mount point then copy those files to DBFS client mount point. Its performance is optimum.
   
   
4. Now rest of the things are within Exadata server. Create Oracle Directory using OS directory under DBFS server mount point which is visible to OEL if DBFS server is up and running.
   
   
5. Create an external table in Exadata database server using the above database directory.
   

Thus, you can create and use External table within Exadata and can be benefited by its power.

Calculation Context : Ulmitate Solution for Complex Query in Business Object

Calculation contexts give you more control over how a formula or measure is evaluated. To understand calculation contexts, you need to be familiar with basic report concepts.

A report contains two kinds of objects:

• dimensions - which are types of data about your business that can have measures associated with them (for example: products, years, states)

• measures - that you can calculate in relation to dimensions (for example: sales revenue, number of sales).

For example, a report could show sales revenue (a measure) by year (a dimension). In this case the sales revenue results are calculated by year.

The important thing to remember about measures is that they are semantically dynamic. This means that the results returned by a measure depend on the dimension(s) with which the measure is associated. In other words, the results returned by a measure depend on the context in which the measure is placed.

WebIntelligence calculates measures according to default contexts depending on where the measures appear in a report. However, you can change these default contexts. This is what is meant by defining the calculation context. You define the calculation context by including context_operators and context_keywords (Report, Section, Break, Block and Body) in your formulas.

A calculation has an input context and an output context. You can specify neither, either or both explicitly. The input context is the set of dimensions used to make the calculation. The output context is a set of dimensions that functions like a break in the calculation - it causes the calculation to output values as if they are calculated in the footer of a table break (or an SQL COMPUTE BY clause).

Example

The final column in the table below is calculated as follows:

calculation - minimum

input context - year, quarter

output context - year

This combination of input and output contexts tells Web Intelligence to sum the revenue for each year/quarter combination (the dimensions used to make the calculation), then output the minimum such value in each year ( the value that appears in the break footer if the table contains a break on Year and a minimum calculation in the break footer).

Year	Quarter	Product	Revenue	Minimum
2005	Q1	Product 1	10000	41000
2005	Q1	Product 2	15000	41000
2005	Q1	Product 3	16000	41000
2005	Q2	Product 1	17000	41000
2005	Q2	Product 2	17000	41000
2005	Q2	Product 3	16000	41000
2006	Q1	Product 1	14000	33000
2006	Q1	Product 2	15000	33000
2006	Q1	Product 3	16000	33000
2006	Q2	Product 1	12000	33000
2006	Q2	Product 2	11000	33000
2006	Q2	Product 3	10000	33000

This is how the calculation looks when made explicit in a table:

Year	Quarter	Revenue
2005	Q1	41000
2005	Q2	50000
	Min:	41000
2006	Q1	45000
2006	Q2	33000
	Min:	33000

In Web Intelligence formula syntax, this can be expressed as either

Min ([Revenue] In ([Year];[Quarter])) In ([Year])

or

Min ([Revenue] ForAll ([Product])) In ([Year])

The input context appears inside the function parentheses and the output context appears after the function parentheses. The set of dimensions in each context is itself enclosed in parentheses and the dimensions separated by semi-colons.

You use the context operators In, ForEach and ForAll to set up input and output contexts. You can also use context keywords to specify the data referenced in the contexts.

For more information, please download the manual from help.sap.com and read chapter 3 - Understanding calculation contexts.