[AccessD] OLAP Cubes

Keith Williamson keith at williamson5.name
Tue May 22 21:23:14 CDT 2007


Michael,

Thanks for your help.  I'll be working with this soon.

Regards,

Keith Williamson

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Michael Maddison
Sent: Tuesday, May 22, 2007 7:50 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] OLAP Cubes

Keith,


Michael,

Couldn't you just schedule your Access view to run nightly?  Seems like
this will work well, for my needs.

>>>Not sure what you mean.  
I could schedule MS Analysis Server (AS) to run nightly but that wont
help because the data is updated monthly ;-).

Newbie question though....does the cube data reside within the Excel
spreadsheet (sort of hidden)....or does it warehouse the data wherever
you want it?
>>>Excel is just a FE for the OLAP cubes.  You connect to it like any
other data source, except it has its own wizard IIRC.
The data is then presented using the crosstab interface.  There are
other FE available or you can query the data directly
using MDX.  MDX is the MS OLAP query language, warning...  its not very
similar to SQL.

A cube has 3 components.  A Fact table,  Dimensions and Measures.
Your main view will be the Fact table.  From the Fact table you will
select your Measures and Dimensions.

Dimensions are the key data fields you want to use to 'slice' your cube.
Examples are Date/Time, geographical area, names, products etc.

Measures are the values you want to slice.
Revenue, costs etc.

It's the dimensions and measures that AS preprocess so that when viewed
in FE the results are already stored.
It is logical that the more measures and dimensions you have the more
work AS has to do.
Take care as the growth can be exponential if you screw up a dimension
;-)

My tip is to start out small, few rows, couple of dimensions, a couple
of measures.  You will work out
the basics pretty quickly.

HTH

Michael M


Thanks,

Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com

RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland
21231-3305

410-537-6098 direct | 410-276-4182 fax | www.rtkl.com


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Michael
Maddison
Sent: Tuesday, May 22, 2007 12:26 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] OLAP Cubes

 Keith,

I believe you would do the join in Access so you should not have major
dramas. 
Create a view based on your separate db's, use the view as the fact
table.

In my experience the more complex the view the longer it takes to
process the cube.  However once its processed then you are gold.  I have
a very complex (fugly) query, about 5mill rows in the fact table, takes
a couple of hours to process once a month.  Looked at again just the
other day, gawd its ugly, still gives the correct results so I gave
up...

cheers


Michael M

Do you know if you can join separate databases (SQL and Access) in the
cube datastream?  Is there really any limitation on how complex the sql
statement is?  My financial statements are based on a couple complex,
nested queries (joining both my access tables and the application sql
tables).

Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com

RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland
21231-3305

410-537-6098 direct | 410-276-4182 fax | www.rtkl.com


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Monday, May 21, 2007 4:50 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] OLAP Cubes

They ARE all they appear to be, but you must understand one thing: they
must be created beforehand. That can take a while. On the other hand,
once they have been created, updating them with new data is relatively
trivial.
They
pre-compute all their results, which is why everything is instantaneous,
but for sure they WOW the managerial types.

I'm no expert, but my rule of thumb so far is, create a dimension for
every foreign key, plus the date dimension of course. The latter
automatically divides the data into units such as year, quarter, month
and week.

Have fun!
Arthur


On 5/21/07, Keith Williamson <Kwilliamson at rtkl.com> wrote:
>
> Anybody do anything with OLAP Cubes?  I was at a conference this past 
> weekend, and saw a demonstration of one.  WOW!!!  Very fast, and 
> appeared easy to use.  I use an Access database to store static 
> month-end financial summaries, and additional information, to enable
us
> to print our month-end reports (linked to our SQL-based application.) 
> If these OLAP cubes are all they appear to be.....it will greatly 
> decrease my processing speed for the financial statements.
>
> Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com
>
> RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland
> 21231-3305
>
> 410-537-6098 direct | 410-276-4182 fax | www.rtkl.com
>
>
------------------------------------------------------------------------
--------
> The information contained in this communication is confidential, may
be
> privileged and is intended for the exclusive use of the above named 
> addressee(s). If you are not the intended recipient(s), you are
expressly
> prohibited from copying, distributing, disseminating, or in any other
way
> using any of the information contained within this communication. If
you
> have received this communication in error, please contact the sender
by
> telephone at (410) 537-6000 or by response via e-mail and permanently
delete
> the original email and any copies.
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list