Howto: Historical Prediction In Sales Forecast Model
Aug 3, 2006
Hello...
I am new to SSAS and i want to try to build a "Sales" model. I will have some "Usage" data for some timespanns, but I am not quite sure how to tackle this. Is there somewhere a "Howto" for this?
Edit: There are several locations, and for each location a forecast is needed. And the Icing would be If I would be able to tell where my supplies must go 1st to achieve the best sales...
The potential Client wants to use Oracle but I would like to show them that SQL Server is the better tool for this ;)
View 1 Replies
ADVERTISEMENT
Aug 2, 2006
Hi,
I have built a time series model to forecast sales value
I have data from jan 2004 to jan 2006 and the sales value is
at a day level in my database. But I am aggregating it to month level in the
DSV of the mining model.
I am required to make only historical predictions using the
above model starting form jan 2004 to jan 2006 for every month.
I have set Historical_Model_Count
and Historical_Model_Gap parameter
values to 24 and 10 respectively, and trying to predict for the past few months
(PredictTImeseries(SalesValue,-1,1))
But its throwing me the following error
Error(Data Mining): A time series
prediction was requested with a start time further in the past than the
internal models of the mining model, Sales Forecast, specified in the
HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process
In fact it throws the above error irrespective of what the Historical_Model_Count and Historical_Model_Gap parameter values
are
I am not able to figure our why this problem is happening?
What should the parameter values for the above scenario?
It would also be helpful if I can get an explanation on how
these two parameters affect the historical predictions. I kind of understand
that these two parameters are important for historical predictions but don€™t
know why or how.
View 4 Replies
View Related
Aug 5, 2006
I have few more clarification regarding time series.
Firstly
In my model the month level product sales value represented across 1st day of every month. So that the key time column is of datetime datatype containing a sequence of dates representing the 1st day of every month of the year.
Eg: 2006-01-01, 2006-02-01€¦€¦€¦. etc. all in (yy-mm-dd) format
But
when I make prediction for next five months, though it makes monthly
predictions the date part for the months are random whereas I expect
the date part to be 1st of every month. What is the reason for this and how can I overcome it.
Secondly
Predicted
sales values for some time period are negative though I do not have any
negative value in the training data. What is the reason for this and
how can I rectify it?
Thirdly
In
one of your earlier posts you had said that the time series algorithm
does not have any built in time intelligence but uses the key time
column as a time sequence stamp. So If have to make predictions for a
particular time period where the time slice for each time period is 25
days or 50 days etc, then I understand that the input data used to
train the model should be in the same time sequence.
Or
Can I specify the span of the time period according to which the prediction needs to be made?
Basically
how can I use the same time series model to make monthly, yearly,
quarterly, daily or predictions or for custom time period like I have
mentioned above.
View 3 Replies
View Related
Sep 30, 2015
I want to calculate the sum of actual sales until a date and forecast sales after a date.I am not sure what the best approach to this problem is, but I have tried my best with the following approach. Any better ways to solve this (using DAX).
I have created a parameter table that offers the last date of each month as possible choices to the user. I have tried to create a measure that sums actual sales up until this date.
SalesQuantityActual:=IF(HASONEVALUE(parLastActualMonth[Date]);CALCULATE(factSalesActual[Quantity];factSalesActual[Date]<=VALUES(parLastActualMonth[Date]));BLANK())
Unfortunately the measure above does not work.
In addition to the parameter table, I also have a normal date table.
View 2 Replies
View Related
May 24, 2006
Hi
I am new to Data mining in SQL Server. I am using SQL 2005 to create a forecast model for Product sales. I two fact tables that I am using. One has all the Orders historically with the line item details. The other table is a time dimension table which has the value of each of the time values referenced in the Orders. So the time dimesion has multiple values for one day as it goes down to the hour the order was placed.
Can I create a forecast using the time series algorithm in the Data mining module. When I tried to use this algorithm, I got an error that the time is not synchronized with starting series "Unknown" and I should try to set the missing_value_Substitution parameter to previous.
Can some one explain to me how this will resolve my issue. I read in one of the articles on Data mining that in order to use the time series algorithm , I need to have unique set of values for the time. Can some one help me with this
Thanks
AY
View 4 Replies
View Related
May 29, 2006
I believe saving prediction query results to relational tables is possible (the BI studio does it!). I am not clear on how to do this w/o the BI studio, which means if I write a DMX query and want to store its output to a relational table, how do I do it?
Tips, anyone?
Thanks!
View 6 Replies
View Related
Jun 26, 2007
Mining Model Prediction... what for?
Which Data Mining algorithm use Mining Model Prediction?
Every algorithm has to use Mining Model Prediction for a final goal of a Data Mining Project?
View 5 Replies
View Related
Dec 31, 2006
I have a market basket model using associations. It generated several dozen itemsets. However when I attempt to run a singleton prediction like this:
select (Predict(Orderproduct3q,INCLUDE_STATISTICS,10)) as [Recommendation]
From
[Case All]
NATURAL PREDICTION JOIN
(SELECT (SELECT '16407' AS [Pname])) AS t1
the resulting predictions don't take the itemsets into account. Instead, the predictions consist of the ranked products in the training set, ordered by frequency. This appears to happen regardless of the precise query specified within the "natural prediction join".
What's going on here and how do I generate a singleton prediction which makes use of the itemsets?
View 5 Replies
View Related
Feb 11, 2008
Is there a way to display the actual predicted value for an output attribute for a particular model. For example, say I am trying to predict if a particular customer is going to take advantage of a promotion (0=no, 1=yes) and I use neural networks. I know that I can use "Predict" to give me the prediction "yes" or "no" for each customer. However, the neural network actually spits out a number as a result. For example, a 0.997 would be interpreted as a "yes" for life insurance promotion. I do not want the probability that the prediction is correct. I want the actual output for the network.
The reason being is that I want to compute an error rate between the predicted value and the acutal value (root mean squared error or some other measure). Is there a way to compute this using the mining model prediction tab design view? I do not want to write the actual query as I teach a course in data mining using SQL Server and my students do not know DMX queries.
Thanks for any help you can provide.
View 5 Replies
View Related
Dec 12, 2006
I have a question about writing a prediction query against a clustering model that has the same column added more than once.
Per Jamie, I can accomplish some crude weighting by adding a column to my model multiple times. See this post for an explnation... Now that I have that worked out, I was wondering how my DM query would look? If I have Input_A1, Input_A2 , & Input_A3 all being source from the same column in my structure do I have to reference all three when writing my prediction query?
View 1 Replies
View Related
Dec 28, 2007
HI
Thanks a lot for your answer
I say my request but I don€™t know why anybody don€™t answer me
I have a project about predicting a value about selling and buying of a good like t-shirts and I use data mining for my project ,so I should use time series algorithm ,that €˜s mean I have previous data about t-shirts for 11 months ago and now I should say for 12th month ,how many t-shirts are sale?
My tables saved on the excel file and it is problem, because how should I use this table for building model?
After building a model and structure and predicting the value of 12th month for this store in the mining model then I use this query in model in bi:
€œSELECT
PredictTimeSeries(amount)
From
[Forecasting]€?
This query showed a column and prediction value.
After all of that now I should show this value in the application so I use c# language for building it, so I use a form in c# then I add a button to form that with clicking on this button, I can connect to my structure and then show process of connecting in the panel, then with clicking on the other button I can use this query
(€œSELECT
PredictTimeSeries(amount)
From
[Forecasting]€?
) and after using I can see prediction value in the textbox, that€˜s mean the value of 12th month show on textbox.
Form has two buttons: one button is for connecting to mining structure and other button is for sending PredictTimeSeries query to structure, one textbox, which is for showing the predicting value for 12th month for selling t-shirts, one panel for viewing the lift chart.
Also you say
You are building an application that programmatically creates mining structure and a model, and then you want to train the model and display some results.
And I don€™t know how can I train my model, I should use a special code for it?
If your answer is say, please explain that and then say that codes are for training.
Please if you can send c# code for Sporadic of stage, please send that, I need to this code; my request is emergency for me.
Thanks a lot
i am very sad because any body don't answer me
View 1 Replies
View Related
Jun 21, 2006
Read only transacted replication to about 60 clients. 100 or so transactions a day (8 hours), transaction (record) is probably about 8K/transaction. Reliable connection over 100base-T. Latency - 15 minutes would be great.
This doable? -- I couldn't find a timing/ sizing model any place.
View 1 Replies
View Related
Aug 30, 2007
Dear All,
I have a database table which has all the inputs, key and the result. In visual studio, I created a decision tree model which has exactly the same fields as in the table. However the visual studio automatically add space preceding the capital letters. As the field name in the Datamining model and those in the database table are slightly different. I cannot use NATURAL prediction join. Is there anyway to told the visual studio not to add the spaces in the variable names?
Thanks and regards,
Tony Chun Tung Siu
View 1 Replies
View Related
Nov 29, 2006
I am using BI Dev Studio for SS2005 in a research (as opposed to a production) environment. Often I want to compare the results of multiple models using the same attributes. If I switch to a different model, the Design view completely resets. Is there any way to retain the same field names with different models in the Design view?
My current workaround is to give my models similar names with AR, DT, CL, LOG, NN suffixes and make global changes in the DMX.
I have consulted the following without finding an answer:
http://msdn2.microsoft.com/en-us/library/ms178445.aspx
http://msdn2.microsoft.com/en-us/library/ms175642.aspx
http://msdn2.microsoft.com/en-us/library/ms175678.aspx
http://msdn2.microsoft.com/en-us/library/ms175637.aspx
Thanks for your help,
Sam
View 3 Replies
View Related
Jul 27, 2006
Hi,
I have built a Sales Forecast model to predict the sales
value.
Along with making historic predictions for previous time
periods I also want to retrieve the actual sales values for those periods.
How can I achieve this in a time series model?
I also would like to know how do mining models store the data.
Do they store the data in the same table/view format
as their respecive data source view or in the Model Content format.
View 3 Replies
View Related
Feb 19, 2008
Hi all,
I have a very simple time series model which processing works fine without any problem. However when I run the following query
SELECT
[TimeSeries].[PriceChange],
[TimeSeries].[Symbol],
PredictTimeSeries(PriceChange, -3, 2)
From
[TimeSeries]
WHERE
[TimeSeries].[Symbol] = 'x'
I get the following error:
TITLE: Microsoft SQL Server 2005 Analysis Services
------------------------------
Error (Data mining): A time series prediction was requested with a start time further in the past than the internal models of the mining model, TimeSeries, specified in the HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process.
The following is the excerpt of the minding model script related to the two parameters:
<AlgorithmParameters>
<AlgorithmParameter>
<Name>MISSING_VALUE_SUBSTITUTION</Name>
<Value xsi:type="xsdtring">Previous</Value>
</AlgorithmParameter>
<AlgorithmParameter>
<Name>HISTORIC_MODEL_GAP</Name>
<Value xsi:type="xsd:int">1</Value>
</AlgorithmParameter>
<AlgorithmParameter>
<Name>HISTORIC_MODEL_COUNT</Name>
<Value xsi:type="xsd:int">10</Value>
</AlgorithmParameter>
</AlgorithmParameters>
These HISTORIC_MODEL_GAP (1) and HISTORIC_MODEL_COUNT (10) should accommodate PredictTimeSeries(PriceChange, -3, 2). Could anyone shed some light on this?
View 3 Replies
View Related
Sep 14, 2007
Hi,
I am not getting Mining Accuracy Chart and Min ing Model Prediction
Plz tel me how to do.And how to use the filter input data used to generate the lift chart and
select predictable mining model columns to show in the lift chart
View 1 Replies
View Related
May 19, 2015
i am trying to find a way to link an 'initial' Sale ID of a product to 'future' Sale IDs of products that will trace back to the original Sale ID.For example, if I call the original sale , 'Sale ID #123', how can i link future Sale ID's (child[ren]) and all future sales to the original Sale ID #123? Can I use a Surrogate Key or similar function?
Parent:Sale ID #123
Children:Â Sale ID # 456,
Sale ID #789,
Sale ID #.....
how I can link the original Sales ID (Parent) to Sale ID's (child[ren]) of future purchases currently existing and in the future going forward?
View 4 Replies
View Related
Dec 9, 2014
I have the below data. I need to produce a report that shows customer and total sales who had the max sales by year.
Order ID Cust ID Year Sales
O1 C1 2000 100
O2 C1 2000 150
O1 C2 2000 50
O1 C1 2001 150
O2 C3 2001 200
Report:
Cust ID Year Sales
C1 2000 250
C3 2001 200
View 1 Replies
View Related
Feb 22, 2008
Hi
After building a model in BI, I want to view the chart of model in mining model viewer, in the chart tab I can just see one prediction value that means for my model do prediction for some time slice and in prediction steps I can specify how many steps, I want to show this chart
In mining model viewer tab we can see the chart of prediction also decision tree and the chart is for showing all of value prediction, and with choosing prediction steps we can specify that show just one value prediction or two or several values. But sometime I can see just one value in chart and sometime I can see several values in chart,
This difference is for my data or no?
And also for viewing historic prediction I should choice €œshow historic prediction€? and before that I should set
Two parameters: Historic_ model _count and historic _model _count,
But I can€™t see historic prediction (sometime this happens)
Please help me.
View 1 Replies
View Related
Dec 5, 2005
Running 2005 Beta 3 Refresh. When I first deploy, it works fine. Subsequent deployments yield the following error:
View 9 Replies
View Related
Nov 16, 2006
Using the TimeSeries algorithm, how do I forecast more than one time period ahead? I read in you book on page 182 that PredictTimeSeries function can take a parameter for the number of time periods you want to predict. Fore example, SELECT PredictTimeSeries(Bread,5) tells the algorithm to predict the next 5 time periods. Can you tell me how to change that parameter using the graphic interfaces?
Thanks!
View 1 Replies
View Related
Oct 29, 2014
Attached is a file that generates a sample dataset from which I want to forecast a value for column 'Parcel Count 2014' from tomorrow (30/10/2014) till the end of December (31/12/2014). The forecast should be based on values from column 'Parcel Count 2013' for the same Month.
View 9 Replies
View Related
Dec 17, 2014
Background is that I am recreating charts in SSRS that were being created in Excel. The source data is residing in a SQL Server database. I'm having a hard time coming up with a SQL query to provide a 6 month forecast. I can get my data into a pivot (within a stored procedure) in the following format:
Period---------Date--------------------------------Percent
1--------------2013-11-01 00:00:00.000---------0.3762
2--------------2013-12-01 00:00:00.000---------0.3584
3--------------2014-01-01 00:00:00.000---------0.3604
4--------------2014-02-01 00:00:00.000---------0.3292
5--------------2014-03-01 00:00:00.000---------0.3519
[Code] ....
I need to forecast the next 6 dates 12/1/2014 thru 5/1/2015 using the last 6 months in the data set (periods 8 thru 13)
Period---------Date--------------------------------Percent-------Forecast
1--------------2013-11-01 00:00:00.000---------0.3762
2--------------2013-12-01 00:00:00.000---------0.3584
3--------------2014-01-01 00:00:00.000---------0.3604
4--------------2014-02-01 00:00:00.000---------0.3292
5--------------2014-03-01 00:00:00.000---------0.3519
6--------------2014-04-01 00:00:00.000---------0.4064
[code].....
how to use the first table to generate the forecast values in the second table.
View 4 Replies
View Related
Oct 12, 2007
I have a result set that looks like this:
Code Block
Quarter
Year
EstimatedValue
ClosePercent
EstimatedCloseDate
4
2007
100000
50
12/31/07 5:00 AM
4
2007
20000
50
11/30/07 5:00 AM
4
2007
20000
90
10/30/07 5:00 AM
1
2008
278000
50
3/31/08 5:00 AM
4
2007
200000
50
11/30/07 5:00 AM
4
2007
225000
90
10/31/07 5:00 AM
4
2007
36500
90
10/31/07 5:00 AM
4
2007
80000
90
10/31/07 5:00 AM
4
2007
107200
90
10/31/07 5:00 AM
4
2007
225000
75
12/31/07 5:00 AM
4
2007
35000
50
12/31/07 5:00 AM
I have create a simple tabular rolling forecast report (with cumulative totals) from today (October) thru the next 12 months that looks like this. It smartly works no matter when the report is generated, by starting with this CurrentMonth and moving forward by using 1,2,3,4,etc. in the dateadd: =MonthName(datepart("m",dateadd("m",1,Now())))
The report sample (formatting lost in dropping it in here):
Code Block
Close Pct
October
November
December
January
February
25.%
$0
$0
$26,625
$0
$0
50.%
$237,500
$110,000
$262,500
$0
$0
75.%
$56,250
$0
$891,075
$0
$0
90.%
$1,051,830
$0
$0
$0
$0
Monthly Total
$1,345,580
$110,000
$1,180,200
$0
$0
Cumulative Total
$1,345,580
$1,455,580
$2,635,780
$2,635,780
$2,635,780
It is working fine....there doesn't seem to be anything wrong with it (all numbers total correctly, etc.), but it is very unelegant.....and I know there must be a better way.
In the righthand most month (which would be September 2008) column, I have a formula that produces the amount (the Monthly Total amount is the same):
Code Block=sum(iif(datepart("m",dateadd("m",11,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0)))
and for the Cumulative Total Amount it gets really hideous, as it is trying to add up all of the totals across the board:
Code Block
=sum(iif(datepart("m",dateadd("m",0,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",1,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",2,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",3,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",4,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",5,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",6,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",7,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",8,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",9,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",10,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",11,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0)))
I have searched high and low for examples of reports that do something similar.
Can anyone offer any advice?
Thanks.
View 2 Replies
View Related
Oct 2, 2015
Is there any way to copy my Data of 2015 to the Planning/Forecasting Value of 2016?
My question is based on that we use INFOR ION BI right now and there we can just add an Button in our reports wich physically copies the value from one year to the next year based on some other rules in the cube.
Now I need to make this example work with SSAS and Excel PivotTables but I cant figure out how.
I have absolutely no clue where and how to accomplish it. Do I use Calculations, do I use Actions, do i make it in the Dataview, Cube or directly in Excel?
View 4 Replies
View Related
Apr 22, 2015
What is the best way to forecastestimate space for non-clustered index on a table?
Example :
Table name : Test123
Row : 170000000
Reserved : 18000000 KB
Data : 70000000 KB
Index: 40000000 KB
Note: Test123 already has clustered index and 2 non clustered indexes.
View 7 Replies
View Related
Nov 26, 2007
I have a report which totals sales by customer. Then table footer has a grand total of all customer sales. I would like to get a percent of each customer's sales against the total sales. How do I get the sum from the table footer to use in an individual customer row?
Thanks.
View 3 Replies
View Related
Oct 19, 2015
I need to develop a language specific dwh, meaning that descriptions of products are available from a SAP system in multiple languages. English is the most important language and that is the standard. But, there are also requirements of countries that wants productdescriptions in their language.Â
Productnr Productdesc Language
1       product    EN
1       produkt    DE
One option is to column the descriptions, but that is not very elegantly. I was thinking of using bridge tables to model this but you have to always select a language in a filter (I think)..
I'm thinking of a technical solution, such that when a user logs on, the language is determined and a view determines whether to pick a certain product table specific for a certain language. But then I don't have the opportunity to interchange the different language specific fields in a report (or in my case PowerPivot).
View 2 Replies
View Related
Apr 25, 2008
We have the following scenario,
We have our Production server having database on which Few DTS packages execute every night. Most of them have Bulk Insert stored procedures running.
SO we have to set Recovery Model of the database to simple for that period of time, otherwise it will blow up our logs.
Is there any way we can set up log shipping between our production and standby server, but pause it for some time, set recovery model of primary db to simple, execute DTS Bulk Insert Jobs, Bring it Back to Full recovery Model AND finally bring back Log SHipping.
It it possible, if yes how can we achieve this.
If not what could be another DR solution in this scenario.
Thanks Much
Tejinder
View 6 Replies
View Related
Sep 19, 2005
Hi Everyone.
I need some advise on how to create a historical database.
What is the best way of doing this? For example, should I create a new row if a column is changed in a row and Time Stamp all record? What happens when I have child tables link to a Header table?
I have been looking on the NET for methods of creating a historical database, but I cant find any.
Thanks in advance
View 11 Replies
View Related
Mar 13, 2008
Hi,
I have a question about historical table. I have a table in Sql Server that keeps the history of my clients for the last 3 months.
This table has a PK : client_code and rep_date_id. I want to do a query that returns me a client and to extract the date when i inregistreted for the first time.
I think about a cursor, but i don't know how to use it.
My table looks like this structure:
- client_code
-activity_code
-country_code
-district_code
-...
-rep_date_id_n
And I want to return
--activity_code
-country_code
-district_code
-...
-start_date
-current_date
Thanks
View 1 Replies
View Related
Oct 20, 2005
A general data design question:We have data which changes every week. We had considered seperatinghistorical records and current records into two different tables withthe same columns, but thought it might be simpler to have them alltogether in one table and just add a WeekID int column to indicatewhich week it represents (and perhaps an isCurrent bit column to makequerying easier). We have a number of tables like this, holding weeklydata, and we'll have to query for historical data often, but only backthrough the last year -- we have historical data going back to 1998 orso which we'll rarely if ever look at.Is the all-in-one-table approach better or the seperation of currentand historical data? Will there be a performance hit to organizing datathis way? I don't think the extra columns will make querying too muchmore awkward, but is there anything I'm overlooking in this?Thanks.
View 1 Replies
View Related
