Applied Dimensionality

What’s new Fix list

Nothing revolutionary, performance improvements, fighting with Java-based Rich Client memory footprint and performance. Publishing enhancements seem interesting.

Since BI upgrade is required as well, I’d wait for first couple of BI service packs before trying this one out.

Another point worth clearing out: I really think TM1 is not a very good choice for OLAP reporting engine. On a decent data volumes, of course, millions of rows will be fast as lightning ;) Let me explain why.

I’m a fan of testing in each DWH project, because it allows to:

• be sure that at least tested parts work * change logic w\o retesting all results
• Receiving “All OK” in the morning means that there’s time for a cup of coffee )

A list of approaches I use for testing ETL-procedures:

‘Water in a Sieve’

Checking whether we could carry all required data into DWH without ‘spilling’ it out.

A check-list for using Microsoft SQL Server as a reporting database. This is a ‘be aware’ list for myself in first place, since I forget some details as time passes.

Just a bullet list of things that separate Cognos Transformer from being a capable OLAP-tool in modern market. Neither of these problems will be solved, I think, and this will lead to Transfomer’s death in near future (about 5 years, imho, given huge userbase). That’s a pity, since it’s a nice tool, absolutely brilliant 10 years ago. But as data sizes grow, Transformer just cannot scale. Hyperion did a nice job of inventing ASO option when it faced volume challenges, Cognos decided all was good enough.

Extracting from a discussion from sql.ru forum:

PowerPlay performs very bad when it comes to large and sparse (with big number of nulls and zeros) cross-tabs.

Modern OLAP engines are specially optimized for NonEmpty MDX clause, which is default report generation option.

Just a link to developerWorks thread where Phil_W gives an excellent explanation. Kudos to Phil, all his post are truly brilliant: concise, informative and insightful. I use ProcessExplorer to find PIDs too.

Oracle BI publisher works awfully bad with Answers type of datasource in at least two points:

1. column names are created by answers formulas instead of column names, so if you have long formulae, it’ll be shortened to some 128 chars. And if first 128 chars of formulas are identical, you’ll get only one of them in publisher and if columns return null in answers they will not be available at all. We ended up using logical SQL generated in answers as a source for publisher reports, which works like charm up until you start changing repository or changing underlying data you’re using for filters.  Repository changes can be handled by adding aliases to columns being renamed
2. data changes are way more difficult to tackle For example when you change city name from ‘NY’ to ‘New York’ in geography dimension (this should be solved by BI 11 id\description) you have to change all formulas and filters using this value.

So I wrote a simple Python3 script, doing exactly this: replacing all needed strings in publisher xdo files. If there’s any need for it, email me, I’ll send it over.

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.

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.