Configuring Ms SQL databases for Cognos Enterprise Planning

September 2nd, 2010

Just a quick side note -- I usually use simple recovery mode in Ms SQL installations because:

  • application databases become inconsistent during administration tasks so having a backup of database in a middle of GTP crash doesn't give you anything, you'll have to roll back to GTP start time. So backing up before and after administrative task is better.
  • publish databases are created via bulk load, which gets rather slow with full logging as log files grow very fast.

In ideal situations, application databases should be in Full Recovery Mode not to lose contributor input and publish databases should be in Simple Recovery mode. But in most cases Simple Recovery Mode and daily backups suffice.

Because otherwise -- and it's oh so common situations -- I arrive at the sites with planning databases occupying hundreds of Gb (no, really, I've seen another such case just yesterday).

Here's a simple script that turns all planning databases (selected by cognos_ep prefix) into Simple Recovery Mode and then shrinks them.

 
USE master;
 
DECLARE
    DBNames CURSOR
FOR
    SELECT
	  NAME
    FROM sysdatabases
WHERE name like 'cognos_ep%'
 
OPEN DBNames
 
DECLARE @Name VARCHAR(50)
 
FETCH NEXT FROM DBNames
INTO @Name
 
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	PRINT @Name
	EXEC ('ALTER DATABASE '+ @Name + ' SET AUTO_SHRINK ON WITH NO_WAIT')
 
	EXEC('ALTER DATABASE ' +  @Name + ' SET RECOVERY SIMPLE WITH NO_WAIT')
	EXEC('ALTER DATABASE ' + @Name + ' SET AUTO_SHRINK ON')
	EXEC('ALTER DATABASE ' + @Name +' SET RECOVERY SIMPLE') 
 
    DBCC SHRINKDATABASE( @Name , 0)
 
    FETCH NEXT FROM DBNames
    INTO @Name
 
END
 
CLOSE DBNames
DEALLOCATE DBNames
 

And a bottom line: Worry about backups! Hire a DBA (setting up backups is a one day job -- pay part-time)

Posted in ep | No Comments »

Pivot Tables and temp files in Oracle BI EE

August 27th, 2010

Pivot tables

Pivot tables are by far most used data view in Oracle BI Answers, since they provide 'multidimensional' analysis capabilities, aka dimension drag&drop, filtering and some basic calculations.

From server-side view -- this component provides worst performance, since all future 'cube' data has to loaded into BI server, aggregations and formulas calculated and transformed to internal format.
So what happens when user clicks a pivot for 200,000 cells cube?
1) SQL-query is fired into database
2) All resulting rows are loaded into temporary directory of BI server
3) If there are calculations to be performed -- initial temp file is read, calculated and new temp written out
There are two important instanceconfig.xml parameters for Pivot tables:
- CubeMaxPopulatedCells -- The maximum number of populated cells in the Pivot Table. If this maximum is exceeded, the user receives an error when rendering the pivot table.
and
- CubeMaxRecords  -- The maximum number of records returned by a query for the Pivot Table engine to process. This roughly governs the maximum number of cells that can be populated in a pivot table (unpopulated cells in a sparse pivot table do not count). If this maximum is exceeded, the user receives an error when rendering the pivot table.
All instanceconfig.xml options can be found here.

Temp files.

Temp files can be rather huge, I'm currently seeing files about 1gb size for medium sized reports. That, surely, because we're using MS SQL server and BI Server cannot "push" some calculations in database, but it's nice to keep in mind the fact that temp files can occupy some 20-30 Gb.
Temp files are cleaned out when session logs out, so some of them stay there for whole day.
Location of temporary files is set up by WORK_DIRECTORY_PATHS variable in NQSConfig.ini and by SATEMP enviroment variable. See this post for instructions on how to move tmp folder.

Speeding things up.

Rather huge files are written to temporary directory, so the IO performance becomes an obvious bottleneck for process.
What can be done:
1) Use multiple tmp\ directories on different disks to balance the load
2) Use Ramdisks for tmp\ -- I've tried this way and got a some fantastic 5x speedup on most IO bound reports. But it's pretty hard to find free 30 Gb of RAM, so we're using multiple disks for now
3) Use SSD for tmp -- that's what I plan to try next. I bought an SSD for my laptop about a year ago and my life has never been the same, so stay tuned )
Posted in oracle | No Comments »

Essbase ODBC configuration on Solaris

July 19th, 2010

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:

  1. There are 2 versions of odbc.ini file, one in 32bit driver folder, other in 64. 64 essbase uses 64 drivers.
  2. 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)
  3. 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' )

Posted in essbase | No Comments »

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

May 19th, 2010
"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.
Moreover, look at whole bunch of tools, based on SDK:
and tools, developed by third-party vendors:

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.
Posted in bi, oracle | No Comments »

Freebase GridWorks — a data-driven approach to ETL

May 11th, 2010

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?...

Posted in etl | No Comments »

TM1 hint – TurboIntegrator Local Variables

April 27th, 2010

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).

Posted in tm1 | No Comments »

Multidimensional reporting functions in Cognos and Oracle

April 25th, 2010

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  :(
Posted in cognos, oracle | No Comments »

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

April 13th, 2010
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

Posted in bi, cognos, oracle | No Comments »

Setting up ODI file-CDC capabilities

April 6th, 2010
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 )
Posted in oracle | No Comments »

Oracle Data Integrator, Oracle XE and JDBC NLS problems

February 26th, 2010

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

Posted in oracle | 2 Comments »

Next »