Power of/Capabilities of Oracle BI Administration - Tool Query Repository Tool


It was a bit relaxing day so thought of blog something but not sure what to blog L. I guess many people might be knowing about this but thought of re-sharing as it may help at least some one.
Okay lets goto topic. If you have some question like following , all those are can be answered using Querying the repository.

a)    How to find all hidden presentation attributes (may be based on some condition)
An example query filter (there could be other options):

•        In the Administration Tool, navigate to:  Tools > Query Repository (Ctrl+Q)
•        Name: * (to query all)
•        Type: Presentation Column
•        Click Filter
o        Click 'Item' row to bring up drop down arrow
o        Choose 'Visibility Filter'
o        In the next column, click in the row to choose the '< >' (not null) operator
o        Leave the Value column blank
o        Click 'OK'
•        Click 'Query'
Like this we can answer many of following questions.
 b)   How to do I know impact of an foreign key. Where all it is joined , if you have a single alias in physical it is easy to find where all it is joined but if you have many aliases and you don't know what are those aliases (Example Date Dimension could have more than 100 aliases – you want to know what are those and what is the count of joins and join conditions etc).
In other words find all joins with in RPD , with what are the facts joined with dimension on which condition.



Launch query tool , choose type as "Physical Foreign Key"  , optionally you can set filters (if you are looking for all joins then don't choose any filters) , now make sure using "Columns" button choose all required columns ie Table1, Table2, Join Expression as bare minimum as bare minimum then run the "Query". Using "Save" button you can save them to spreadsheet.

c)    How do I know how many presentation attributes are there (may be by subject area or so, or how many attributes with prefix as some 'abc' or
d)    How many MUDE projects are there what are those names
e)    How many ragged , skipped hierarchies are in the RPD and what are those.
f)     How many variables are there what are those default values , what is the initialization block it is associated to , what is the sql to populate the variable,
g)    How many users/roles are defined in the RPD and who are those users
h)    How to find how many nested presentation folders are there in all subject areas and what are those. (if you have old technic -> then you may have to query differently).
i)     How many time dimensions are there or how many parent child hierarchies are available.
j)     How many lookup/fact/Bridge tables are there and what are those.
k)    How many presentation attributes have different name than that of logical columns.
l)     How to find how many presentation attributes are re-named with alias or in other words report created long back, but presentation attributes are modified , we can find find old alias and corresponding new presentation attribute.
In order to achieve this name should be * , type from drop down "Presentation Table" if looking for presentation table , "Presentation Column" for column, Click in on 'Filter" and choose Alias operator as not equal and input values as NULL.


m)  How to find more than 2 levels Hierarchical attributes
n)    How to find all metrics which has aggregate as SUM /MIN/AVG etc

   

o)   How to find all derived attributes and what are those ?
p)   Find out all the logical sources which has filter in it.


q)    How to find all Logical source which are disabled for query also there are many other filters as shown below
   



Bottom line we can do many things with Query Repository tool, it is very useful for OBIEE administrators to quickly find few useful things and save the results to present.

I hope you like the post.

Goldengate: How to handle soft deletes

How are you all , it has been a  long while since my last blog so thought of sharing some useful information on goldengate, let us try to implement soft deletes in the goldengate.

Concept: 
      In the source table record got deleted , in the replicated target update the record attribute delete_flag from N to Y, if record got reinserted in the source, in the target modify delete_flag from ‘Y’ to ‘N’. If new record got inserted then delete_flag will be N and if source updates a record in the target also record should get updated but there is no change in the delete_flag value i.e. it will be N only.

Implementation details…..for example let us have source target tables as follow

CREATE TABLE source.SOFT_DETELE_IMPLEMENTATION
(
   ROW_ID      VARCHAR2(15 CHAR) ,
   LOGIN       VARCHAR2(50 CHAR)
);
  
CREATE TABLE target.SOFT_DETELE_IMPLEMENTATION
(
   ROW_ID                 VARCHAR2(15 CHAR) ,
   LOGIN                  VARCHAR2(50 CHAR) ,
   DELETE_FLG             CHAR(1 CHAR)     
);


In extract parameter file add the table which you are interested in
example: edit /app/ggate/dirprm/eextract.prm

EXTRACT EEXTRACT

SETENV (NLS_LANG = "AMERICAN_AMERICA.UTF8")
USERID dbuser_name@DB_Instance, PASSWORD password

EXTTRAIL /app/gg/trail/
-- etc

TABLE source.SOFT_DETELE_IMPLEMENTATION, &
      COLS (ROW_ID, LOGIN);


--Modify the Replicat parameter file and add following syntax for corresponding table then perform all necessary task need for extract / replication to work.

edit /app/gg/dirprm/rextract.prm

---------------------------------
-- TARGET Table mapping -- 
---------------------------------
-- following tag will allow multiple maps for single source
ALLOWDUPTARGETMAP
GETINSERTS
GETUPDATES
-- IGNOREDELETES will ignores deleted records
IGNOREDELETES
MAP source.SOFT_DETELE_IMPLEMENTATION,  TARGET target.SOFT_DETELE_IMPLEMENTATION, &
    COLMAP ( ROW_ID = ROW_ID, LOGIN =  LOGIN, DELETE_FLG = "N" ),HANDLECOLLISIONS;
IGNOREINSERTS
IGNOREUPDATES
GETDELETES
-- UPDATEDELETES will convert delete operations into update operations.
UPDATEDELETES
MAP source.SOFT_DETELE_IMPLEMENTATION,  TARGET target.SOFT_DETELE_IMPLEMENTATION, &
    COLMAP ( ROW_ID = ROW_ID, LOGIN =  LOGIN, DELETE_FLG = "Y" ),HANDLECOLLISIONS;

Unit testing:
Inserted a record into source  ( insert into SOFT_DETELE_IMPLEMENTATION values ('400','aaa');)
Target Table Values ---- 400    aaa         N     

Updated a record in the source (update SOFT_DETELE_IMPLEMENTATION set login ='upd'  where row_id ='400';)
Target Table Values ---- 400     upd        N 

deleted a record from source (delete SOFT_DETELE_IMPLEMENTATION where row_id ='400';)
Target Table Values ---- 400     upd        Y   

Inserted same record 2nd time in the source
Target Table Values ----400     aaa         N    

Source record got updated
Target Table Values ----400     upd        N  

Source record got deleted second time

Target Table Values ----400     upd        Y