Database Reference
In-Depth Information
data and dimension SQL Load rules or via Essbase Studio or its predecessor, Essbase
Integration Services. often, the atomic level of the data is simply too low and the effort
to aggregate the data before it gets to Essbase is beyond an Essbase implementer's capa-
bilities. Another concern is that the definitions that made sense when the warehouse
was defined are simply not correct for your Essbase application, or worse yet, the data
you need is not there at all. This is where a data mart can be handy as a source of preag-
gregated fact data, calculated measures, and alternate hierarchies.
There are dangers inherent in the data mart approach around resources, data qual-
ity, and internal political considerations. If the data mart is more than a simplified
and focused access point to the data warehouse, the effort to extract, transform, and
load data from source systems into the data mart can be just as complex as a true
data warehouse, albeit, at a smaller scale. Can your project timeline support a 30-day
data mart build before you start pointing Essbase at the data? Data warehouses are
enterprise-wide constructs that have had exhaustive review by It and the business.
Data marts seldom undergo this same scrutiny and review. how are you going to guar-
antee that the Essbase data mart is as good? Who is going to do that? how will it it
into the timeline? Lastly, It departments get all of the blame when systems report bad
data. The enterprise data warehouse was designed to mitigate that situation and, there-
fore, will cast a jaundiced eye over additional data sources. you, your project sponsor,
and the SmEs are going to have to work in concert to convince the business that this
additional source of data is vital. Armed with a truly valid reason, you should get the
buy-in needed from It.
11.3.4.3 Source Systems
Sometimes all of the skillfully crafted arguments in support
of the need for a data warehouse (or data mart) will not suffice. For whatever reason, be
it organizational, technological, or schedule-based, neither a data warehouse nor data
mart exists for your Essbase project. The source system, whether it is an ErP (Enterprise
resource Planning) system, product sales data system, or inventory control system, will
be the direct source of your data and metadata. This is not an impossible situation;
after all, a data warehouse or mart would have to go to the source systems. There are,
however, numerous challenges that now arise:
• how do I get data out of the system? SQL? Flat files?
• What does the metadata look like? Can I transform it to make it work in Essbase?
• how do I validate the data after a custom multistep extraction and transforma-
tion process?
These are just a sample of what must be carefully considered.
The procedure for getting data out of a source system varies based on product age and
platform. generally, older mainframe and minisystems do not have a relational interface
to the outside world and extracts will be in the form of flat files. you or your It department
will need to build EtL (extract, transfer, and load) processes to get that data from what-
ever the source computer is to your Essbase server and then perform whatever additional
transformations are required before you load the data into Essbase. Although you may not
have thought that the Essbase administrator job description included data integration,
you are about to find out how flexible your skill set really is. SQL is your friend in this case
because an intermediate SQL data store is a good place to park data. It departments are
familiar with automating loading file extracts to relational data stores, and once loaded,
custom SQL queries will allow you to transform the data as needed.
