Home | Ciber
Knowledge page of Ciber Netherlands

Archive for November, 2008

Effective Partitioning in SQL Server 2005

Partitioning can help reduce maintenance effort, provide high availability and increase performance for large database tables. Whether or not partitioning will provide the desired benefits can only be determined by running tests, but in general partitioning should be implemented when a table gets so large that maintenance tasks take an unreasonable amount of time. Usually, this aspect outweighs the performance benefit side of partitioning and also will occur sooner than a performance bottleneck, which in many cases can also be solved with some well-designed indexes.

This whitepaper discusses the factors that influence the effectiveness of partitioning solutions and examines to what extent SELECT queries can reap performance benefits from partitioning, as opposed to the well known benefits to INSERT, UPDATE and DELETE statements, by running a number of performance tests in an actual real-life customer environment.

Read more

No comments

OBIEE Federated Query’s / Fragmented Query’s

One of the power features of OBIEE is the usage of federated or fragmented query’s. Being able to source different database at the same time can save you a lot of time because you don’t have to reengineer your whole ETL every time a new system gets introduced.

Still it wise to put some effort in the way you map the tables in the repository, especially if you want to minimize the query response time.

Let’s go back to out basic model:

 

Read more

No comments

OBIEE Understanding Outerjoins Part 2

In a previous article I showed the basics of working with OBIEE Outerjoins: (http://knowledge.ciber.nl/weblog/?p=152).

image

This works fine until you add a filter :

image

Read more

2 comments

OBIEE Understanding Outerjoins Part 1

First of all in an "ideal" DWH you wouldn’t have a need for outerjoins, but sometimes you need to make data visible which you don’t have.

Outerjoins in OBIEE can be a bit tricky and don’t always give you the result you would expect. In this article I will get trough the basics, in a next trough some pittfalls….

Creating an outerjoin

Read more

1 comment

OBIEE OBI - Forum Live

Ze Bloggen en Loggen… Ontmoet ze nu op OBI FORUM LIVE

CIBER, InCore Solutions en Scamander zijn toonaangevende bedrijven op het gebied van Oracle’s BI Suite. John Minkjan (CIBER), Remco van Dam (InCore Solutions), Daan Bakboord (Scamander) en Gerard Braat (Oracle) zijn Nederland’s bekendste en beste (b)loggers op dit vlak. Op de nationale en internationale fora zijn ze bekende personen en leveren ze een zeer belangrijke bijdrage aan de kennisdeling.
Nú heeft u de mogelijkheid om deze 4 belangrijkste spelers persoonlijk te ontmoeten en met ze te ‘sparren’. Neem gewoon uw eigen vragen mee en ontdek de geheimen van OBI!
Op 14 januari 2009 is de eerste editie van OBI Forum Live. Een initiatief van CIBER, InCore Solutions en Scamander, in samenwerking met Oracle Nederland. OBI Forum Live zal plaatsvinden bij Oracle Nederland in De Meern.

Schrijf je nu in op http://www.obi-forumlive.nl/

No comments

OBIEE Cache Settings Presentation Services

The cache is accessed when users make requests in Answers. This is not the same cache that is accessed by the Oracle BI Server. You can change the internal defaults by modifying the Oracle BI Presentation Services configuration file (instanceconfig.xml) to add the cache entries.

First of all try to have your presentation Cache on the fastest disk available (consider a "RAM drive").

Specifying the Maximum Number of Oracle BI Presentation Services Open Record Sets

From the guide:
You can override the maximum number of open record sets that Oracle BI Presentation Services keeps open at any one time. The internal default is 10. The minimum value is 3. For systems under
significant loads, you can increase this value to 500 or 1000.
The following entry is an example:
<CacheMaxEntries>100</CacheMaxEntries>

Read more

1 comment

OBIEE Aggregate Persistence Wizard

If you are doing POC on OBIEE or have to do some RAD work, you usually don’t have access to ETL of the database to prep the aggregate tables. If you have access to an DB-schema with some create and drop table rights you can use the OBIEE Aggregate Persistence Wizard to do it Q&D / RAP style.

More info on using aggregate tables you can find here: http://knowledge.ciber.nl/weblog/?p=148

The examples in this article are based on this simplified business model;

 image

Read more

1 comment

OBIEE making it "aggregate aware"

In a previous  posting (http://knowledge.ciber.nl/weblog/?p=147) I showed a way to split huge dimensions to bring down the response time of OBIEE. Can we bring down the response time even more? Of course we can, let’s make OBIEE aggregate aware.

The aggregate tables

First we have to change our ETL so that we get aggregate table(s) in the form:

  • ag_fact_table_level_1; dim_level_1, fact_aggregate_level_1
  • ag_fact_table_level_2; dim_level_2, fact_aggregate_level_2
  • ag_fact_table_level_3; dim_level_3, fact_aggregate_level_3
  • ag_fact_table_level_4; dim_level_4, fact_aggregate_level_4

 

Read more

1 comment

OBIEE Huge dimensions? Let’s split them!

OBIEE is (like most other tools) based on the Kimball methodology of representing date in a star model, using dimension and fact tables. The problem with dimension tables is that if they have several levels they can easily become huge and therefor take a long time to load. And if there is one users don’t want is waiting…..

An example: a dimension with 5 levels and 25 categories on each level can give you 25^5 = 9.765.625 possibilities. An entry on level 1 would generated a "select distinct level_1 from dim_table". No mater how good your indexing is, a select distinct on almost 10 million rows is always slower then a select distinct on 25 rows.

Read more

2 comments