IPL - Chennai Super Kings -- Team Analysis with Tableau Public Vizzes

Dt: 8 February 2018

I have been looking at various visualization tools over the years to understand  how quickly one can adapt and create reports using each one of them.The tool that I have had the most fun using has undoubtedly been Tableau .

I was looking up their website and found out that they now have something which is called as Tableau Public.This essentially is a free tool with all capabilities ,The only catch is that the reports crated will be public and shared on their platforms.
But heck its a good solution for everyone of us who would like to learn the tool without having to spend big bucks.

So without spending much time I did set up some data from the biggest public event this year which was the IPL 2018 mega auctions .For my experiment today I chose the most popular IPL team over the years which is CSK.

I classified the players based on following criteria & on the basis of my judgement

Here are some of the reports which I could set up within a couple of hours once the data was set up.

CSK IPL 2018 Big Spends (Sheet 1)

This map shows the players who made big money as paychecks from CSK

CSK Talent Hunt 

This map shows CSKs biggest players in terms of their skills & match winning talent

Gamechangers

Lastly lets look  at the types of players they Invested in.

Looks like CSK invested the most on Allrounders both in terms of money &  talent .We can predict that they are planning to win their next IPL by relying on their allrounders.

Thats it for the day. !!!

Note:
The reports are shared on Tableau Public 
https://public.tableau.com/profile/ashwin.puthran#!/

New approach for Customization of Out of the Box ODI Interaces

Dt: 14 Sep 2017

Recently I have had the opportunity to work on a OBIA implementation & extension project after a long time.

A few things have changed since  had last seen the OBIA environment.One of the notable changes was the Oracle recommendation for customizing the OOB objects,

When OBIA 11 had launched with ODI for the first time ,Oracle had tried to pick up all features of Informatica and tried to replicate it like fir like in ODI .Even the design principles remained similar to what Informatica would use.

One such design principle was for customizing OOTB objects.You would copy the existing OOTB  mapping folder to the Custom folders. Modify the interface & then create a scenario with name like CUSTOM_SDE_XXXXX or CUSOM_SILOS_XXXXX.

This design meant that you had to follow up the changes with replacing the OOTB scenario  name with Custom Scenario name & regenerate the load plan every time you make a modification to the OOTB interfaces.

To avoid the frequency of load plan generations ..Oracle now recommends that you just change the interface in custom folder and generate a scenario with the same OOTB scenario name but a higher scenario number. This helps you to retain the original load plan & run it as is after modifications to OOTB mappings.

Although this could turn out to be a nightmare for production support teams as thy wont be able to figure out at first look whether the OOTB object failed or the custom one .But  it seems Oracle has ruled in the favor of developers this time .

How wold you like your SCD Type 2 Dimension

Dt: 15 March 2017

Most of you must have designed an ETL extract for a slowly changing type 2 dimension .
However how do you  manage the first load as it will populate all the existing active dimensions.

Today while we were discussing the design we came across an interesting dilemma.What should be the date in the EFFECTIVE FROM DATE columns for the first record (version) of a dimension loaded into the warehouse table.

Typically most ETL tools provide a mapping which will set the EFFECTIVE FROM DATE for the first version  of the dimension as the SYSDATE when the dimension table was first loaded.

Although this is not incorrect but when you read the data in the dimension table it seems as if the first known version of the dimension only existed from the date when you first loaded the warehouse table.

A different approach to this is to update the EFFECTIVE FROM DATE column to an early date( 01/01/1900 ) just to denote that this is the first known active record for this table.

This is what  I would prefer.Let me know your thoughts on this .

Maintaining a Datawarehouse -- Customizing Knowledge Modules to use different keys

Dt:5 June 2016

If you have been wondering as to why I have stopped writing about OBIA in the recent past , then here is the answer  to that.I have moved on from OBIA to a different project which involves support & maintenance of an old datawarehouse which is around 10 years old.This DWH was  built using ODI on a teradata target.

In the past I have always worked with building new datawarehouses or  in the worst case maintaining young datawarehouses which were 2-3 yrs old.Usually most of us run away from support projects considering that they are pretty boring and have routine steps & processes. However if you wish to put your mind & soul into  it then maintenance of old datawarehouses can be fun.

This is provided that there is some amount of freedom given to you to make your choices.The DWH that we were maintaining recently got migrated from Teradata DB to MSSQL server .This migration process ended up being a learning process in terms of what is supported in teradata & what features are supported in MSSQL .Also we got a insight into the difference in how Teradata handles certain standard features against how MSSQL does it. For example teradata rejects a subquery SQL which tries to update a same record using two records from the subquery. However MSSQL picks up the highest record & uses that for the update.So similar query in Teradata fails however it does not fail in MSSQL.

Also there are other intances as to how Teradata handles string overflow by automatically truncating characters but MSSQL will throw   you a string overflow error.

This weekend we also came across an interesting case where we wanted to use a IKM to insert records into a target table based on whether a composite column primary index was violated or not  and in the same IKM we wanted to update the records based on only a single key from the composite column index.This kind of requirement is unusual however we still wanted to get this done as a part of migration fix. My team came back to me saying that this is not possible in ODI. I looked at the query and asked them to modify the IKM.

Modification suggested.
Usually all ODI IKMS use the inbuilt APIs to build their queries. One need not understand all the functionality of these APIs but if you look at the KM query & then the translated query in ODI operator logs then you get a fair idea of what the APIs are doing.For our case I asked the team to use the UD10 flag and mark it for all columns on which they wanted to write a NOT EXISTS query in the insert part of the KM.We then used the UD10 flag to retrieve the column list used to reject records based on the composite column primary  index.The updates were still driven by the UK flag.In this way we seperated the inserts logic from the update logic(Columns used) in the same IKM.
Once you learn the art of modifying IKMs then ODI becomes a lot more interesting

Oracle Data Integrator class names to navigate ODI repository schema

Dt: 7 June 2015

Courtesy: my ODI team 

SQL override in ODI for OBIA

Dt: 25 April 2015


Recently  I interviewed for OBIA at one of the reputed firms of the world..
Interestingly this firm has rejected me 4 times now.But I keep going back to prove a point to myself.

The fact that ...everytime they have rejected me, the interview panel was so dumb and asked the most stupid questions... is what irritates me a lot.So I hop in every time they conduct drives to check out what stupid questions they have got this time.


I guess thats enough of complaining  .Lets get back to completing the post.

So the question that this beautiful lady on the panel asked me was something related to ODI.

Can we write custom SQL override queries in ODI like we can do in Informatica source qualifiers ..she asked.
When I said " yes we can"  very confidently she looked almost surprised ,,Her next response being "Are you sure".I told her  "I do not remember the KM property right now but yes we have done this before".

Obviously she must have felt that I have bluffed and must have considered that to be one of the most convincing reasons to reject my candidature for the job.

So here is the proof for all those enlightened OBIA professionals who are new to OBIA with ODI.

You can indeed write query overrides in ODI. However this feature comes along with the OBIA LKMs only.

Note: There is one out of the box KM which ships with ODI which lets you issue SQL against the DB.The name of the LKM is Source as SQL.


Let me know if anyone out there has got more to add to this


Also all the ranting at the start of the post is only in good humor :-) & not meant  to hurt anyone

Renumbering Work Repositories in ODI before migration

Dt:22/3/2015

Renumbering the work repositories is important if you want to migrate the code from one environment to another,

Normally if you install ODI at two locations say DEV & QA it would install with the same default internal IDs that is 500.


For every object within ODI we have an associated internal ID .The last three digits of the ID represent the work repository where the code was developed.

Now consider a scenario where you are creating objects in both DEV & QA.It might be possible that the internal IDs of the objects might clash .

For ex, A new model created in DEV may have the internal ID as 23500 & a new mapping created in QA may have the same internal ID(23500) ,This would create a huge confusion when  you  try to migrate the object from DEV to QA causing the migration to fail.You therefore need to ensure that the work repository IDs in all our environments are unique.



Right lick on your QA repository and choose the option renumber.

DEV is at 500.Lets go to 600 for QA

The operation completed within seconds.Not sure if it really did the job



Let me check.
Custom Folder changed to 4600 from 4500

Another thing that I observed is that the prebuilt code is not stamped with the internal Id .Its just the code that  is custom.(What I now know is that the prebuilt code carries different IDs of repositories where they were initially created)
This makes sense as the documentation says that last 3 digits represent the repository where the code was built.Which means that there should not be problems with migration.



Let me test migrating a folder which I could not yesterday.
It now goes through.
In this method of migration we need to move the models first and then move the mappings so that the references remain intact.(Avoid using Smart mode)


NOTE:All the methods mentioned above have been tested with ODI 11g .I am not sure if they remain true for the older versions.
.

Oracle Data Integrator with OBIA 11.1.1.7.1 --Migration Issues.

Dt:19 March 2014


The honeymoon is over literally.Just when you were close to giving ODI the best ETL tool for OBIA award.They have goofed up.And Big time.

ODI-23040 -->this is the error which I get when I am trying to import my code from one environment to another.
No Rhyme no Reason. Guess it feels good to be a developer at Oracle when they are told never let the end user know what the error is.Let them come to us and beg for support.

But seriously for something as simple as migrating code from one environment to another which Informatica does so efficiently .I had to go through 100s of documents advising me how to use Internal Ids .What these internal Ids mean.How ODI stores this .......& so on....so forth..... .

I mean common which developer would share this information to his customer that this is how we store our objects and you have to change your backend Ids for our migration to work.

I understand that ODI was designed with another view of code migration .But Oracle being the business leader should have known for sure that the three tier architecture of DEV, QA & PROD is how projects are executed in the real world & this is how they should have built the migration module.

For now hope for those guys at Oracle Support to come out with a meaningful answer.It sucks.But yes its business !!!

How to add Custom PLP ,SDE & SIL interfaces to ODI load Plans in OBIA 11.1.1.7.1

Dt: 15 March 2014

Well its easy.

Of the many things you would like to do to get this done.Here are a few.

Make  the flex fields on the custom tables in Models to  point to fact group --> X_CUSTOM_FG. This lets ODI know all your custom fact tables.
Create foreign keys in ODI to the dimensions attributed in your custom facts.
This helps in understanding dependencies.

Just like you would add Informatica sessions to the DAC task lists.
Add the ODI scenarios into the BI Apps Load Plan provided Out of The Box.
You need to add your scenarios to the X_CUSTOM Facts, X_CUSTOM Dims in the LOAD PLAN DEV components.
There are seperate classifications for SDE,SIL & PLP.

Unfortunately once you go to the PLP section here you will not find the custom groups available.
No worries just create your own custom Load plans here within the parallel steps.

Once you have added your scenarios those will reflect in the new load plan done using custom fact group(X_CUSTOM_FG).

For PLP you have to add your newly created load plans into the  LOAD PLAN SYSTEM components.
This is a small piece of wiring which has to  be done.This was left unanswered in the documentation.

For that matter ODI with OBIA is like a new game on the blocks.Steps & rules provided for most of the things but then some hidden secrets left for you to explore.


Seems like a blockbuster so far.

Moving on with Oracle Data Integrator for OBIA

Dt:23 Feb 2014

Its been a roller coaster of a year so far on the technical front.Two months gone by and with a young team conquering the world of OBIA with ODI for data support has been challenging to say the least.

When I started with ODI I had my reservations about how this tool would match up with Informatica which for so long has been taking on the burden of building the  OBAW on its broad shoulders.My first hunch was that ODI would fail and fail miserably at that to make an impact.

As the days have passed by I have seen no major upsets  though and the project has been running smoothly as if we never migrated from one ETL tool to another.But there are differences of course and ones which you have to get used to as time goes by.

For an Informatica developer working on  OBIA projects, moving into the world of ODI .I would say that things are not that bad as they seem .They probably are a lot better.

Some of the comparisons that I can make now that I have worked for some time on ODI are,

For starters you do not have to write your queries.You got to validate them of course.

Odi has flows for everything so your multilevel lookups in Informatica would turn into two yellow interfaces in ODI. As the lookups are done on the database itself the query has to be generated as either a subselect or a join clause.Of course you need to be your judge as to what design you would prefer.

Another interesting thing that comes to mind is that the performance of ODI depends on the performance of your database as well .A slow source database may make you  change your design .As you have the luxury of moving transformations to a quicker target database.

Load plans are generated in the BIACM .Need to look into more details as to how we can customize them.

Overall on the design front ODI seems to rock it .The next challenge seems to be in how it will be able to handle environment migration.

But I can surely say that  ODI with OBIA is not going to be a let down for Oracle

OBIA 11.1.1.7.1 with ODI

Dt: 27 /12/2013

Hi Folks,

Quite some time since I have written to you.Well it has been an interesting phase of my career where I have now got an opportunity to drive an OBIA implementaion with the latest version 11.1.1.7.1 for one of our client here in the United States.

Some of the newer things that we will be working on is Fusion Middleware.Oracle has moved the whole suite of OBIA applications to weblogic server with an aim to have a single point for design execution and manufacturing of OBI analytics.

An interesting thing would also be the use of   Oracle Data Integrator as the ETL tool instead of Informatica.Oracle has managed to do a good job of converting all Informatica mappings to ODI interfaces.The first look seems quite promising.We completed a full load for few analytics and the results look great.

Of course there are  some beginner hiccups with ODI and we have raised quite a few SRS to tackle them but the overall feel looks good.For someone who has worked with Informatica as the ETL tool for OBIA ,for quite some years now ..ODI doesnt make me uncomfortable or uneasy.

Over the years it might well be a great investment for Oracle.

Will keep you posted on the challenges and issues faced.

Till then Good Bye.

OBIA upgrade 7.9.6.2 to 7.9.6.4

Dt: 4 Nov 2013

Sorry for being away from the publishng desk for quitet some time now.Will be back to blogging soon.

Currently working on an assignment to upgrade OBIA for a client from 7.9.6.2 to 7.9.6.4.
In the process we are upgrading OBIEE from 10 g to 11g.
Informatica from 8.6.1 to 9.5.1.
BIP from 10 g to 11 g.

We have been facing quiet a few issues where we have realized that all bases aren't covered and the upgrade wont be fool proof if you blindly follow the documentation provided .

During the upgrade process you need to have a sane head on your shoulders and make sure that you do not make wrong choices and compromise the datawarehouse.

We are making reasonable progress on this and will keep you posted on developments.Meanwhile any help from people doing a similar upgrade will be appreciated.

Real Time OBIEE reporting.

Dt: 10 Mar 2013.

 Have you ever tried real time reporting using OBIEE.When I say realtime reporting I mean using the OLTP rather than OLAP databases for reporting.

We are designing a solution where in we are trying to replicate a OBIEE report from OLAP to a realtime report in OBIEE ,effectively trying out to remove ETL from the equation.

This is very much possible and we were able to replicate most of the functionalities.But even as we were creating the report we came to know about why OLAP is preferred for reporting and what advantages it provides.

For starters the zero wid dimensional record  helps in accomodating those records in the fact which do not have reference to a particular dimension.This is difficult to replicate in realtime reports withou adding a view to the source table adding a zero reference record.

Also we faced considerable challenges in drilling from a realtime report to a transactional report in OLAP.This is all the more difficult as  OBIEE drill down works in a very rigid manner requesting the prompted columns on which the drills are developed to have the same column names in both subject areas.

We are putting in a effort to resolve this .In the meantime if anyone of you out there has attempted it then please share your findings.Real time reports can be a boon to clients ,especially in business where the transactional database is not too busy and can accept reporting queries without affecting transaction speeds.

Actuals and Budgets in the GL_BALANCES table.

Dt :17 Nov 2012

The Oracle EBS table stores the actuals and budgets amounts.Any given record in the GL_BALANCES table is classsifed as either a budget or an Actual by the ACTUAL_FLAG column .

The ACTUAL FLAG column has two values 'A' for actuals and 'B' for budgets.This flag has been exploited in the Oracle warehouse to divide the actuals and budgets into two differenet tables.

So in case you are using the OLAP table W_GL_BALANCE_F  table(assuming that its a replica of GL_BALANCES table ) to get a OBIEE report out and you are expecting to get both the budgets and the actuals information from this table then you may be in for a surprise.
Coz the budgets are not available in the W_GL_BALANCE_F table.This can be verified using the BALANCE_TYPE flag in this table.

You will have to either modify the prebuilt  Informatica map to get budgets information into this table
or the other option that you have is to use the budgets data filtered into the W_ACCT_BUDGET_F table.

Hope this piece of info suits you well for your Finance reports.

Happy reporting :-)

AP_INVOICE_PAYMENTS_ALL & AP_PAYMENT_HIST_DISTS_ALL

Hi,

Recently we were working on a customized report to get  details of all paid invoices into a report.
We decided to use the  prebuilt W_AP_XACT_F,fact  table available in OBIEE to get the reports out.
The fact table supported all the report requirements and all the columns requested by business could be pulled out of the AP Financials subject area.

All was hunky dory till we reached functional testing where we realized that the report was missing some of the invoices which were present in the AP_INVOICE_PAYMENTS_ALL table which held all the paid invoices.

Further digging into the W_AP_XACT_F table we realized that the fact used records from the AP_PAYMENTS_HIST_DISTS_ALL table .
Now ideally since a payment is done its payment history must be recorded in this Oracle EBS table but we realized that some of the invoices which were present int he AP_INVOICE_PAYMENTS_ALL table missed an entry in the history distributions table.
Or to rephrase it some payments in the AP_INVOICE_PAYMENTS_ALL table did not have an entry AP_PASYMENT_HIST_DISTS_ALL table.

We have been trying to link AP_PAYMENTS_SCHEDULE ,AP_INVOICE_PAYMENTS_ALL & AP_PAYMENT_HIST_DISTS_ALL tables and to get the flow of invoices.But with not much luck as of now.

Any guidance on this front will be welcome.

W_MCAL_DAY_D & W_AP_XACT_F

Dt:20 July  2012.

Applicable to OBI Apps 7.9.6.2 and higher

We were going through some financial reports prepared using W_AP_XACT_F and second guessing on why a few invoices were not being  displayed in the OBIEE reports.

Problem with W_AP_XACT_F is that it gets loaded by different arms like Payments,Schedule,Invoice Distribution etc.Each flow having a different level of granularity.

For example the Payments flow just records invoices for which payments are done.So if a particular invoice has not been paid it does not show up in the payment flow.Logically correct

Now consider the new calendar dimension W_MCAL_DAY_D. It has records corresponding to 3 different types of calendars with MCAL_CAL_WID taking values 1000,1001,1002 .

But every dimension table has a default value(zero wid row) which is assigned to those records in the fact for which no matching dimension is present .In case of dates it ideally refers to a scenario where no date could be found.

In W_MCAL_DAY_D though this record has a MCAL_CAL_WID=0 stating that it doesn't belong to any calendar type.

Now, W_AP_XACT_F has records from INV,DIST flow which do not have Payment due dates...For these records the fact has PAYMENT_DUE_DT_WID=0,MCAL_CAL_WID=1000.1000 being calendar type from Ledgers.

So the prebuilt payment due date dimension uses following joins

W_AP_XACT_F.PAYMENT_DUE_DT_WID = W_MCAL_DAY_D.ROW_WID

W_AP_XACT_F.MCAL_CAL_WID= W_MCAL_DAY_D.MCAL_CAL_WID.

In the current scenario whenever  an OBIEE report is developed using this dimension,records from INVDIST,DIST flows are automatically filtered out.

If anyone out there has faced a similar issue then your comments/suggestions will be welcome.

Oracle Tablespaces : Logical or Physical

Dt: 5 Aug ,2012

A tablespace is a storage location where the actual data underlying database objects can be kept. It provides a layer of abstraction between physical and logical data,[1] and serves to allocate storage for all DBMS managed segments. (A database segment is a database object which occupies physical space such as table data and indexes.) Once created, a tablespace can be referred to by name when creating database segments.

Tablespaces specify only the database storage locations, not the logical database structure, or database schema. For instance, different objects in the same schema may have different underlying tablespaces. Similarly, a tablespace may service segments for more than one schema.

By using tablespaces, an administrator can control the disk layout of an installation. A common use of tablespaces is to optimize performance. For example, a heavily used index can be placed on a fast SCSI disk. On the other hand, a database table which contains archived data that is rarely accessed could be stored on a less expensive but slower IDE disk.

While it is common for tablespaces to store their data in a filesystem file, a single file must be part of a single tablespace. Some database management systems allow tablespaces to be configured directly over operating-system device entries, called raw devices, providing better performance by avoiding the OS filesystem overheads.


Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace.

SCENARIO
Has anyone of you come across cases where a single table is allocated muliple tablespaces.This scenario is something new to me because we always tend toincrease the size o fteh tablespace whenever the data begins to grow in size. ??
Waiting for your inputs on this one.

Difference between Teradata and Oracle databases

Dt: 5Aug,2012

You will have experts giving a lot of shit whenit come sto doing a comparison between teradata and oracle.Most of their answers end with either 'Oracle is faster/better' or that 'Teradata can handle large data better' .But you find it diffficult to get a basic differentiation between the two.

So here it is the difference boils down to the way in which they store data.

• Teradata is different from other DMBS (like Oracle) because it uses logical addresses rather than physical addresses to store data. This allows teradata to perform (automatic) defragmentation/re-organiazation of data anytime without effecting the addresses.
•  It stores data on blocks, unlike other PAGE-BASED databases. These blocks are variable length that can grow and shrink based on data.
•  Moreover teradata is DBA-FRIENDLY because:
  - It does automatic disk management
  - It automaticly manage indexes (which are store in internal tables rather than B-tress or other structures)
  - It does automatic defragmentation, garbage collection etc
  - It uses automatic partitioning/distribution of data using hashing algos.

Now if someone has  a different perspective on this then you are free to add your comments.

Informatica Architecture 9.1

Dt: 05 August 2012.

While we have had a look at the OBIEE  architecture its worthwhile to have a glance at the new architecture of Informatica 9.1.
This is available at the Informatica support site.Quite a few bit of new components that have been added.Will analyze and report about them as and when I can have a hands on on the latest version.

MUST read -- Page 1

COPYRIGHT NOTE

This blog is written by a consultant working with the  Oracle Business Intelligence Suite. The primary goal of the blog is to help beginners with Oracle BI Apps .I am planning to add learnings ,experiences and scenarios .


1. Paid – To- Blog Disclosure – I am NOT paid to write this blog. This is a personal blog.

2. Responsibility
The opinions on this are personal &  represent my personal views only. I do not represent or write on behalf of any agency, company or employer. I don’t represent views of my employer(s) or client(s). I’m not affiliated with Oracle, nor do I have any claim to their intellectual property.


3. Libel & Do No Harm
My intention is to “do no harm” and my posts are not counsel they are just my opinions and advice. What I write on my blog is not to be taken as fact nor absolute. I can not guarantee accuracy.  If people use my advice, tips, techniques, and recommendations, and are injured or damaged, I am  not to be held responsible.
 

4.Comments
I am not responsible, nor will be held liable, for anything anyone says on my blog in the blog comments, nor the laws which they may break in my country or theirs through their comments’ content, implication, and intent. I have to approve comments first and have the right to refuse them.

5. Copyright
I don’t claim copyright to the works that I quote using fair use principle. If you find copyrighted material that you own on this site – please let me know and I’ll remove it or credit you for it whatever that works for you.


**********************************************************************************
Note: Would like to thank (http://obiee-blog.info/about/)  for this post and credit this post to the mentioned blog.I just picked the copyright clauses and I hope you wont mind this.
**********************************************************************************