Database Reference
In-Depth Information
keep in mind the bullets above reflect guidelines. We have all built BSo databases
that go against the rules and they work, kind of. The point is that for read/write data-
bases, where performance is critical, know the pitfalls. Do not handicap yourself if you
can avoid it.
4.4.2.1 Databases Having More Than 10 Real (Nonattribute) Dimensions not only are
databases having 10 or more dimensions slow, they are usually cumbersome for the
end user. BSo databases are usually designed with 10 or more dimensions for one of
two reasons. The most common reason is dimensional irrelevance. This occurs when a
database is used for multiple purposes. For example, sales and marketing and balance
sheet functionality should not be combined into a single database. Sales and market-
ing applications typically have large product and customer dimensions that have no
significance for a balance sheet. Likewise, the hundreds or even thousands of balance
sheet accounts are irrelevant for sales and marketing. The better option is to create
separate databases.
The second contributor to an overly high dimension count is dimensions that should
be combined, but are left to stand alone. This situation is most often seen with year and
month, or state, city, and zip code. In the relational world, it is common to see these as
separate dimensions. Essbase works best for both performance and usability when city,
state, and zip code are combined into a single hierarchy. If you absolutely must include
more than 10 dimensions, the majority should have few members and you should pay
close attention to the dimension order.
4.4.2.2 Dynamic or Dynamic Calc and Store Sparse Dimensions This one seems to be
pretty obvious, but I included it because I recently encountered a client's database where
almost every parent of the sparse dimensions was dynamic calc and store. Sure, there
were no batch calculation time, but retrievals were painful. to make matters worse, there
were no dynamically calculated members in the dense block because Essbase could not
lock enough blocks in 32 bit. This database was an extreme case. more commonly, you
will encounter only three or four members in a sparse dimension that are candidates
for the dynamic tag. Even still, be careful here and watch the order of calculation. often
times, your quarter time period results will not be what you expect.
4.4.2.3 Dense Blocks without Any Dynamic Calculations This is another tip that seems
pretty obvious, but never fails to surprise me as often I see it: nonaggregating dimen-
sions in the dense block. Dynamic calculations in the dense block are the best way to
reduce the stored data footprint. In plain English, the PAg file will be smaller. Dynamic
calculations also take advantage of the modern multiprocessor environment, so why
waste a perfectly good dense block by having dimensions that do not need calculations?
It should be noted that the official oracle Essbase documentation warns that having too
many dynamic calculations can have a negative impact on performance. Frankly, I have
never seen a case where in memory calculations are slower than data retrieval.
4.4.2.4 Two or More Large, Flat Sparse Dimensions Flat dimensions are those that
have few parents with many children. BSo will issue warnings when the number of
children exceeds 100. Do not worry too much about 100 children, but when there are
thousands, expect trouble. The best solution is to create additional levels. Let us consider
an employee budgeting database as an example. The employee dimension has 15,000
Search WWH ::




Custom Search