OLAP Cubes in Access (multidimensional dataset)

[Barbacuca]

Hello,

I'm looking for more information on how to implement OLAP cubes in Access. So far I have only found information on how to implement them in Excel, which led me to believe that Access handles multidimensional (hypercube) datasets in some other way.

Basically I need to store productivity values according to:

Contractor X Location X Code of Account

I could simply generate tables with these fields where they would have several repeated entries, however I'd rather reduce the number of records if possible.

Thanks in advance for any ideas on this subject.

/ Barbacuca

 

[gemma-the-husky]

i would have thought you need a single table with these 3 columns as the key values, together with any other attributes you need to store

you therefore potentially have

Contractor x Location x Code of Account rows in total, although in general you will probably not have every possible row in the database

--------

Searching the database is a different issue. You now need some flexible tools to help you decide what range of rows you want to retrieve

ie which contractor(s), which location(s) etc, and which attributes.

---------

The problem comes with handling very large datasets

searches of this type are hard to index (if at all). Therefore all data must be retrieved by examining every row - so if you have millions of rows, every row must be examined to see if it meets your requirements - which is time consuming.

--------
now i am not sure exactly how a true data-warehouse is described - perhaps there is data pre-processing to build up summary details of information you are likely to request.
Perhaps it generally runs on more powerful machines.

------
Anyone know - perhaps these thought are completely misguided?

 

[Barbacuca]

Thanks for your reply, Dave.

The problem is, as you said, the immense size of the dataset. The database will have hundreds of thousands of records, times 10-15 different contractors operating in 1-8 possible different locations.

I have started this database design thinking of "flat files" with 3 fields as key values. But this is soon getting huge and I'm afraid that, given to network limitations, reporting will turn into a rather slooooow affair...

Cheers,
Barbacuca