Forecast Model
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
ADVERTISEMENT
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
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 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
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
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
Oct 27, 2007
Hi all,
I have MS Time Seeries model using a database of over a thousand products each of which has hundreds of cases. It amazingly takes only a few minutes to finish processing the model, but when I click Mining Model Viewer to view the models, it takes many hours to show up. Once the window is open, I can choose model for different products almost instantly. Is this normal?
View 1 Replies
View Related
Aug 10, 2005
hi !!i have a question about the connected and disconnected model to access the Sql server DB.......i know that there is better to choose one rather than the other in some situantions and there is no better model in all cases...... os i hope you can help me to decide what shall i choose...i will use the DB to connect to Web services and read data from the DB and wrtie some data back.......i do not know that to use ..... i hope you advise me and tell me about the rules that will allow me to choose what model to choose .... i appreciate your help!!Thanks !!!
View 2 Replies
View Related
Nov 15, 2006
I get the following error when I try to load the mining model in the mining model viewer
Query (1, 6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[NeuralNet].[GetAttributeValues]' function does not exist.
I get a similar error when I try to load the Load Mining Accuracy Chart
Failed to execute the query due to the following error:
Query (1, 6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[AllOther].[GenerateLiftTableUsingDatasource]' function does not exist.
I have OWC 11 installed. What am I missing here?
Thanks
View 7 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
Jun 6, 2001
Hello,
I just installed the enterprise edition of MS SQL Server 2000 in model database there are no store procedure. whereas in MS SQL Server 7.0 there are 18 SP so when I create the new db there is no SP only the one which I've created. Is it normal or did I miss something in installing
Thanks
Rea
View 1 Replies
View Related
Apr 9, 2008
Hi,
I'm new in SQL server DBA and in my company. There are about 40 applications in my company and since there's no DB document available, my boss has asked me to prepare a full documentation package about DB schema of each application. I'm going to use RUP method for that but I'm not sure of including which information in the package.
Any idea is appreciated
View 7 Replies
View Related
Apr 5, 2006
hi
I have changed the initial size of the model DB , now I want to change it again , but I want to make it smaller than the one it is currently can i do that
When I go the EM & try changing the file size it says :-
The new DBFile size must be larger than the current size
Regards
aki
View 2 Replies
View Related
May 7, 2002
Hi there,
My problem is : No de system stocked procedure in the system db "model".
What should I do to visualise theses sp.
any help will be appreciated.
Thanks in advance
View 1 Replies
View Related
Jan 31, 2003
SQL Server 2000 SP3.
Prior to SP3 the recovery model was switched to simple during transfer (Copy object task) and changed back to the previouis setting after DTS was complete.
Nice thing because performance was increased and T-Log was keep small.
Now I assume that the recovery model is switched to bulk-logged causing the T-Log to explode, to be onest not in all my databases.
1.Is my interpretation regarding recovery model correct?
2.Does anybody knows the reason of this change?
Any suggestion is really appreciate.
Thank you very much - kind regards.
View 2 Replies
View Related
May 7, 2002
Hi there,
My problem is : No de system stocked procedure in the system db "model".
What should I do to visualise theses sp.
any help will be appreciated.
Thanks in advance
View 1 Replies
View Related
May 31, 2002
Hello,
I'm new to MS SQL Server I want to know which recovery model is good, Full or Bulk Logged as I'm doing full backup at 11:00 PM and diffential Backup at 12:30 PM and from 8:00 AM to 6:00 PM 15 min log backups.
Please guide me which recovery model should I choose.
Thanks
Lara
View 1 Replies
View Related
Jun 10, 2004
Hi all,
I need some sugestions from all of you about setting up security model in
our SQL2000 box.
The server was setup using Mixed mode. However, all the applications
(web and MS access) access the server using "sa" userid.
There are several databases in our server. Ex: (DB1,DB2,DB3,DB4 and DB5)
Application 1: need read/write access to DB1,DB2 and DB3
Application 2: need read/write access to DB5
Application 3: need read/write access to DB4 and DB3
Should I set up three userids and give them the dbo access to those
database that they need to use?
Does that make any sense to you?
Thank you for all your suggestion
View 5 Replies
View Related
Nov 26, 2004
Hello, i have no specific problem but i just want to know your opinion on the issue. Last week I tested my recovery scenario and after installing new server i successfully restored master db. Then I tried to start server, but it wouldn't come up because physical path of model db in master..sysdatabases table didn't correspond to the actual location of db on new server, so that tempdb couldn't be created from model db on server startup. I started server without recovering dbs with exception of master using trace -T3608. Now I hoped that I could restore model using MOVE clause but it gave me error saying that restore operation needs to use temporary table in tempdb. To break this vicious circle I used model db files from installation CD and put them to the expected location. After two restarts server accepted the files and I went on restoring msdb and user databases. But I'm sure I must be missing something and there must be better way to do this but i haven't been able to find anything relating to this problem. Thank you for your input. mojza
View 1 Replies
View Related
Oct 31, 2005
attempted to move my Model and msdb database files to a different drive on the server. I did the following:
* Restarted SQL Server 2K with the -T3608 startup parameter
* successfully detached msdb and model DBs
* moved the msdbdata.mdf, msdblog.ldf, model.mdf and modellog.ldf
files to their new homes
* successfully reattached model and msdb
When I take away the -T3808 startup parameter and restart SQL Server, I do not see the model database in the db list in enterprise manager eventhough my 'attach' seemed to work. Now I get the following error when i try and query against the Model database using query analyzer:
"Could not locate entry in sysdatabases for database 'model'. No entry found with that name. Make sure that the name is entered correctly"
Also, when I look in the sysaltfiles table in Master, I see no reference whatsoever to the Model DB
What did I do wrong?
View 1 Replies
View Related
Jan 11, 2006
What would be the best Recovery Model for: a database which is 4 gig in size and imports via MSAccess queries and also stored procedures approximately 400,000 meg of data each month (and some other update queries are run against it) and it is also queried off of for totals on weekly basis?
The problem is that the SQL Server box only has 512 meg of memory and the tranlog on this database grows tremendously each import and when update queries are run against it. This tends to slow things down a bit on our other databases. We are getting a new SQL Server box but until then, what would be the best recovery model? I currently have it as Bulk-Logged and allow the tranlog to grow by 10% (with a base of 250 meg). The tranlog grows to up to 5-10 gig and in order to shrink it, I have to change the recovery model to Simple, and then back to Bulk-Logged in order to shrink it (I've tried all the dbcc shrinkdatabase, dbcc shrinkfile, dbcc showcontig, and dbcc checkdb commands as well as BACKUP LOG dbName WITH TRUNCACTE_ONLY and nothing will shrink it unless I change the recovery model to simple.)
View 2 Replies
View Related
Jan 30, 2006
hi guys,
i have the following category table...
CREATE TABLE [dbo].[categories] (
[cat_id] [int] IDENTITY (1, 1) NOT NULL ,
[dModified] [datetime] NULL ,
[dCreated] [datetime] NULL ,
[cat_parent_id] [int] NULL ,
[cat_lft] [int] NULL ,
[cat_rgt] [int] NULL ,
[cat_image] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[cat_sort_order] [int] NULL
) ON [PRIMARY]
GO
i now wish to add a levels column which indicates (funnily enough :)) which level each record is on starting from 1.
is there a query i can use which will output for me the levels based on the child/parent relationship?
regards
mike
View 3 Replies
View Related
Sep 6, 2006
I try to create relational database but i cant develop a model. after i create a database i will create an OLAP cube for my project.i dont want to complex model but i cant create a simple database. it may be marketing, CRM or another. i dont know what fields and tables i should create. i saw sample databases such as northwind and foodmart but i must create original database for my project. please help meeeeee
View 1 Replies
View Related
May 17, 2007
Hi,
I am finding nice examples data model of CMS. Do you know any link to data model phorum or any link to data model examples(nice)?
Thanks
View 3 Replies
View Related
Jun 16, 2008
Why would we need to know the data model to write queries?
Sorry but its been a long time since I've worked with databases and I don't remember!
Could anybody lead me in the right direction please?
View 1 Replies
View Related
Jun 16, 2008
Why would we need a data model to write queries?
Sorry but its been a loong time since I've worked with databases and I barely remember.
Could anybody lead me in the right direction please?
Thanks.
View 1 Replies
View Related
