Applied dimensionality

Essbase ODBC configuration on Solaris

ykud — Mon, 19 Jul 2010 07:31:50 +0000

Using this blog as offline memory. Just ignore this post, if you're not up to Essbase )

If you're configuring DataDirect ODBC drivers on *nix system, be aware of following:

There are 2 versions of odbc.ini file, one in 32bit driver folder, other in 64. 64 essbase uses 64 drivers.
You should copy required odbc.ini file to essbasepath\bin. And you should create a symbolic link (ln -s) to odbc.ini file in essbase user home folder (/home/hyperion, for example)
When creating a SQL data source rule file -- ServerName corresponds to database server name, not essbase server name (type TNS name for oracle database)

Useful links:

Essbase SQL Interface Documentation

Metalink on search keywords 'odbc.ini + essbase' )

BI system metadata impact analysis and what BI system APIs are for

ykud — Wed, 19 May 2010 10:52:23 +0000

"Okay, this column in our BI metadata was supposed to be named Sales, not Sals.

But when we change it, what will happen to the reports that were created, using this name? And how many reports use this column anyway? Maybe we shouldn't touch anything to keep things working..."

Ever been in such situation?

This question can be answered by opening each report and then looking at it's contents ) A horrible task indeed.

Actually, this is exactly what BI system API are made for. Using them you should be able to automagically look into each report and find whether this column is used in it.

And as usual in this 'BI system Needs and Means' series, let's look how it's done in IBM Cognos BI and Oracle BI.

Cognos BI

Cognos BI has had an SDK (Software Development Kit) since ReportNet (for more than 5 years already) and this 'long-time in market' allowed to develop a whole ecosystem of tools to breed on top of it.

Concerning the question at the start of the post -- there's:

Cognos Dynamic Report Updater -- a tool to replace change column in every report.

http://www-01.ibm.com/support/docview.wss?uid=swg24021248

Moreover, look at whole bunch of tools, based on SDK:

http://www-01.ibm.com/software/data/support/cognos_diagnostictools.html

and tools, developed by third-party vendors:

http://www.brightstarpartners.com/bspsoftware.php

Oracle BI

In Oracle BI, finding such changes is somehow easier, since all reports are stored as xml files in presentation catalog folder. You just write a simple script that iterates on files, finds a string in file and reports back it's findings (or replaces this string with a new one). Sounds easy, but after spending a couple days at it, I'm not so sure anymore )

There are a few major point against this way

1) It's unsupported, there are no declared report modification API's, AFAIK, so you're scripting at your own sake. That would not be a big problem, if not:

2) Oracle BI presentation service adds a hash tag to report (and folder) names if they exceed 55 characters in length (see more on this topic here: http://obiee101.blogspot.com/2010/02/obiee-report-name-length.html).

2.1) If report's names are not in ASCII, they are converted to UTF-8 (but starting x symbol is replaced by #), so each non-ASCII symbol takes 5. So if you find file, containing desired string, you can only decode back first 9 letters of it's name -- report names are usually much longer than that. Therefore your searching capabilities are seriously limited.

2.2) Folder name hashing poses another problem if you're using Windows. It impossible to iterate through catalogcontaining hashes an # in it's name -- OS listing methods stop working. Hope that situation is better on Linux. An easy but entirely manual workaround is to rename catalog folders to numbers (1,2,3,4) before starting the script ant to rename it back after.

I totally wish that this situation will change in nearest future and there will be a simple Presentation Server API call that will bring report name from file name and vica versa.

As usual, if anyone is interested in scripts I wrote for the task (python, as usual + vbs) -- ping me.

Freebase GridWorks — a data-driven approach to ETL

ykud — Tue, 11 May 2010 14:11:32 +0000

Take 5 minutes to watch screencast for FreeBase Gridworks -- an interesting new approach to data transformation. Instead of 'transformation-based' approach of every tool on the market, this tool uses 'data-based' approach, which looks rather intriguing. Especially 'Undo-Redo' function )

I keep thinking when to use this tool. User driven data load in DWH? This tool lacks MDM capabilities in such case. Manual cleaning of csv files?...

TM1 hint – TurboIntegrator Local Variables

ykud — Tue, 27 Apr 2010 15:55:18 +0000

TM1 TurboIntegrator (TI) is truly a highly customizable tool I recently needed to be able to pass cube view names as a parameter for export process and it turned out to be really easy.
Although DataSource is selected via menu during TI process creation and there are no TI functions to change it, it can be done easily via TurboIntegrator Local Variables. There are local variables in TI (namely, DatasourceType,DatasourceCubeview and some more) that are set during GUI datasource selection. But they can be set in procedure as well.
So you just:
1 Add a Parameter of type string

2 Update needed TI Local Variable using this Parameter

And then work with data as you like )

There are many interesting Local Variables in TI, especially those that define ASCII output (delimeters, quotes).

Multidimensional reporting functions in Cognos and Oracle

ykud — Sun, 25 Apr 2010 17:47:32 +0000

As many of you already know I have a mixed attitude to Cognos BI DMRs (dimensionally modeled relational source) models:

performance is allways a consideration, full-table scans are unpredictable
there's always 'local processing' vs 'database only and some things not working' dilemma
using multidimensional functions in reports is oh-so-hard to debug when they don't work in complex situations.

Even after 4 years working with Cognos BI I still have to start multidimensional reports with simple cases to prove that functions work as I expect them to (sigh).

BUT -- at least Cognos has those functions!

We've just spent more than a week trying to replicate members() function in Oracle BI project. Function is as simple as can be, it just returns all dimension members from all levels in a single list (see documentation on IBM site). And if you add measure to report -- correct totals will be calculated. Oracle BI has the dimension with levels and aggregations concept, but no such function, so you have to turn over your head to achieve this. Replicating dimension table in logical model as many times as there are levels seems the easiest solution.

I'm really frustrated by this.

Definitely not the problem I'd like to spend time trying to solve :(

Connecting BI system to Active Directory. Oracle BI vs IBM Cognos BI. Scripts to extract Active Directory information.

ykud — Tue, 13 Apr 2010 06:00:31 +0000

I consider Single Sign-On setup through Active Directory(AD) a key point in every system's life.

Up to this point -- it's just a PoC, a prototype or a department (10-20 users) level application.

AD integration step is usually prosponed, because it's documented somewhere and there are much more important things to do, like showing that this system is of any value at all.

But adding an AD connection mean that system is rooted (even if lightly) in company's IT ecosystem.

There's always a dilemma on groups and rights storage:

1) You can use AD groups for BI level user rights. AD admins are usually against this, it means more work for them.

2) Just use AD as a user catalog and add all groups and rights information in BI system. This means introducing more work to BI administrators and adding potential security problems. Imagine if BI admin won't be notified of a person transfer from department to department or from sales to marketing. And why should BI administrator even care about that )

Pro's and cons of each way are easy to deduct and choice is always organization-dependent, so I won't stop on that and go right to complains section.

Using Active Directory in Cognos and Oracle BI

Cognos

About a year ago we had a PoC on Cognos, where the requirement was to not just common "users and groups", but also we had to use custom created AD properties for data filtering.

It's easy to do in Cognos 8 BI: you just add Framework Manager macro that returns this property. Piece of cake really. And it turned out to be hard for our competitors.

Description on IBM portal, historical number -- 1027162

Oracle

Recently I've had to do the same with Oracle BI and it turned out that Oracle BI's current integration with Active Directory is pretty basic. The only way to communicate with AD is authentificate users through it, there's no way to access User groups or custom properties (there is a way, but it requieres changing ActiveDirectory, which isn't good at all). See Metalink note on this subject: 544828.1

So there's no built-in way in OraBI to get all those delicious groups and other properties. But there's always a way out called scripting )

Extracting Active Directory information to csv

You can write a script that will extract needed information from Active Directory to a csv file and then schedule it for nightly extracts, load it into a table and use this table in Oracle BI security variables. This is a reliable solution, but it introduces a time lag between AD modifications and your extracts, which can be important from security point of view. Same example --user is transfered and can still access BI data he's no longer authorized to see until Active Directory information is extracted again. Setting small extract lags can bring unwanted load on Active Directory, so it's once again a balanced choice to make.

Links for extracting Active Directory information to csv:

Step by Step Active Directory Export Guide
http://www.rlmueller.net/ADOSearchTips.htm
http://www.computerperformance.co.uk/Logon/LDAP_attributes_active_directory.htm
JXplorer -- a graphical tool to browse any LDAP server

Setting up ODI file-CDC capabilities

ykud — Tue, 06 Apr 2010 11:10:50 +0000

How to set up a trick called 'put a file in a folder and it'll dissappear in DWH' with Oracle Data Integrator.

Imagine that we need to upload a text file in DWH whenever it appears in some folder with no defined schedule.

It really easy to do in ODI:

1) Create a package with ODIStep 'WaitForFile' which will scan the directory on predefined basis and will move found file in a folder where it'll be processed. I usually archive and timestamp file on the way -- this saves a lot of time in "where did this number come from" arguments.

2) Insert a scenario that will process the file

3) Steps 1-2 work only once, so we need to cycle things up, so that after processing a file, system starts waiting for a new one. This is as easy as making a scenario of a package we're creating and inserting as step 3 )

So we've got a scenario which grabs files from a folder and processes it. Just run it and it'll wait in background and process all the files you throw at it. But what happens if we reboot ODI agent server? To ensure that this task starts automatically -- just add a schedule for this scenario with 'Start on Startup' option.

Bingo: disaster-prone file vacuum cleaner )

Oracle Data Integrator, Oracle XE and JDBC NLS problems

ykud — Fri, 26 Feb 2010 16:51:45 +0000

Since I've spent a day breaking through this mess, I'd share my way of getting ODI to work with XE as a repository.

ODI repository creation is sucessfull, but after that Topology Manager and all other ODI modules won't work, throwing "ORA-12705: invalid or unknown NLS parameter value specified" while connecting to XE.

There are 3 java connections to repository in ODI:
1) For Oracle Data Integrator Modules

Unfotunately, I couldn't get ODI to work with correct NLS by changing odiparams.bat as they recommend on Metalink, but downloading latest jdbc driver from Oracle and removing old ones (physically deleting ) from \drivers folder helped with this part.

2) For ODI Agent

After trying in vain with adding "-Duser.language=en" "-Duser.region=US" to odiparams, I've created a new wrapper configuration file (stored in ODI_HOME\tools\wrapper\conf) by copying snpsagent.conf to and adding this options to line

wrapper.java.additional.1=-Djava.security.policy=server.policy

so it read

wrapper.java.additional.1=-Djava.security.policy=server.policy -Duser.language=en -Duser.region=US

reinstalling agent with the new wrapper conf file (you explicitly run 'agentservice -i -s agent_name 20910 new_wrapper') worked.

3) For Metadata Navigator

I've used Apache Tomcat as a Metadata Navigator container. Setting additional java options (the very same '-Duser.language=en -Duser.region=US') for tomcat solved this one.

See related Metalink Note: 471749.1

Macro functions and prompts in Cognos BI reports

ykud — Wed, 17 Feb 2010 18:43:49 +0000

This post spent a year in drafts -( But Macro functions are an interesting feature of Cognos BI and sometimes their usage is unavoidable.

You can use macro functions in Report Studio to do some manipulations with prompt values before they are used for actual processing in filters, report functions, etc.
As a general (and very common) example, you can use macro functions to do datetime format transformations, to allow one various filters to be based on a single prompt (selecting 'Version X, year 2009' in prompt, which you can split up to two filters, for year and version) and do some other string conversion stuff.
Usually such need arises, when you have drill-through reports with different source-target dimension member formats, such as OLAP (Transformer, TM1, Ms AS, Essbase) -> Relational or vice versa. For example (a very simple and not close to real-life), if you have a detailed transaction-level report based on relational database and a MOLAP-aggregated data on top of it.

Fictional Example

If, for example, your date MUNs in OLAP are like '2010-02-10' and relational database expects something like '2010.02.10', you can change string value of prompt by changing filter in detail report from

date = ?date_prompt?

date = #subtitute('-','.',prompt(date_prompt,'string','1'))#

prompt macro function returns prompt value and substitute subtitutes strings )

Real-life example

One of real-life examples, where I had to use macro functions extensivly was OLAP(Essbase) to Relational drillthrough setup.
When using Essbase it's better to have unique member names for dimension elements since this speeds query time a bit. So usually some unique identifiers are added to common conformed dimensions. On this project we've constructed a cube with financial transactions, with a number of participants for each operation. The same company can be a client in some operation and a supplier in another. So unique postfixes were added to each of dimensions in Essbase cube. Relational model for Oracle, however, had a single "Party" dimension table, which contained all possible participants.
This problem is easily solved with tecnique given above, I've just removed unique identifier.
The other problem in the same case is that Essbase dimensions usually have hierarchies ) Since operation participants can be aggregated by industry or region I can receive industry (or region) label in dimension prompt (building special prompts for each level is just impossible).
So I had to detect the level of member I've received (which was easier by having those unique level identifiers) and then to filter accoding column in relational table. So I can receive values like 'Pharmaceuticals SUPPLIER INDUSTRY' or '10241235 SUPPLIER ID' from Essbase. Creating such a filter turns into filter like:

when (#sq(sb(csv (grep(': SUPPLIER_INDUSTRY';array(prompt('SUPPLIER_ID';'string';'1')))) ))#,'[]') THEN
([Relational Souce].[Supplier].[SUPPLIER_INDUSTRY] = #substitute(': SUPPLIER SUPPLIER_INDUSTRY';'';prompt('SUPPLIER_ID';'string';'1'))#)
when (#sq(sb(csv (grep(': SUPPLIER ID';array(prompt('SUPPLIER_ID';'string';'1')))) ))# ,'[]') THEN
([Relational Souce].[Supplier].[ID]= #substitute(': SUPPLIER ID';'';prompt('SUPPLIER_ID';'string';'1'))#)
else (1=1)

This really looks awfull, but it's the only way I could find out to do 'search': that is to form an array from prompt values and do a 'grep' over it and compare it with empty string. But it does the trick nicely.

BI and CPM Gartner Quadrants ’10 are out

ykud — Mon, 08 Feb 2010 11:57:37 +0000

Rather interesting:

CPM

The CPM Big Three(aka leaders quadrant) is formed and I think they'll just waltz around the quadrant in next couple of years.

PS: Just to notice, they say aggregate awareness for Cognos BI is in beta-testing. Just after I spent so much space promoting database-level aggregate navigation.