Stock forecasting using SQL Server Analysis Services

Posted on 2008-02-09
Medium Priority
Last Modified: 2008-02-16
I would like a pointer as to how I could use SQL Server 2005 AS to forecast expected stock usage, based on a trend of past stock usage?

Any links / books / tutorials / other suggestions appreciated.
Question by:egxis
  • 2
  • 2

Expert Comment

ID: 20857151
Books by the Kimball group (Ralph Kimball) seem to be the most popular and respected.  Below is a link for one that is specific to SQL Server 2005.


Expert Comment

ID: 20857841
Before you start using any tool to forecast expected stock usage in any industry you need to have a good management hypothesis about how you want to do it.

Do you have seasonal trends, is there pipeline work, marketing campaigns? What is the stock lead time / sales period and how far out do you want / need to forecast?

It depends which feild you're in as to what books to suggest. Have a trawl about online first before you go much further.

Choosing the tool to help you calculate this is the second stage. SQL Server 2005 AS may not the best solution - you may find a spreadsheet does the job much better, strange but true in many cases.

Author Comment

ID: 20858589
Well, the stock I am looking to manage needs to be for multiple clients, with real time reports on their stock movement, and using historical usage of stock data give them reports on what their stock planning can be for the next 3/6/12 months, based on the historical data I already have.

I won't know if they have a marketing drive, as an example, but the data is available to them, and if they did a marketing drive they would see a spike and their real time figures, and this may skew future stock forecasts if previous stock figures were used.

I really don't have a clue where I can look for information on forecasting models, but I hoped that AS may have some tools to make this task easier for me.

Accepted Solution

Spot_The_Cat earned 750 total points
ID: 20859427
I think that you need to look at the basic requirements first and think them through given all the data you have to hand.

Before you start looking at AS dump some data into Excel and play with pivot tables to understand what you've got.

Make sure you look at supplier orders and understand their stock requirements. They may be seasonal - they may be flat.

Once you've built a picture you can start on forecasting.

If all else fails ask the suppliers if they can help - they may well be forecasting their requirements and all you need to do is pull down their forecasts.

AS is a data-warehousing and reporting tool but it doesn't give you solutions it just presents data. I hope you're not thinking that it'll resolve these issues for you.

It depends on what you're trying to achieve and this really isn't a question for EE in my view it's a business problem that requires someone in your business to come up with an answer. Alternatively hire some consultants to work it out for you.

Author Comment

ID: 20910907
Hmmm - I suppose this is not quite I wanted. It's true that I have analyze the business requirements.
fesnyng: I had a look at the ToC and that particular book doesn't appear to answer myquestions. I am comfy with RS & ETL, but I didn't see much with regard to creating forecasting models.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question