It is impossible to pass full data warehousing knowledge in a newsgroup
posting but here is my attempt :)
First get it out of your transactional environment (OLTP). Use DTS to
transfer this to another database (preferably another server depending
on size).
You will want to decide the subject matter of your cube which for you
would be the sales. This is your lowest granularity and you will use
this table to create what is called your fact table. Your fact table
will consist of the lowest granular aggregations (sales) and foreign
keys back to the other tables which will be used as dimension tables.
This is the structure that you will use to build the cube from. You need
to install Analysis Services for SQL Server 2000. Go into Analysis
Manager and say new database. call it whatever you want.Here you will
add a data source that points to wherever you sent your transformed
table. then right-click on cubes and say new cube, use the editor not
the wizard. it will ask you what tables you want to use, specify your
fact table (the one with sales data and your key columns) and add it.
You can then add your dimension tables (details - like customer, product
and possibly stock in your case). In the cube editor you will create
dimensions (all real cubes need a date dimension) dimensions are
typically what the customer says they want to see the data "by"
Examples:
I want to see the number of sales by region (region would be a
dimension)
I want to see the average sales by customer (customer would be a
dimension)
Again, these are examples, might not apply to you.
Then you will pick your measures, these are your aggregations, WHAT do
they want to see (dollars, total orders, etc.)
Calculated members are more advanced and you can get into those by
looking at MDX examples. From there you need to process the cube and
then you can connect to it by using Excel pivot tables (or something
better once you get the hang of it)
If you want to know the theory and use of data warehousing/cubing get
Ralph Kimball's data warehouse lifecycle toolkit book, if you want to
quickly learn how to use analysis services get Reed Jacobson's "Analysis
Services: Step by Step"
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!