Informatica Analyst: an Intuitive Interface to Data Warehouse Reference Tables
Informatica Analyst users can co-opt this handy tool to maintain their reference data across databases. In this blog post, I show you how while explaining common configuration issues.
Co-opting Informatica Analyst for Reference Data Maintenance by Data Stewards
At the most recent meeting of the Houston Informatica User Group, I gave a brief presentation on a non-standard use for the data quality tool Informatica Analyst and interest was quite high, so I thought I should make a post about it, in case more people find it useful.
An All-Too-Common Scenario
Every data warehouse will, sooner or later, have what I like to call “human-maintained” tables. These are a subgroup of the reference tables that, for a number of reasons, cannot be refreshed from any reliable source: tables like hierarchies, targets, etc. The key issue is not that such information is not contained in any accessible DBs (that is not usually the case) and the problem is that the DB rows tend to lag significantly behind events that may change this information. For example, by the time takes for latest changes to the hierarchy percolate through layers of approvals and updates and ETLs, it might have been weeks, during which many reports were incorrect to a certain (sometimes worryingly large) extent.
The correct data instead exists in a combination of Excel sheets, emails back-and-forth and meetings in which the Stakeholders and Data Stewards sit down and hammer out the exact details and compromises. Their meeting are usually far-flung, and while meeting notes are all well and good, what the Data Stewards really need is a place where they can simply input the results of the meeting, rather than depend on the usual trickle-down to eventually reach the ears or eyeballs of DW developers such as myself. This, it turns out, is where Informatica Analyst comes into the picture in a surprising way.
Over the years, I have met many of these decision makers in various projects. By and large, these are very busy people that do not have the time or inclination to learn SQL, install programs, or even devote more than a few minutes to learning a new system of data input. Their schedules are busy, and filled with meetings in rooms away from their own computers. To date, the best way I have found to collect the data from them is to make it as easy and as painless as possible for them to input and maintain the data. This means to remove complexity: I don’t need something like Oracle SQL Developer or TOAD, powerful but much too complex. Analyst, as it turns out, is very close to the ideal. Sure enough, it’s not perfect, and I’ll get to that in a minute, but first, take a look at the standard Data Steward user experience:
Three steps: that is all it takes to get to a fully editable, audited interface to the DW. The Data Stewards in my current project were able to learn to use it in 15 minutes, and required no installation or configuration of their local machines: just an email with the URL, username and password. Each user has been given access to only those tables they have been approved to access, and they can add rows, delete and modify immediately. There is even a search and replace and, if needed, read-only access. Feedback has been quite excellent from all parties involved. It even has a reasonably functional audit trail that keeps track of all changes:
Behind the Scenes
Making the Analyst work as a DW interface is easy to setup and for the Data Steward to use. As long as you don’t need anything fancy, all you need is a table with auto-incremental surrogate primary key, and a view that hides all the columns the user need not see (the PK comes to mind, but there are usually one or two others).
As you can see in the pictures, I set up a target by quarter table (surrogated) and exposed it to the user with a view. They are given the chance to set the starting quarter for a given target, and what the target is for (although in practice I broke it down into three actual views, one per type). Total time to set up: less than an hour.
If, on the other hand, you need to do something more complicated that is also possible, although beware that the development time will increase substantially. For example, I did not just need the starting quarter, but the end quarter as well. This is done on the fly with a rather clever view. For even more complex situation, triggers can be employed. Now, since you will be operating primarily through views, you will need to familiarize yourself with the Instead Of formulation, which overrides the insert/update for a PL/SQL block. It is not for the faint of heart, but once you have completed the development, it works quite nicely.
On the Informatica side, things are even simpler: all you need is to create a Reference Table in Analyst with the following properties: Connected, Unmanaged, Editable relational table (see left picture). Place the reference table in a folder named appropriately, and grant read/write access to a “Data Steward” user group (in practice, I have many such groups, one by functional area, for ease of assignation). The group, as you can see to the right, only needs one permission: the Edit Reference Table Data access. This is sufficient for them to be able to log on, see and edit the reference tables. Any users that need access should be assigned to the appropriate group(s), which is a matter of a few minutes in each environment.
The Analyst is meant for data analysis (and does an excellent job of it, I should add), so a lot of its functionality is really not meant for the purpose I found for it. In particular, while it looks in many ways like Excel and Access, it is neither, and in some ways it shows. I am particularly disappointed by the lack of filtering (searching can only take you so far, especially when the values being searched are not contiguous) and advanced sorting. Sure, you can click on a column and Analyst will sort it, but it cannot sort on more than one column at once, which is very limiting, even in simple examples like the ones in this post. I find that sorting the view itself helps a little, but one can only hope that future Analyst versions will address this (for the record, I am working with 9.5.1; for all I know, this is solved in 9.6).
There is also no support for more user-friendly, resilient features such as pre-selected values (to prevent the user inputting typos and unrecognized entries), nor is there a way to set some fields as read-only. In some cases, this means my users input in one tab, and must go to a second one to check the calculated fields. Some of these issues can be addressed with triggers, of course, but those are expensive to develop, and do have their limitations.
But maybe the most baffling limitation is the lack of a link to changing the password of the user. The Informatica Administrator webpage, with the exact same look-and-feel has such a link in the options menu, but not in Analyst. While I have taken to providing the link in the introduction email, it would make so much more sense if it was in the website itself.
All in all, this secondary use of Informatica Analyst was an auspicious discovery. The time saving in setting up cannot be forgotten but, for my money, the real gain was in providing my Business Data Stewards an immediate, safe, access to the DW that they could exploit with minimal training and yet see the results of their effort immediately or, at worst, with a few hours’ delay – we do have certain reports that require cubes built overnight which causes delay in the reports reflecting the changes made to the reference tables.
Smartbridge is an Informatica Business Partner
There’s more to explore at Smartbridge.com!
Sign up to be notified when we publish articles, news, videos and more!