Sunday, November 05, 2017

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.

Saturday, October 14, 2017

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.

Tuesday, August 29, 2017

The Fascinating World of Ford

      The stories of how the automobile industry revolution started had been in the air for sometime around me after watching a few documentaries in YouTube. It is then that I started learning more about the smallest of the small details of Henry Ford's life, and gradually it became an obsession. When I learnt that he has created a world where a snapshot of history is preserved so well, it was irresistible to drive up to Dearborn, Michigan.
      The exquisite quality with which various aspects of historical importance, some original, some amazing replicas, has been created, leaves one awestruck and longing for more when returning. The Henry Ford Museum and Greenfield Village together form a masterpiece of portraying almost all aspects of the history of the growth of this country over the centuries. Horses, cattle and poultry are wandering in fenced areas created similar to centuries before. The Ford model cars from the earliest generations giving rides to tourists, it was hard to resist the goosies.

      Several artifacts like Edison's laboratory, the Wright brother's home and shop, and detailed housing complexes consisting of schools, courts, etc are arranged to give the feel of an actual city around us. Most of the objects are original or are close replicas of the originals, and arranged exactly how they were present in the actual laboratories. Each step felt like we needed to pause and absorb the intensity of the surroundings, the enormosity of the thoughts made even a lifeless historic backyard or fireplace grandeur.

      A visit to the world of Ford is not complete without getting a feel of the first Ford Model cars. Getting to know the stories of the struggle to create the car, and how much pain had to be endured to overcome each hurdle step by step to come where we are today, is astonishing. The respect for the legacy left behind continues to grow as we get to know more. We get to sit on the cars and assemble parts of the cars with the help of friendly guides, who walk us through the step to tighten screws for a few minutes to give us a feel of how things used to be done. Models of the Ford are kept showing each of the parts and how they can be assembled to create a car.

      The widespread usage of the car and the way it changed lives by allowing x-large distances to be covered in such small times was a new thing for all, and was so beautifully captured. Earlier people used to be able to cover only small distances in the limited times, and travelling to cities was elusive for the majority. But after the spread of the car population, things changed in multiple ways, leading to better industrial growth, better agriculture, medical care, and all other aspects of life. It brought all of us close to each other, marking a significant step in the human evolution story.
      As we walk through the amazing time castle, we find ourselves walking through the growth of the civilization in this land, how each president contributed, and then the tumultuous 1860s during the Civil Wars. The details are explained in crisp and lucid style and is one of the most comprehensive collection that can be completed in a limited time. Several artifacts and memoirs are preserved, like Abraham Lincoln's chair from Ford's Theater, the bus where Rosa Parks sat before the Civil Rights Movement, and several signboards and newspapers from memorable times. Taking a seat in this bus triggered thoughts of all the events that followed decades back, it is just a grim recollection of the events that our past generations encountered.
      Discriminations of decades have been vividly described with references, reports, documents, printings pertaining to that time. It is hard to imagine a time where this was all true, but yet it was right there with evidences before our eyes. The first hand witness details and stories and reports of a time so near to our past gives chills and cannot stop us from continuously appreciating the grit and spirit of these brave people. The time is beautifully represented by lighting and color effects on fonts and texts simulating an atmosphere that gives a feel of the actual days decades back.
      As we continued our dream tour of this amazing place, we learned details of various cars, how they were made, and their standout features. It showed how the efforts were continuous on moving towards where we are today, and bit by bit how we gradually came here. It was nice to learn how different routes and roads started getting prioritized as cars evolved, how hotels, motels, inns, lodges came into existence. Replicas of the earliest lodges and inns gave glimpses of the life decades back and how we might have spent our nights had we lived during that time. The basic facilities we have today, regardless of the technological advances, are far more luxurious than anything we could have imagined that time for the same lifestyle.
The use of trains for large distances and the ever increasing power and speed of the engines that led to the Industrial Revolution was described in amazing details with several replicas and actual models of the engines kept, some of which were over 20 feet high. The impact these trains had on the lives of millions of people across the geography by carrying almost any amount of anything in short times led to growth in man's lifestyle that we have seldom seen in the recent history of man. The lives of everyone started changing due to the effect of this period and it is an incredible feeling to actually get to learn the stories by watching movie clips, reading features and news of these actual trains from that time.
      A section of the Presidential cars awaited us where the names of each of the Presidents were engraved with details. All the cars were there including that of John Fitzgerald Kennedy. The amount of protection and the techniques used to guarantee safety for the most important men in the country was amazing to see, read and learn so closely. It was sad however for some cases no amount of protection could protect us from ourselves leading to sad early ending of promising lives.
      One can only thank Ford after visiting this place, for helping preserve a fraction of our lives from the most important times to learn about ourselves and help create a better, brighter, future.

Friday, August 25, 2017

Immersing in the Mystery of Dakota

Time is the medium by which life unravels itself. We can understand ourselves better and get to know what right means by looking at the centuries of years behind us. A trip to Dakota sparks that consciousness in us which makes us question why we are what we are. A visit to the Crazy Horse Memorial triggers waves of emotions which are hard to explain. The questions of why, how, continued to plunge us down more and more into repentance, one which could do nothing about.

The days were spent equally amazing at the same time, understanding the undying spirit of Crazy Horse, Sleepy Eye, Little Crow, it just showed how we could not adjust with our fellow neighbors and how systems fell apart again and again. The struggle for existence deserves heroic mentions for each and every tribe, to preserve the individuality and trying to ensure a prosperous future for their future generations. Until gradually their population thinned, and they had to be in the run in the cold, hungry and without basic amenities.

A trip to Dakota is never complete without understanding the history of the tribes and their years of struggle. If reading the book Bury My Heart at Wounded Knee has not given you goosebumps, it is sure to give goosebumps once we visit Dakota after reading the book. All the names that are so much familiar with us after reading the book comes almost alive in front of us with all the artifacts preserved in details. We get to see their way of life, the tools and techniques that were used, why they did what they did during hunting, fishing, cooking, and all other aspects of life.

The meaning of trust got lost again and again with so many treaties of peace broken over the years. It is single most unfortunate trait of us when we want to take away from others for our own benefit, by betrayal. There is no doubt why warriors were worshiped when talks failed, since the only way one could stand up against untrustworthy negotiators is by taking up arms. Statues of Crazy Horse is found everywhere and the superhero stature transcends time, and has resoundingly found a place in the hearts of all. It was my wish if only the mountain carving could have been completed beyond the arm and the face when I went, as evident in the adjacent picture. The white statue is what the mountain carving in the background would have looked like had it been completed, it's still in progress currently, and it started in 1948.

The conquest of lands by the people who could not (or did not want to) believe all of us could stay together did not destroy the culture of the tribes. They continued to carry forward their traditions in whichever way they could, and we were lucky to witness a show of their celebratory entertaining dance. The simple things of life made so much meaning for them, starting from animals to weather, colors to shapes, the smallest of the small things has become a part of their life which is really soothing to see from our WiFi centric vision.

There are things which we so so wish we could undo, but we cannot today. Even if we wish what it would have been if this and that would not have happened. There's always another way, it is sad that we seldom put in the effort to find the better way, even if it maybe painful and difficult at the start. And this applies to every aspect of our life, where often the easy option becomes the solution that ultimately may not be helpful at all in the long run. The essence of Dakota thus lingers on even after coming back. Someone asked me today, why bother after the trip is over, it was just another weekend getaway. I only wished I could explain how this getaway changed everything.

Monday, August 07, 2017

A Trip to the 1850s

The last few months have been incredible. It isn't something unusual to come across great movies every now and then, but the impact of a great book is so much more. It all started with the sci-fi 11.22.63 that held me spellbound for days till the last line. Time travel had always attracted me, not to mention how many times I watched Back to the Future every time it's on.

Then flooded with amazing books on the native American history, visits to Dakota last year, witnessing the struggle of man over time, it actually starts to feel so much more than just facts. Every trip to a museum, every detail of the significances of each antique, emphasizes so much more regarding the nature of man than just that period. It's how we suffered and how we understood our surroundings, the cause and effect repeatedly over time that shaped us as who we are today. We are blessed today that we have documented collections of historical events that can enrich us into understanding who we are, what we have done, and what we can do. It's not about the landscapes, monuments, attractions, sceneries, instead it is all about the people and the cultures that have played role in shaping our today's world.

A recent visit to the historic site of Abraham Lincoln's home sparked several realizations, specially after reading the book Team of Rivals. It's amazing to know how someone could endure and tolerate so much in a single lifetime, and that too without letting it change the flow of life and the challenges that it brings with it. The visit felt an icing on the cake after finishing the book that had so much details that one can ever ask for.

It is always a pleasure to be knowing what we could never have known had we not lived in the time. Maybe it's appearing too much obsession with the past, but maybe we really to need to know the past well to understand ourselves and create a better future. Really do not want some of the things in history to repeat itself!

Wednesday, May 24, 2017

OBIEE Dashboard Export to Excel PDF HTML using Custom Links

Oracle Business Intelligence Application in it's out of the box form do not provide any solution to export a dashboard from another dashboard using custom links. But given the available resources, it is possible to create custom links to export a dashboard in Excel format, PDF format, or HTML format. To do this we need to create few snippets of HTML and Javascript coding, and can add some cool animations as well if need be.

To achieve our goal, we first need to have the name of the dashboard and the tab which we will be using to create our extracts. Once that is ready, the below HTML and Javascript codes will help create few export links that will extract exactly what we want.

<div id="exports_hdr" onmouseover="document.getElementById('exports_dtls').style.display = 'block'; document.getElementById('exports_hdr').style.display = 'none'">
<font color = "mediumblue" size = "2"><u>Export</u></font>
<div id="exports_dtls" style="display:none">
<img style="vertical-align:middle; margin:0px 0px 0px 5px" alt="" src="res/sk_blafp/catalog/exporttoexcel_ena.png"><a name="ReportLinkMenu" title="Export to different format" href="javascript:void(null)" onclick="return saw.dashboard.exportToExcel('<<dashboard_name>>', '<<dashboard_to_be_exported>>', true);"><font color = "mediumblue" size = "2">Excel 2007+</font></a>
<img style="vertical-align:middle; margin:0px 0px 0px 5px" alt="" src="res/sk_blafp/catalog/printtopdf_ena.png"></img><a name="ReportLinkMenu" title="Export to different format" href="javascript:void(null)" onclick="return PortalPrint('<<dashboard_name>>', '<<dashboard_to_be_exported>>', true, 'pdf');"><font color = "mediumblue" size = "2">PDF</font></a>
<img style="vertical-align:middle; margin:0px 0px 0px 5px" alt="" src="res/sk_blafp/catalog/printtohtml_ena.png"></img><a name="ReportLinkMenu" title="Export to different format" href="javascript:void(null)" onclick="return PortalPrint('<<dashboard_name>>', '<<dashboard_to_be_exported>>', true, 'html');"><font color = "mediumblue" size = "2">HTML</font></a>

Once this is working fine, it will show a cool 'Export' link, and when we do a mouse hover it will instantly show three Export links for Excel format, PDF format, and HTML format.

Let me know if you have any other ideas to export in different formats!

Sunday, January 08, 2017

DRM Data Loading Automation using ODI

The Oracle Hyperion Data Relationship Management application is a pretty flexible tool and most of the activities that can be done manually can be automated (using ETL tools like ODI, Informatica PowerCenter, etc). Recently I was presented with a business scenario by one of my readers which is pretty interesting yet tricky. Yes it involves a request to automating a manual process as you might have already guessed.

The requirement goes as below:

The source systems are DWH and HRMS. Hence, the data/master data which we will get would be through staging tables to DRM. The source system would send the full data every month on month to the staging table and from where DRM has to pick. The comparison process in table (interim changes) first should go to the business user in an email and once the reply is ok, it should be incorporated in DRM and then publish to down stream applications.

To proceed with this activity, first and foremost we need to be familiar with DRM action scripts - which will involve generating Add, AddInsert and Move scripts. In addition to that, if we have an ETL tool in our environment (like ODI, Informatica PowerCenter, etc) it's beneficial - else we can also use SQL to achieve our purpose as we will see next.

Approach 1:

Since the source systems DWH and HRMS are sending the full dataset every month - there needs to be a mechanism to detect the changes arriving at the staging tables in DRM end. This is where the Changed Data Capture (CDC) feature of ODI can come in handy, or else if we are using any other equivalent tool this feature will come in handy. This changed data capture can also be achieved using standard query languages so that the records that have changed/inserted can be flagged (say U/I) accordingly.

This set of records can be sent as an email (with attachment if data volume is too big) to the respective business users for approval. Let's say we have a field called "APPROVED" in our table and all these records are defaulted to N. Once the approval comes from the users - these fields need to be set to Y for the approved changes manually by the IT team. Until then these records will sit idly in the staging table without propagating to DRM. This manual flagging step unfortunately cannot be avoided since there is no integration yet between DRM and email server.

Now, once the records (say 8 out of 10 got approved, so we have 8 Y's and 2 N's) are flagged Y - they will be considered for the next steps of processing. The next steps are to create the Action Scripts for DRM to create the Add and Move scripts. Assuming the DRM version and hierarchy are already existing - the scripts formatting once done as per the required format it will be ready to be loaded to DRM. If the target txt/csv Action Script file is not in the same server where DRM can read it from, it has to be further SFTP ed to that location. Or else we can use SQL Loader to fetch the data from the Action Script table to the DRM server and then schedule Windows Batch Scripts to load the action script. There are quite a few ways this can be done and all depends on the environment setup we have in place.

Approach 2:

If we want to keep the DRM staging table untouched by the manual update of Y and N flags - we can fine tune our approach to a control-file based design where the member names that are approved, will be kept in a file - that will be used for lookup. So say N1 and N2 are approved, the file will contain the below data:


Here X denotes the batch id (yes, we need to create a "BATCH_ID" field in the DRM staging table which should be updated with a sequence generator type of ID for every record flagged U/I for that specific load run) - which should be unique for every load.

Let's see why we need this BATCH_ID field. If tomorrow our load runs and detects a change for the same member N1, and it finds N1 already in the file, it will get processed without waiting for approval. So since the batch id will change in the DRM staging table for every run - we do not have chances to unapproved changes of the same member to flow through since the next steps will check if both the BATCH_ID and member name are present in the control file. Instead of a file, this can also be made into a small table with only 2 columns which the IT team will have update/delete privilege to manage it daily.

This approach will provide a more safer control to the IT team and avoid the risk of accidentally modifying sensitive application tables or objects. Anyways working with DRM is always extremely a cautious activity day-in and day-out where a simple typo can cause widespread implications to multiple downstream systems - so minimizing risk and avoiding manual errors is preferred to be a part of any design.

How do you prefer to automate your DRM data loading processes?