This is one of the most important lessons in EPM. If you get this wrong, your application won't just be slow—it will literally stop working. Let’s break down Dense vs. Sparse as if we were sitting in a classroom.
1. The Definitions: Detailed Explanation
Dense Dimensions
A dimension is Dense when there is a high probability that data exists for every combination of its members.
* The Logic: Essbase expects data to be "crowded" here.
* The Storage: Essbase pre-allocates a physical "slot" in the data block for every single member of a Dense dimension. Even if the cell is empty (contains #Missing), it still occupies space (8 bytes).
Sparse Dimensions
A dimension is Sparse when data is scattered or "rare" across the members.
* The Logic: Essbase expects a lot of "empty space."
* The Storage: Essbase does not allocate space unless data is actually loaded. It only creates a "Block" when a Sparse combination has a value.
2. The "Classroom & Lockers" Example
Imagine you are at a school with 1,000 students.
The Dense Example (The Class Roll Call)
Think of an Attendance Sheet for a single class of 30 students. Every day, the teacher has a list of all 30 names. Even if 5 students are absent, their names are still on the paper. The "space" for their attendance is already printed because we expect almost everyone to be there.
The Sparse Example (The School Lockers)
Think of the 1,000 lockers in the hallway. The school only buys a physical lock and assigns a locker number when a student actually shows up and asks for one. If only 200 students use lockers, the school doesn't waste energy managing 1,000 open doors because most students might not need one.
3. How it is useful while building an application
When you are an Architect building an EPM application (like FCCS or Planning), this concept is your Performance Steering Wheel:
* Memory Management: It helps you control the Block Size. If your block is too big (too many Dense dimensions), the server runs out of RAM.
* Calculation Speed: Essbase calculates Dense dimensions inside the RAM (extremely fast). It calculates Sparse dimensions by moving blocks in and out of the disk (slower).
* Disk Space: Proper Sparse settings ensure your database doesn't take up 100GB of space for only 1GB of actual data.
4. Where do we use these concepts?
You will apply this every time you go into the Dimension Editor or Outline:
* Period/Time: Almost always Dense.
* Accounts: Almost always Dense.
* Product/Customer: Almost always Sparse.
* Entity/Department: Usually Sparse.
5. Extra Information: The "Architect's Secret" (The Block Size)
To master this, you must understand the 8-Byte Rule. Every single cell in a Dense block takes 8 bytes.
The Survival Calculation:
Imagine you have 3 Dense Dimensions:
- Period: 12 members
- Account: 1,000 members
- View: 3 members
$$Total Cells = 12 imes 1,000 imes 3 = 36,000 cells$$
$$Block Size = 36,000 imes 8 bytes = 288,000 bytes (approx 281 KB)$$
**Tutor's Pro-Tip:**
* Perfect Block Size: 64 KB to 256 KB.
* Danger Zone: If your block size is 5,000 KB (5 MB) or more, your application will "choke" during consolidation.