----- Pmxgs wrote: -----
Hi everyone!
Can someone tell me if it's possible to create olap cubes in Access.
If not, where can i create cubes, excluding SQL Server.
Thanks
Hey Pmxgs:
Open your Office folder in Program files and open the folder for your version of MS Office. Find the following object: MSQRY32.EXE & move it to you desktop, you will use it a lot. Now go to your desktop and double click on MSQRY32 to run it.
On the File Menu, pick New;
Uncheck the box at the bottom of the wizard that does the 'Use query wizard'
in the box pick 'MS Access Database'
click OK
pick an Access db that has either a table you want to cube or a query that gives more detail
When the next window comes up pick either that table or query you want
Close the db window
double click the '*' in the table/query list of fields to move them to matrix at the bottom
Click the 'bang' button to pull up your data.
Now on the file menu, choose 'Create OLAP Cube'
Click the 'Next' button on the resulting wizard
on the next screen, pick the items you want to count or total, i.e., people, money, amounts, etc. (You can rename here if your desire)
Click next
On this screen pick the ways you want to break down your counts or totals from above. Move them from the left window to the right window. You can stack things, like sales region, district, store; or State, County, Town; if you had dates in your underlying db, this thing does beautiful date stuff.
Click next
check the radio button at the bottom 'Save a cube file containing all the data for cube'.
Click the 'Browse' button & rename the cube. The first time thru, just take the default location for cube, note where it is. Click 'Save' & then 'Finish'
Now Start Excel. Under Data->Pivot Tables & Pivot Charts
Pick 'External Data Source' and 'Pivot Chart with Pivot Report'
Click 'next'
Click the 'Get Data' button
pick the OLAP cube tab
If your cube does not show up, then click the browse button & find your cube (at this step you will pick an object with an icon that looks like a cube and has a 'oqy' extension and click 'open'
Click OK
Click Next
Click 'Layout'
Put items you pick to count in the cube in the middle and the rest into the page fields. you can change these when the pivot table completes
Click OK
Click Finish
Tah dah
you now have a pivot table based on the cube you just created.
if you click on the chart tab & from the File Menu pick 'Save as a web file' & select 'Selection Chart' & 'Add Interactivity'
Change the name of the file and the title.
Save.
Open the html file using IE Explorer and be amazed.
If you have FrontPage xp or better, you can expose all the web components for the web page & do some really amazing things.
Good luck
Smarty1949
