Before we get started I want to let you know that I am making some assumptions. I know, I know... and yes, of course you are right, but assumptions are a necessary kind of evil. So here they are:
- You have heard of Essbase
- You have some understanding of information systems
- You need to use Essbase and care about doing it right
- You understand the difference between data and metadata
A single cube (Essbase database) architecture is typically used when you need to report on data from a single source or a group of closely related sources. They are usually purpose-built and address specific reporting or analytical requirements. (GL reporting, Expense reporting, Branch reporting, Revenue reporting, Inventory analysis, Yield analysis, <insert your own reason here>, Etc.) This architecture is also a kind of building block for larger EPM solutions.
The diagram above describes a Single Cube Architecture with a fairly simple "Source to Target" data flow. I like to read these from left to right but read it how you like as long as you understand the pattern. The leftmost column of the diagram deals with sources. The rightmost column looks at the flow of data and metadata into a cube and out to user interfaces. A source is any (information system, person, or process) that generates data or metadata that needs to be loaded into the Essbase cube. Across the entire solution (blue bar at the bottom) we need to address security, administration and automation.
How do we know what to load to Essbase?
NB: Everything in this diagram is subject to clarification based on the requirements for your project. It is also a recommended starting point upon which we build other, more complex, EPM architectures.
Throughout the diagram I have provided several on-page references numbered. Here are my thoughts on these components:
- Data: This is the data coming into Essbase and it could come from any source system that can provide data as a text file or relational database. Its up to us to decide how that data is organized within the dimensions of our cube by mapping the data to members in each dimension.
- Metadata: The metadata in our application (the data that describes our data) is organized into collections of members called dimensions and these need to come from somewhere. Ideally we should source our dimensions entirely from the source system but this is not always feasible. Automate what you can and manually map or maintain everything else.
- Staging: Whether you are manually building or extracting text files of some kind or pushing data to a relational database you will need some form of staging area for all the resources you plan to load into your Essbase cube. My recommendation is that you look to relational database solutions first. Note: If you are not comfortable with relational databases yet, you can always start with files and graduate to relational databases at a later date.
- Essbase: At this time, Essbase supports three different modes of operation. Aggregate Storage Option (ASO) is a good default choice for rapid aggregation of data. Block Storage Option (BSO) is good for complex formulas and calculations that run in stages or batch. The third is a hybrid of the BSO calculations and ASO aggregation. Essbase RUL files are typically used to import data and metadata into Essbase and they support text files and relational database connections.
- User Tools: There are many end-user applications that can speak with Essbase. These are just a few examples and I noted MDX here just to remind you that you can query Essbase with MDX although it is not technically a standalone client application for Essbase.
- Admin Tools: Shared Services and Essbase Administration Services (EAS) are the most basic components for building a classic Essbase cube. Other tools such as EPMA can optionally be deployed to manage the metadata in a cube but this is not necessary or even recommended for a single cube deployment.
This is a basic architecture for a single Essbase cube where data and metadata are loaded to support analysis and reporting from a single source or group of closely related sources. Hopefully the diagram helps you understand how to organize your sources and the flow of information into Essbase. Keep in mind that you have options when it comes to staging information for import into Essbase, namely files or relational databases and that Essbase load rules can import from either. You will need Shared Services and Essbase Administration Services when you work with your cube and you can optionally leverage EPMA to deploy your cube if you need to.
I hope you found this overview to be useful.