SNP_SESSION - The Data Analyst’s Dream Table of Oracle Data Integrator

The Oracle Business Intelligence Applications stack provides an array of tools during the implementation, and each of them comes with its rich set of features. The awesomeness comes when we get to experience several business use-cases and scenarios, analyze the metrics and data, interpret them along the lines of the business process, and ultimately when also we encounter product limitations - and discover amazing ideas to make our lives easier. Happiness...as deep the word sounds, becomes almost synonymous in such cases, as we make breakthroughs through innovative complex ideas.

All of us are aware of this repository table SNP_SESSION in ODI, the unattractive component that shows lots of rows and numbers and dates, often just helps only to find a specific information and then we are done with it. In an environment where overnight several incremental loads consume around 6 hours daily, it generates lots of logs and data and writes to all of the repository tables, including session level details in SNP_SESSION. All information of every session like rows processed, rows inserted, rows updated, period and filter variables used, duration taken, start time, end time - are logged in SNP_SESSION.

To understand the prowess of SNP_SESSION, we need to get to a few questions first, and then the train of thoughts and discoveries can follow. For a session am interested in, what is the behavior of this session over the last 4 months? Does it have any pattern during specific periods? Does it have any relation with other sessions’ attributes? Does a data volume of another session or duration of a different session influence this? Since rows processed do not always proportionately impact session durations, does a % variance of a different session impact the session am interested in to an extent? Say in my today’s load, can I find which scenarios from the past repeated today, say with similar data volume or % variance in duration? Can I foresee untold information or what is going to happen as the loads progress by real-time analysis of the data?

It’s been very exciting to know over the last few weeks that all of the above questions can be answered, tremendously by using SNP_SESSION, and with some help from SNP_LP_INST and SNP_LPI_STEP. We have implemented a solution which is now in its final testing phase with live data, and will heavily complement manual human monitoring activities - by providing root causes before the impact happens, and providing additional insights into the application which otherwise often gets overlooked due to the vastness of the system.

We have calculated the weighted average of each session duration, load plan wise, over a period of last several months, with an algorithm that took almost a week to develop after a lot of brainstorming. Then came the perilous task of calculating the standard deviation of the weight samples so as to help calculate the accuracy of our analysis, but it finally happened! Next came analyzing the data volume - with NB_ROW, NB_INS, NB_UPD already available in SNP_SESSION and waiting for us. Comparing today’s volume with the weighted average for the corresponding session itself started giving insights, but we wanted more. We asked what next, what if, why now, what then, and each metric opened up new paths before us to explore.

Each field of SNP_SESSION gave rise to almost 3-4 metrics of it’s own, giving rise to real-time daily calculation and analysis of the datasets during executions, and the impact it causes to the parent load plan. During the execution of each load, we are able to get insightful emails consisting of detailed analysis of the load - and a similar day in history if today’s scenario matched, and what happened then, and how the day went with the consequent activities.

But again, we need a single indicating factor, giving rising to the calculation of probability - as a single point of figure to indicate the possibility of actual realization of our real-time predictions, for each prediction. Hence more brainstormings followed, and finally now every email gets tagged by a probability that makes it so much more meaningful. Thus it becomes so true in today’s world, data is only useful when we know how to process it to our benefit - and it will truly continue to become more and more the case in future!

Read More »

Sensational Sequoia

The exquisite Sierra Nevada, in the magnificent slopes of California, is the home to some of the oldest living things on earth – the dazzling Giant Sequoia trees, some as old as 3500 years. The stunning facts of these exquisite trees were as overwhelming to me as a 10-year old, as it is today – and the long dream of experiencing these breathtaking wildernesses came true few days back.

Driving via Three Rivers Visalia through the wild fascinating roads uphill to the Giant Forest is an experience in itself that takes some time to sink in. The gradual change in the landscape around us as we approach is magnificent, the appearance of the trees, the heights, the feeling of suddenly shrinking is so electrifying – one just gets dazed.

The role of forest fires on the lifecycle of the Sequoia trees is interesting. Due to reduction in the number of natural forest fires today, the Park Service executes controlled fires to remove competing vegetation to allow Sequoia seedlings to germinate - which has difficulty otherwise. The Giant Forest Museum is a storehouse of some gripping figures and facts – the Sentinel tree greets all the visitors as we enter the museum, a statuesque of over 250 feet high.

As we went down from the parking lot via the Sherman Tree Trail, we could feel the energy and excitement around us – the weather felt perfect for visitors, not a surprise given that the Sequoias thrive best in humid climates. When we reached, standing in front of the General Sherman Tree – the magnificence and awe was too much to absorb, and took an hour of lingering around; to really believe it's happening for me. A part of me felt complete. For me, it was always more than a tree. That day I realized it for real. The largest living tree – the all so familiar impressive artistry that we all have craved to see for so long after reading about it in books – right before us.

The enchanting walks around Sequoia forest, the rousing emotions, the captivating views, the grace and elegance of surviving thousands of years withstanding fires and storms, converted it into a spellbinding magical forest – resulting in an indelible weekend.

The wildlife comprises of hundreds of black bears, rattlesnakes, mountain lions, and some friendly rodents. The visitor guide below is really well versed with all the different situations that our curious minds wandered on, and it was interesting to read.

Read More »

wss_username_token_service_policy use case for Oracle EBS and DRM Integration

The year of 2017 was an incredible year with tremendous ups and equal downs both at a professional and personal level. However it has again helped in garnering some very fruitful insights regarding everything around me and to plan few things better ahead. This is not to undermine any of the other years like say 2016 or 2015, but it is just that the impact of some of the incidents and the decisions that I have done and taken in 2017 will be changing my life forever. Let's see what 2018 has in hold! Wishing you a very happy new year ahead!

The web services play an important role in the authentication process for the EBS and DRM metadata integration. Few months back during the DRM repository movement we came across a few challenges with the MDS schema database host info which enlightened a few areas and paved way for some more personal study. After the initial setup, once the oracle-epm-drm-webservices WSDL is up and running fine, we need to attach a security policy to this application. This will ensure that clients like the program "Load Segment Values and Hierarchies" makes the request to the WebLogic Server to get the system generated token for the user (say EbsIntegrationUser) which can be passed to DRM. Then DRM can validate that token with OID to verify authentication instead of requiring a username/password.

Oracle Web Services Manager (OWSM) will need to be deployed first in the same EPM Server and domain where DRM Web Service is deployed. The database repository schema name for OWSM is set to a different value and usually ends with *_MDS which corresponds to Metadata Schema.

Once done, the new policy needs to be created in Weblogic under Farm_EPMSystem → Weblogic Domain → EPM System → Web Services → Policy Sets. Then in Step 3 for "Add Policy References" we need to select and assign wss_username_token_service_policy.

The details of the steps to be followed can be referred here. There are other policies also that can be used as per the scenario faced, however for this specific integration an authentication token suffices. Here are some more details related to authentication and uses of web services.

The ultimate test will be to make sure the token name is visible in the WSDL url. If the attachment of the policy is done fine, it will reflect in the URL. Else there's another approach to manually attach the policy which is kind of a workaround and done only in exception scenarios, which we faced few months back.

Read More »

Essbase Shared Member vs Duplicate Member vs New Dimension ODI Roller Coaster

The beginning of winter brought with it some exciting rides into the world of Oracle Data Integrator and Essbase. Feels lovely when things start to unravel their beauty and prowess. After several sessions (more to come!) of brainstorming on Shared and Duplicate members, we came to a few interesting findings that can help us plan better.

The "Duplicate" members are providing the below benefits:

• Allows same member to be used multiple times across altogether different dimensions. This is different from a "Shared" member where members need to be part of the same dimension.
• Besides the dimensions where we need Duplicate members, we can explicitly mark other dimensions as Unique which need not contain Duplicate members, thus ensuring integrity.

One drawback being once the outline is marked as enabled for Duplicate member we will be unable to rollback this setting!

However, unlike a "Shared" member, some of the inconveniences that are caused by "Duplicate" members are:

• To get clarity on the dimension used, Smart View users will now have to select either the fully qualified or ancestor-driven qualified name to ensure the correct member is selected. This defeats the aesthetics to an extent. Like [Parent].[Member].
• Existing calculations if used referring these members will need to be revisited to ensure they refer the correct required member from the corresponding dimension.
• The KM "SQL to Hyperion Essbase (METADATA)" doesn't seem to like allow loading the duplicate alias for the duplicate member. The second dimension loaded latter suffers, the first dimension gets loaded fine. The first gets all the alias fine, the next run doesn't get any alias for the members in the second dimension.

Considering all the above, the most effective solution now becomes a "new dimension":

• We are going to compromise on space and performance as a drawback for this
• But now, we can have our distinct prefixing and naming convention for each member to make them identifiable at-a-glance
• Reference of any members in any calculations can stay intact without any ambiguity 
• The KM "SQL to Hyperion Essbase (METADATA)" is at it's best again

Given the current world, this post will soon be updated over next few weeks.

Read More »

Call of the South West

      A trip to the southwest USA creates excitement even when we just imagine it, even though it is not my first time. It was during late winter that we started to plan a trip, but due to various changes in our lives things were regularly getting re-prioritized, until the month of August. It was extremely difficult to finalize a list of destinations, there were just too many to cover. My preference always lies towards places untouched by man, not that am not pro-progress, but the simple fact that we can unwind to unseen beauties and see the same things which has witnessed such notable historical events, listen to the amazing stories of the local people, the fact that so many lifeless mammoth rocks can be worshipped, and a cave that

failed to serve as a hideout during invasion from the east leading to hundreds being dead, or a view from one of the toughest hiking destinations in the world, driving via an amazing scenic road that can only be felt by our senses, each such fact, moment and place gives so much intensity and meaning to the time and place we are in, it helps makes my decisions a lot less difficult.
      Nearly 20 places made it to my initial list, but after much deliberation it came down to 16, then finally to 9 places. As always, per our new rule, we do not want to stress out so much that the thoughts of reaching the next destination late, becomes a constraint for cherishing the times at the last destination. Needless to say, the best part of a trip is the planning part, the quest for the unknown, the curiosity of how the new places will be like, the excitement of the memories that are going to be in the making, really makes the weeks before the trip so much memorable. This applies to not only special long-awaited trips, but even for mere neighborhoods trips for me. And as always, the planning part involves lot of research for the food explorer in me even

before the trip begins, which never completes and continues till the last day of the trip.
      After the bookings of the flights, the next best part was to decide between cottages or inns or motels or hotels. I have curated a mammoth collection in my Feedly (am so addicted to Feedly, besides Audible ofcourse) for specially travel blogs, and it has greatly influenced me on this aspect last few years. The closeness to the place and some local touch can only be felt easily if we can for once decide to reserve a cottage or resort close to our venue, which might be in a forest or at the foot of a mountain. With the landscape as it is in Arizona, it was not easy to find such a place in the
Navajo Reservation, but we managed to find one awesome place just 20 mins north-east to the Reservation, near Mexican Hat in Utah. We also reserved a cottage near Bryce Point in Utah, the slowness and calmness of these places really becomes a priority sometimes, due to which we often change our plans to spend more time at such places as I will divulge in the next post in this series. The sounds of nature seems music to our ears, days spend without earpods doesn't make us miss it anymore, it is then that we realize we are able to connect to what really matters in our lives.

Read More »