If you’re a serious Cognos BI shop with Dev-Test-Prod migration and release management, you definitely have to read this Proven Practice article. Overall, proven practice articles are very good, I’ve skimmed through half a dozen of them.
It seems that the easiest way to gain full article list is to filter by cognos_business_intelligence tag. This reminds of one of my former bosses who inquired Oracle, whether they had special training on how to find content on their site.
I ended throwing RSS-feed for this search in my reader, so I hope not to miss any updates.
We’ve just finished our TM1 load-testing project and there are some conclusions I’d like to share:
1) Stress-testing scenarios should model user’s behavior as closely as possible (hence all that xml configuration in
stress-testing tool)
3) Real-model performance testing is way more accurate than general capacity planning guidelines. So if you’ve got heavy hardware at stake: consider doing a preliminary load-testing, it can reduce potential hardware cost. It was multiple times in our case )
4)
TM1 9.5.2 Parallel Interaction feature really works, especially in models with lots of rule calculations. We’ve got 1,5 performance gain on 100 concurrent writing users just by turning it on. And yes it takes 20-30% more memory, sigh.
Overall, watching the CPUs throttle up on big AIX machine makes my internal geek happy.
It was a very interesting project to do, I don’t think a lot of people emulated a 1000 users load on TM1 model. Although, I personally consider such user load unreal for a planning system, but if the customer says so — we test it.
Spent half an hour sketching up Cognos process architecture — what goes where and when. Will refer to this drawing in my future hardware planning assigments 
Comments:
- You can separate all components (Gateway, Application Server, Content Manager) into separate machines
- There can be only one Content Manager service active in cluster at a time
- There may be multiple gateway servers, as I was corrected. What I originally meant was close to something like ‘if you give user a cognos url, that url points to one specific gateway server’, so you have to do clustered web-server and application server installation for gateway fault-tolerance.
- There may be multiple active dispatchers in cluster
- Each dispatcher consists of multiple services, that may be turned off or on for a specific server in cluster.
- Most dispatcher services are processed in application server (Tomcat by default and almost any other of your choice), which can use either 32 or 64 bit java machine
- Reports (interactive and batch) are processed by a special process ‘BIBus’, which is a C++ 32 bit application (hence the 2Gb cap for each process and CCLOutOfMemory errors). Number of such processes depends on Dispatcher service settings, load on each process depends on affinity connection settings.
- BIBus processes generate temporary files if they require significant amount of local processing (hence UDA files problems)
- Each BIBus process can occupy up to 2Gb RAM and 1 CPU Core
Basic rules for capacity planning I use (your mileage may vary greatly, it depends on reports design, size and calculation complexity in first hand):
- Estimate number of concurrent users
- There should be a BIBus process for each couple concurrent users
- There should be at least 1 or 2 cores for all other dispatcher services
- There should be at least 1-2 Gb of RAM for Application Server
- There should be at least 2-3 Gb for Operation System (it depends on operation system greatly)
- There should be a separate database instance for Content Store and Audit database
For example, if we’re talking about 20 concurrent users (mind that it’s 2000 named users by basic IBM metrics), we’ll need:
- 10 BIBus processes, each will take 1 Core and 2 GB Ram
- 2 cores and 2 Gb ram for dispatcher and application server (for each dispatcher server, if there going to be a cluster environment)
- 3 Gb for operating system (for each dispatcher server, if there going to be a cluster environment)
And it gives us 16 cores, 32 Gb RAM for a single server.
Suggested reading:
– Architecture and Deployment Guide )
– these threads on DeveloperWorks, where Phil W. describes all this way better than I’ll ever do:
http://www.ibm.com/developerworks/forums/thread.jspa?messageID=14233120
Update: That’s why I like the web — I
was corrected by Dave even in the middle of weekend ) Thanks again, Dave.
¶
Posted 25 June 2011
† ykud §
bi
‡
°
Just in case you missed it — read this two posts by Mark Rittman (OBIEE 11g for Hyperion Users – Are We There Yet?, Incremental Essbase Metadata Imports Now Possible with OBIEE 11g) on the oh-so painful topic of Essbase-BI integration. I participated in some projects with this combination a couple of years ago (when BI 10g was the only option) and this experience left me very confused. Reading Mark’s cover-up for 11g — it seems a lot has changed, but still not enough (
Both Oracle BI and Essbase are totally great tools, but this 3 (or even 4) year long integration story gives us a couple interesting points to think about.
- Efficient MDX-generation is hard. 50+ MDX queries for a single BI 11g report are a real showstopper, since you can kill even such a mammoth as Essbsase with such load. Moreover, such query patterns (1 query for a couple of cells) negate Essbase caching and calculation efficiency. So almost all Oracle BI reports on Essbase I’ve encountered are handwritten MDX, tied to BI report by using Evaluate functions. And that’s a thing everyone should avoid.
- Managing OLAP metadata in relational repository is hard. When you’re trying to incorporate both conventional table structures and flatten OLAP dimensions in the same wire-model, you get cut by torn strings. Incremental OLAP model updates are very hard, since you have to preserve user changes and yet to ”find out what’s new”.
So everyone using Oracle BI + Essbase I’ve talked to end up using scheme like this:
- Use some tool for rapid OLAP data exploration (Excel Add-In, Visual Explorer)
- After some query layout is formally accepted — molten it in concrete by adding handwritten MDX for Oracle BI report
Given that in this OLAP + RDBMS BI scheme there’s also a problem of security settings synchronization (no built-in integration for this as well), introducing OLAP in BI system is always a big decision.
I may be overly pessimistic, but given the rate of RDBMS adapting for analytic purposes (aggregate awareness, partitioning, MPP-architecture, compression, columnar storage) and BI tools complexities when working with OLAP, we might witness slow decrease of OLAP engines usage for reporting (whereas in areas like planning OLAP is still unbeatable).
To cover both sides of the story I’m aware of — I’ll write about the way Cognos works with OLAP engines in next post.
Always thought it was a joke … Up until today.
Really, there is a Speed = True parameter in Transformer MDL. I adore developer’s sense of humour )
PS: There’s so much going on lately — hold on for some interesting news & posts.
¶
Posted 05 June 2011
† ykud §
bi
‡
°
What is an aggregate?
One of the main ways to speed up your DWH is to use aggregate tables, so that every time you ask about monthly sales per region system doesn’t have to sum up individual store daily sales transactions. Since a single aggregate table can speed up multiple queries, designing a set of aggregate tables is a hard task give that you’re trying to maximize query speed with limited resources for aggregate storage and processing. There’s a very good book on the topic that I highly recommend.
Ok, we have aggregates — how to use them?
Once you’ve populated your aggregate tables, you need to use them to answer the queries, i.e. select possible aggregates for a given query, substitute base table references with aggregate tables reference in SQL and then fire the query to DBMS. To do this efficiently you need to know which aggregates are available for what levels.
Tool which does it is called aggregate navigator and it can be implemented in a number of ways:
1) Human-based: you just tell your analysts to use new tables in their SQL or BI tool. No comments, but I’ve seen this working )
3) BI tool based: this case is commonly called aggregate awareness. You specify aggregates for levels in BI metadata modeling tool and then BI tool constructs appropriate SQL
As of Cognos 10 — there’s no such option as aggregate awareness. But since I’m already an option 2 fan, here’s the code showing how to use Oracle Materialized Views for this.
Sample solution
Following forum discussion: aggregates were generated in some external statistics tool (let it be SAS or, even better, SPSS). So we have a base table and an aggregate one.
ALTER session SET query_rewrite_enabled = TRUE;
ALTER session SET query_rewrite_integrity = STALE_TOLERATED;
SELECT * FROM f_sales;
/*
1 200101 Canada Toronto 100,00
2 200101 Canada Monreal 200,00
3 200101 USA NY 500,00
4 200101 USA Buffalo 20,00
*/
SELECT * FROM f_sales_by_country;
/*
1 201001 Canada 111
2 201001 USA 222
*/
CREATE materialized VIEW f_sales_by_country
ON prebuilt TABLE without reduced precision
enable query rewrite
AS
SELECT country, SUM(VALUE) AS sales FROM f_sales
GROUP BY country;
SELECT country, SUM(VALUE) AS sales FROM f_sales
GROUP BY country;
/*
1 Canada 111
2 USA 222
*/
Cognos 10 Redbook is now avalaible on IBM site. I always respected RedBooks initiative, because these are actual books with lots of practical knowledge, not just product documentation. I’ve only skimmed through this book, it seems less tips’n'tricks than Packt one about Cognos 8 Report Studio. But it’s a very good overall coverage of what Cognos BI is and how all parts fit together.
And there’s an absolute must-read article about new TM1 9.5.2 Parallel Interaction feature.
I’m actually asonished to see an article of such depth in support knowledge base. It contains detailed explanation:
- of how things worked before, common problems and workarounds
- what has changed in server engine
- how will this change affect current systems
It’s actually terrific, I’d kill to get more of such articles on tools I work with, not just ‘try reinstalling ones’.
On an unrelated note: I’ve uploaded updated versions of tm1 stress testing tool (with more input parameters) and tm1 model comparison tool (just some bug fixing).
And on another unrelated note: I was nominated an IBM Information Champion again. Thank you all for reading this blog )
Another simple script, this time about reporting changes between a couple TM1 models. Developed for pre dev->prod migration in one of our projects, but maybe useful for answering ‘what did we change since backup?’ or even ‘what did they change while we weren’t around?’ questions.
I’ve made random changes to Planning Sample — here’s the resulting change report
No need to install anything, I packed it into .exe file:
1) Download application archive, unzip it to any folder
2) run
tm1cmp.exe -o Path_to_Old_Model -c Path_to_Changed_Model
You can also specify -r report_file_name for name of generated report
3) Open report.html or the filename you specified and enjoy
How it works:
- basic file comparison
- for changed files I use difflib to detect changes + very basic logic to ignore simple edit_datestamp changes
It seems a very interesting new release. They’ve added a Parallel Interaction feature, which should decrease locking contentions that we’ve been working with. Would be nice to try this thing out.
Take a look at new features guide and operations for more details.
And Russian seems to be fully supported, cool.
Watch TM1Forums threads for more comments as people start trying it out.
9.5.2 – New Features and Issues
Migrating to TM1 v9.5.1 or TM1 v9.5.2?
¶
Posted 19 March 2011
† ykud §
tm1
‡
°
A more thorough answer to this LinkedIn thread.
Several points before we start:
- You can load a rule file in TI process by using RuleLoadFromFile function
- It’s pretty easy to generate need rule file using ASCIIOutPut to write the need rule.
-
But ASCIIOutPut opens the file on procedure (EPILOG, PROLOGUE, META) start and closes connection only when procedure ends.
I was thinking about using one TI process for generating a number of rule files (simple [Dim.Element1]=[Dim.Element2] format, where Dimension and elements are parameters) and attaching them to a list of cubes, so the closing file restriction was a problem. I just couldn’t generate the files and attach them in the same procedure.
An easy workaround is to use a very simple bat file, that will echo your generated rules into txt files that you will load in cycle.
TI Prolog Code then looks like this
# Iterating through your given list of cubes
# see Planning Sample language change process for example
# For given cube -- currentCube holds it's name
# Generating rules file
SQ = '''';
lineToWrite = '"[' | SQ | Dimension | SQ | ':' | SQ | elementFrom | SQ | '] = [' | SQ | Dimension | SQ | ':' | SQ | elementTo | SQ | '] ;"';
ExecuteCommand('echo.bat ' | lineToWrite,1);
# backing up current rules file
commandToExecute = 'copy.bat ' | currentCube | '.RUX ' | currentCube | '.RUX_BACKUP';
ExecuteCommand(commandToExecute,1);
# attaching generated rule, saving data
RuleLoadFromFile(currentCube,'test.txt');and a simple echo.bat
and copy.bat
¶
Posted 11 March 2011
† ykud §
tm1
‡
°
