Scribd Logo
Upload

Welcome to Scribd!

  • 188 views

    Essbase Block Formation: Dimension Stored Members Storage Type

    Uploaded by

    Vikrant Singh
    Essbase stores data in cubes or OLAP databases using a block storage model. It creates data blocks for unique combinations of sparse dimension members that have at least one data value. It indexes each data block and stores the blocks and indexes in .pag and .ind files respectively. By strategically marking dimensions as sparse or dense, Essbase can create optimally sized blocks to improve performance and reduce storage requirements.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd

    Essbase Block Formation: Dimension Stored Members Storage Type

    Uploaded by

    Vikrant Singh
    188 views5 pages
    Essbase stores data in cubes or OLAP databases using a block storage model. It creates data blocks for unique combinations of sparse dimension members that have at least one data value. It indexes each data block and stores the blocks and indexes in .pag and .ind files respectively. By strategically marking dimensions as sparse or dense, Essbase can create optimally sized blocks to improve performance and reduce storage requirements.

    Original Title

    Essbase Block Formation

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Essbase stores data in cubes or OLAP databases using a block storage model. It creates data blocks for unique combinations of sparse dimension members that have at least one data value. It indexes each data block and stores the blocks and indexes in .pag and .ind files respectively. By strategically marking dimensions as sparse or dense, Essbase can create optimally sized blocks to improve performance and reduce storage requirements.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    188 views5 pages

    Essbase Block Formation: Dimension Stored Members Storage Type

    Uploaded by

    Vikrant Singh
    Essbase stores data in cubes or OLAP databases using a block storage model. It creates data blocks for unique combinations of sparse dimension members that have at least one data value. It indexes each data block and stores the blocks and indexes in .pag and .ind files respectively. By strategically marking dimensions as sparse or dense, Essbase can create optimally sized blocks to improve performance and reduce storage requirements.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 5

    ESSBASE BLOCK FORMATION

    Oracle Essbase (Extended Spreadsheet Database) is a multidimensional database for building analytic applications. Essbase stores data in a "cube" or Online Analytical Processing (OLAP) database. Before moving on to block formation, we should be aware of the Data cells, Data Blocks, Sparse- Dense Dimension, & .Pag and .Ind Concepts. Data Cells: In Block Storage database, the Intersection of one member from one dimension with one member from each of the other dimensions represents a data value, and each data value is stored in a single cell in the database occupying 8 Bytes of disk storage. For Example: Consider for a database, we have 5 dimension as

    DIMENSION
    Accounts Time Year Scenario Product Customer 100 17 5 2 500 500

    STORED MEMBERS+
    Dense Dense Dense Sparse Sparse Sparse

    STORAGE TYPE

    A data value or cell (100) is represented as Current Year -> JAN -> Actual -> Product A -> Customer X -> Units : 100 (means for Product A units sold to customer X in month of JAN of Current Year for Actual Scenario is 100) Sparse & Dense Dimension: For the formation of block in Essbase, first we need the dimensions to be tagged as either Dense or Sparse. As in most of the data sets, the data is not uniformly distributed and the data does not exist for the majority of member combinations. Now the question arises when we mark dimension as sparse and dense. A sparse dimension is a dimension with a low percentage of available data positions filled. A dense dimension is a dimension with a high probability that one or more data points is occupied in every combination of dimensions Based on this concept if you match the patterns correctly when you select your dense & sparse dimensions, you can store data in a reasonable number of fairly dense blocks, rather than in an excessive number of highly sparse data blocks. This enables the database to cope with data, speed data retrieval, minimizes memory & disk requirements.

    + Stored members are members with Store Data, Dynamic Calc and Store, or Never Share data storage properties

    Data Blocks: In Block storage database, analytic service uses two types of internal structure to store & access data : Data Blocks & Index System. Essbase creates a data block for each unique combination of sparse standard dimension members (providing that at least one data value exists for the sparse dimension member combination). The data block represents all the dense dimension members for its combination of sparse dimension members. Essbase creates an index entry for each data block. The index represents the combinations of sparse standard dimension members. It contains an entry for each unique combination of sparse standard dimension members for which at least one data value exists.

    BLOCK 1 2

    SCENARIO Actuals Actuals

    PRODUCT Product A Product A

    CUSTOMER Customer X Customer Y

    Index of Data Blocks

    Scenario->Product->Customer

    Now lets consider the following three scenarios, how blocks are created in ESSBASE

    CASE 1: ALL SPARSE DIMENSION


    If all members are tagged as sparse dimension i.e. highly scattered data, then for each data value, a data block (consist of single data cells containing single data values) is created & for each data block separate index entries are made in an Index file (.ind). Resulting in high memory usage and more search time for each specific block.

    Single cell blocks

    Large Index Entry

    CASE 2 : ALL DENSE DIMENSION


    If all dimensions are tagged as dense dimension i.e. highly populated data, Essbase create large data block with only one index entry.

    Huge Data Block

    Single Index Entry

    CASE : 3 COMBINATION OF SPARSE & DENSE DIMENSION


    Mix of Sparse and Dense dimension means equivalent combinations of Data blocks & Index Entries. Essbase create data blocks that are sufficient enough to be fit in memory and relative Index entry such that minimal resources are required.

    Dense Blocks

    Index Entry

    Taking above example into consideration i.e. having 6 dimensions in our hierarchy, we have data block for each possible combination of dense dimension members i.e. Accounts, Time & Year as

    Data Cell

    Each data block in BSO has the same number of cells, and after Analytic Services create a data block, all cells of the data block exist, regardless of whether they contain data. These Data blocks are stored in PAG file (.pag) which can grow upto 2 GB, if filled than it creates additional sequential PAG file. Essbase creates data blocks for combinations of members in the sparse standard dimensions (providing that at least one data value exists for the member combination). The sparse members are Scenario, Product & Customers. As these sparse dimensions does not have all value for every combination, than Essbase does not create blocks at that intersection. Essbase creates an index entry for each data block in an IND(.ind) file which is located in same directory as PAG file & can have max size of 2 GB. When an index file reaches the maximum size i.e. 2GB, Analytic services create another sequential IND file. Loading the data value 100 to the member intersection Current Year -> JAN -> Actual -> Product A -> Customer X -> Units create a data block that is indexed as Actual -> Product A -> Customer X.

    Number of Potential data cells is multiplication of stored members of one dimension with all others i.e. =100*17*5*2*500*500 = 4250000000 cells

    Number of cells in a data block is multiplication of stored member of one dense dimension with stored members of other dense dimensions i.e. =100*17*5 = 8500 cells

    Number of Potential data blocks for the database is multiplication of stored members of one sparse dimension with stored members of other sparse dimension i.e. =2*500*500 = 500000

    You might also like