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