Forecast Model

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

You should probably do some upfront data preparation before you build your time series model. Do you want to do a daily sales forecast per product? In that case, you should aggregate sales figures per item at that level.

If you have data for multiple products, then you have a series for each one and each series needs to have values for all time slices present in the data. For example, if you have daily data, you need to have a sales figure for each product for each day and all the series need to begin/end at the same point. The error you're seeing is due to this issue. Specifying MISSING_VALUE_SUBSTITUTION will allow missing data points for a time slice across multiple series to be filled in with the specified value.

|||Thanks for the reply. Yes it is a Daily Sales Forecast that I am trying to build. I will try your suggestion

Forcing Column Headings

Hi...

I have a report that does not use the detail line. It groups the information it needs and prints out in the lowest section heading (Product Type).

The problem arises when we have an employee ( next group up) who has worked on loads of product types in the date range selected.

The report prints perfectly except that when it skips to a new page we only get the page heading, not the section headings.

I have tried moving the section headings to the page headings - report "looks" fine, but does not work as it fails to recognise any changes in the groups.

Anyone any ideas how I can "force" it to print a heading when a certain number of lines have been printed??

Am new to Crystal so am sorry if this is a stupid posting...Will checking the 'repeat group header on each page' box do the trick for you?|||Thanks for the response.

Was not aware of that box, and finally tracked it down.

Unfortunately tried it and it did not change anything. Will carry on experimenting, but if you have any other ideas they will be gratefully received!!

Thanks again|||Update....

The more digging round I did, the more your suggestion seemed to be what I wanted, so I could not understand why it did not work.

So I went back and tried it again... and it now works!

No idea what I did before - can only assume I put it in the wrong section. I can only blame it being early and a lack of caffeine...

But it now works, as I said.

Many, many thanks for your help.sql

For XML clause

I'm trying to turn a product table into an XML file to create an
heirarchical menu, and I was wondering if there was any easy way to do
this. The table is (simplified) below:

Create table product(
category varchar,
subcategory varchar,
name varchar)

and I want the XML to represent the category structure as below:

<Categories>
<sucategories>
<products>
....
</products>
</subcategories>
</Categories
Thanks,

John(jhoge123@.yahoo.com) writes:
> I'm trying to turn a product table into an XML file to create an
> heirarchical menu, and I was wondering if there was any easy way to do
> this. The table is (simplified) below:
> Create table product(
> category varchar,
> subcategory varchar,
> name varchar)
> and I want the XML to represent the category structure as below:
><Categories>
> <sucategories>
> <products>
> ....
> </products>
> </subcategories>
></Categories
I think that you should be able to do this with FOR XML EXPLICIT.
But I'm not very good at XML queries, so I don't want give an outline of
something that is likely to be incorrect.

If you post a CREATE TABLE statement for the table, INSERT statements
for some sample data, and the desired XML from the data, I might give
it a try.

Or you could look at FOR XML yourself in Books Online. (That's anyway
where I have to look to write this type of queries. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Create table product(
category varchar(10),
subcategory varchar(10),
name varchar(10))

insert into product(category,subcategory,name)
values ('Vehicle','Car','Sports')
insert into product(category,subcategory,name)
values ('Vehicle','Car','Saloon')
insert into product(category,subcategory,name)
values ('Vehicle','Motorbike','Sports')
insert into product(category,subcategory,name)
values ('Vehicle','Motorbike','Tourer')

dbcc traceon(257) -- pretty print for Query Analyzer text results

select distinct 1 as Tag,
null as Parent,
category as [Category!1!Value],
null as [subcategories!2!Value],
null as [products!3!Value]
from product
union all
select distinct 2 as Tag,
1 as Parent,
category as [Category!1!Value],
subcategory as [subcategories!2!Value],
null as [products!3!Value]
from product
union all
select 3 as Tag,
2 as Parent,
category as [Category!1!Value],
subcategory as [subcategories!2!Value],
name as [products!3!Value]
from product
order by [Category!1!Value],[subcategories!2!Value],[products!3!Value]
for xml explicit|||(markc600@.hotmail.com) writes:
> dbcc traceon(257) -- pretty print for Query Analyzer text results

Ah, that's one cute. Didn't know.

And thanks for posting the example!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

FOR INSERT Trigger

I've got 2 tables to collect orders. The ORDERS table collects the name,
totals etc. The DETAILS table collects the products. One product per each
row. When a customer places an order, their CType field may get populated
with QW. If CType = 'QW', With a trigger, I need to add a product to the
DETAILS table. The following doesn't work, but should give you an idea what
I need.
IF (SELECT ORDERS.CType FROM AffOrders) = 'QW'
INSERT INTO DETAILS
(OrderID, OrderNo, Description, Qty, PriceEach)
VALUES (ORDERS.OrderID,'SF','ServiceFee',1,4)
How do I get this done? Obviously, I need to query the order that was just
placed. I also need to place the corresponding OrderID into DETAILS.
thanks!Try this:
CREATE TRIGGER Orders_TrigAddQWDetails
ON dbo.Orders
FOR INSERT
As
Insert DETAILS (OrderID, OrderNo,
Description, Qty, PriceEach)
Select OrderID,'SF','ServiceFee',1,4
From Inserted Where CType = 'QW'
"shank" wrote:

> I've got 2 tables to collect orders. The ORDERS table collects the name,
> totals etc. The DETAILS table collects the products. One product per each
> row. When a customer places an order, their CType field may get populated
> with QW. If CType = 'QW', With a trigger, I need to add a product to the
> DETAILS table. The following doesn't work, but should give you an idea wha
t
> I need.
>
> IF (SELECT ORDERS.CType FROM AffOrders) = 'QW'
> INSERT INTO DETAILS
> (OrderID, OrderNo, Description, Qty, PriceEach)
> VALUES (ORDERS.OrderID,'SF','ServiceFee',1,4)
> How do I get this done? Obviously, I need to query the order that was just
> placed. I also need to place the corresponding OrderID into DETAILS.
> thanks!
>
>