Hi everybody,

We're contemplating adding some OLAP functionality to our EDB based product, and would like to exchange some ideas and possible approaches to help achieve it.

Since EDB doesn't support OLAP functionality at this time, here's the approach we'r are planning on.

1.Create a separate database from the main OLTP. (This could be in a another server machine using remote stores...)
2.In the new Database copy summarised information throughout nightly jobs to keep the "OLAP" database updated.
3.Once the information is in the "OLAP" database we can load the main data in memory tables to speed up things.
4.For the front end (the main selling point for users...) we plan to use DevExpress PivoGrid to let the user do some slice and dice with the info available. We also planning on given a web interface (instead of a Delphi one) using Devexpress ASP.NET PivoGrid. If there is better choices for OLAP components I would like to here before we renew our subscriptions...

What do you guy think of this approach? Any pitfall I should be aware of?

Thanks in advance for your comments.

Regards,
Mario

Mario

>>4.For the front end (the main selling point for users...) we plan to use DevExpress PivoGrid

I would look at the FastReports "FastCube". This is a really excellent component with lots of great features and it is much cheaper than DevEx. Also the FastReports people are nicer, supplying more "free updates" (i.e. when Delphi goes from XE6 to XE7) than DevEx.

>>What do you guy think of this approach? Any pitfall I should be aware of?

What you are proposing sounds very sensible.

I would suggest one other track for you to test: Using ODBC in EDB, and making some data-connections to Excel.

Pivot / Cube facilities in Excel are incredible, and users are very familiar with using it.

My approach:

* Put a few FastCube Pivots in the main application.
* Create an ODBC connection to a mini-database which basically just contains VIEWS from the main DB.
  (note using VIEWS means that there is no need to run nightly updates, each VIEW is just generated when the user opens the dataset).
* Train users in making ODBC links through Excel to this mini-database. This is a straightforward process, with just a few simple steps. I have a short Word document on how to do it if you are interested.

This gives users the power to adapt and change Pivots themselves, but keeps them insulated from the main database as they only touch VIEWS which they cannot update.

Adam

> * Train users in making ODBC links through Excel to this mini-database. This is a straightforward process, with just a few simple steps. I have a short Word document on how to do it if you are interested.

I certainly am! Plonk it in the binaries please.

Roy Lambert

Thank you Adam, I'm interested in your Word Document, please post it.

I will take a look at FastCube, I've heard about it before, but never bother to check it out.

Regarding the use of views, I must say I found it to be quite slow, but probably am using it the wrong way.

Regards,
Mario

Roy Lambert wrote:

Adam

> * Train users in making ODBC links through Excel to this mini-database. This is a straightforward process, with just a few simple steps. I have a short Word document on how to do it if you are interested.

I certainly am! Plonk it in the binaries please.

Roy Lambert

Mario


>Regarding the use of views, I must say I found it to be quite slow, but probably am using it the wrong way.

If your view seems slow the best approach is to pull the SQL out of it and run it as a query in EDBManager and check out the execution plan to see if it can be improved.

Roy Lambert

EDB / Excel connection PDF uploaded to the Binaries Group ...

Thanks for sharing Adam!