Greetings,

We are in the process of purchasing a reporting tool. The best
candidate at the moment is MicroStrategy. Other tools evaluated were
BusinessObjects, and Cognos(reportnet+powerplay). We went through all
pros and cons.

However there is an important constraint:

The tool is going to be used in financial applications, and we already
have Hyperion Reports, Essbase DB at hand. Hyperion is so far used for
planning, budgeting, whatever you name it.

The major problem is we cannot integrate the actual figures on our
RDBMS system (the warehouse) and the budget from Essbase within RDBMS.
However it looks easier (and partially implemented) to integrate them
within Essbase. Basically it is a matter of choising ETL direction.
However pushing data from Essbase into an star-schema on an RDBMS
seems nightmare.

We are very confused. 1) Should we forget about buying a ROLAP tool
(which is we think requried to report over a 2TB warehouse), and
extend to some acquired Hyperion products like Brio and somehow how
hope that we can consolidate budget and actual on these front ends, or
2) forget about Essbase and build/purchase budgeting software that
works on an RDBMS or 3) use MicroStrategy and Hyperion together and
dream of putting incompatible front-ends together in a portal
or 4) Do budgeting on Essbase feedback to RDBMS and report on
MicroStrategy

Thanks

I think you should do 5 Report actuals via Essbase ;-)

I would be very suprised to learn that you can't show it all via
Essbase, although you don't seem to list that in your possible
solutions!

Any decent planning tool is going to need to allow you to load historic
(last year's ?) actuals as a basis for your plan.
You also need to be able to load current (this year's) actuals to allow
you to compare actual to budget and also to reforecast.
If you are not going to reforecast, then your plan is a waste of time
and money.

As Essbase can presumably load the actuals as above, why not use that
as the means of both reporting performance against plan and
reforecasting.
I work mostly with TM1 and this is the approach that I would normally
suggest with that product. I can see no reason why it should not also
work in Essbase, but I tell me if I am wrong here.

(I can see a potential issue that you would have to provide Essbase
access to all those who want to see actual/plan comparions. This may
have a cost implication vs your own data warehouse. Does Essbase have
cheapish read-only licences these days?

Another issue is if your actuals need to be presented and drilled down
to at a more detailed level than the plan, as this may cause
scaleability issues for Essbase)

If a user needs to squirt data back into an RDBMS then text file export
normally does the job. Perhaps your situation is a little more complex?

Anyway, I can't see a lot of benefit to buying an extra tool simply to
pull Essbase plans and rdbms actuals together.

Regards

John



Cagdas Ozgenc wrote:



--
John Hobson
The Planning Factory Ltd
www.planfact.co.uk

"John Hobson" <jhobson.nospam (AT) planfact (DOT) co.uk> wrote

We are already doing that (I stated in my previous post that it is
somewhat implemented). The problem is datawarehouse is big, and not
everything is about comparing budget and actuals. Those reports are
working fairly (not as effectively but still serving purpose). The
rest of the reporting should directly access RDBMS, since I do not
wish to ETL everything into Essbase (as we had already spent many
months of ETL from OLTP to warehouse). That's why I asked whether we
should stick to newly acquired tools of Hyperion (Brio), and hope that
at the front end we can both get data from Essbase cube, and RDBMS and
put those on the same report.

I think there is a great need in the market to be able to get data
from MOLAP data sources and RDBMSs and put them together into the same
report. SQL Server 2005 Yukon is somewhat trying to achieve that goal
for example.

We had purchased enough licenses long ago, before I was employed in
this company. So that's not an issue. The major problem is budgeting
was maintained from a different department, and the warehouse is
maintained by IT. They seem to have different data models, and they
have to be consolidated somewhere.

Text file is useless. In a multidimensional model you have the data
plus the dimensions/hierarchies. Dimensions in the RDBMS have keys,
however Essbase keeps only the descriptions (that's why you must have
unique naming in Essbase, which many people think causes problems). So
it seems at the moment impossible to map the dimension keys to the
data exported from Essbase (and use it as a fact table).

Hope I could make myself clear.

Thanks for reading.

Cagdas

Have you considered moving this to SQL & Analysis Services, and if not, why
not? It is possible at present to have both MOLAP data in the same Excel
file, but not on the same sheet. Would this work for you? If not, this could
be implemented easily as the functionality is there, with only a few minor
changes required. There are a few options and permeations to consider here.
Also, you could be capturing your data through Excel as well and be storing
this in the desirable format required, and in the required place. A lot of
users have upscaled their OLAP solutions to the MSAS platform.

If you would like to see this in action, with advanced Analysis Services
browsing and modeling, or wish to discuss some of the options, please feel
free to contact me.

Howard.Taylor@ domain below
www.o2olap.com


"Cagdas Ozgenc" <cagdasozgenc (AT) hotmail (DOT) com> wrote

You already have your datawarehouse (what is it? SQL Server?)
You already have your OLAP planning platform (Essbase)

Move the actuals at the granularity of planning into Essbase & do the
variance analysis there.
Use a reporting tool for the rest of the actuals that are at a finer
level of granularity. They should not need to have planning figures
along side them if they are different granularities.

If Essbase cannot cope with the required actuals then you are probably
planning at too lower level.

Regards,

John Keeley

www.johnkeeley.com

Interesting thread.

Cagdas said : "I think there is a great need in the market to be able to
get data from MOLAP data sources and RDBMSs and put them together into
the same report. "

Oracle agrees with this view and it is one of the many reasons that
Oracle integrated Express Server with the Oracle Database back in
version 9i release 2, (& has greatly enhanced it in version 10g btw).

Oracle has gone much further than just allowing relational and
multidimensional data to be put in the same report. It has put it in
the same database. The Oracle database. So MOLAP data is as secure as
any other data in Oracle, and can leverage the scalability and
availability features that have made Oracle DB the outright leader for
data warehousing, according to analysts like Gartner, IDC etc.

With the Oracle Database, 'MOLAP' as well as relational data is stored
in the same Oracle Database, and can be accessed with plain ordinary
SQL. Plain ordinary SQL which can leverage the power of the
multidimensional engine that is part of Oracle too.

So, multidimensional data and calculation power is accessible to a very
wide range of query and reporting tools and applications that previously
only worked with relational data. (or had to use a different
propietary connection to the OLAP data from an entirely seperate
database, and attempt to try and match up the data in a spreadsheet or
some other end user tool). Meaning that OLAP, for Oracle customers at
least, now becomes much more accessible and useful not only for discreet
OLAP analysis, but as part of a much wider performance and functionality
upgrade for data warehousing and general BI.

With his company's current choice of OLAP technology ofcourse, Cagdas
has several problems. One of which is this issue that seperate
standalone MOLAP servers like Essbase (and Microsoft AS, and indeed
Oracle Express Server), have proprietary APIs and are not accessible via
standard SQL like relational databases. There are all kinds of
resulting ETL-related issues too (like the 'keys' issue mentioned
below), which add to the cost, complexity, and inflexibility of
deploying a solution in this way.

Cagdas - I don't remember if you said what DBMS your data warehouse is
running in. If it is Oracle, then the move to Oracle OLAP is likely to
be especially attractive. If it is not Oracle, perhaps now is the time
for your IT colleagues to be looking at a switch ;-)

Feel free to contact me off-line if you need help in locating your local
Oracle Business Intelligence and Data Warehousing rep. (Just replace the
'dots' in the reply address). He or she will be pleased to give you
alot more info about Oracle OLAP in the Database, and some of the BI
tools that you may find useful when accessing it.

Kevin (@ Oracle)


Cagdas Ozgenc wrote:

If you have SQL Server you should be able to link to MOLAP and select data
transparently without having to export data into RDBMS. Using stored
procedures you will have complete control over data exposed to particular
user with minimal performance impact.

Miron.


"Cagdas Ozgenc" <cagdasozgenc (AT) hotmail (DOT) com> wrote