[SQL Server Data Warehousing] SQL Server Inserting Dummy Rows to Fact Table for each id's for a month if the id is missing for the Month

I am developing a Data Mart for Meat Slaughter Statistics Using SQL Server and WhereScape Red.I got a situation where I have to insert a dummy record for a month for an animal (eg Lambs slaughtered) with 0's and Nulls in the Calculated and other fields if that animal type is missing for the month. This is essentially to handle the reports to display 0 values even if they are missing for the month. I got a unique index in fact table for 5 fields(DTLS_DOC_XREF,ACCNBRI,TransNbr,PRINT_SEQ_NBR,ActPeriod) in the table. I got a WeekEndDateKey in the Fact table that can be joined to DimDate table to arrive at the dates. Is there a way I can attain this without much hastle in WhereScape Red? Currently I am building a custom procedure for this, but not able to attain the desired output. Is there any best practices for inserting dummy records based on conditions in WhereScape Red. The granularity of Fact Table is at a Week Level for an Animal.The approach followed by me is:

Creation of dummyperiod table which has got rows from 1 to 12
Identification of Animal(AnimalIdentifierKey) in the Fact table which is having count distinct Period < 12 for an Year
(Year is not available in FactTable, is derived from DimDate table)
Selection of DateKey and Period for the AnimalIdentifierKeys from the above steps
Inserting to the FactTable, of the 5 unique index in Fact table the missing ActPeriod will be inserted for the animal.

I got issues in selection of Animals and Selection of datekey and period using custom procedure. Any inputs on resolving this is highly appreciated. We are on Wherescape 5.6 verion with SQL Server 2008.

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] SQL Server Inserting Dummy Rows to Fact Table for each id's for a month if the id is missing for the Month

I am developing a Data Mart for Meat Slaughter Statistics Using SQL Server and WhereScape Red.I got a situation where I have to insert a dummy record for a month for an animal (eg Lambs slaughtered) with 0's and Nulls in the Calculated and other fields if that animal type is missing for the month. This is essentially to handle the reports to display 0 values even if they are missing for the month. I got a unique index in fact table for 5 fields(DTLS_DOC_XREF,ACCNBRI,TransNbr,PRINT_SEQ_NBR,ActPeriod) in the table. I got a WeekEndDateKey in the Fact table that can be joined to DimDate table to arrive at the dates. Is there a way I can attain this without much hastle in WhereScape Red? Currently I am building a custom procedure for this, but not able to attain the desired output. Is there any best practices for inserting dummy records based on conditions in WhereScape Red. The granularity of Fact Table is at a Week Level for an Animal.The approach followed by me is:

Creation of dummyperiod table which has got rows from 1 to 12
Identification of Animal(AnimalIdentifierKey) in the Fact table which is having count distinct Period < 12 for an Year
(Year is not available in FactTable, is derived from DimDate table)
Selection of DateKey and Period for the AnimalIdentifierKeys from the above steps
Inserting to the FactTable, of the 5 unique index in Fact table the missing ActPeriod will be inserted for the animal.

I got issues in selection of Animals and Selection of datekey and period using custom procedure. Any inputs on resolving this is highly appreciated. We are on Wherescape 5.6 verion with SQL Server 2008.

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] SQL Server Inserting Dummy Rows to Fact Table for each id's for a month if the id is missing for the Month

I am developing a Data Mart for Meat Slaughter Statistics Using SQL Server and WhereScape Red.I got a situation where I have to insert a dummy record for a month for an animal (eg Lambs slaughtered) with 0's and Nulls in the Calculated and other fields if that animal type is missing for the month. This is essentially to handle the reports to display 0 values even if they are missing for the month. I got a unique index in fact table for 5 fields(DTLS_DOC_XREF,ACCNBRI,TransNbr,PRINT_SEQ_NBR,ActPeriod) in the table. I got a WeekEndDateKey in the Fact table that can be joined to DimDate table to arrive at the dates. Is there a way I can attain this without much hastle in WhereScape Red? Currently I am building a custom procedure for this, but not able to attain the desired output. Is there any best practices for inserting dummy records based on conditions in WhereScape Red. The granularity of Fact Table is at a Week Level for an Animal.The approach followed by me is:

Creation of dummyperiod table which has got rows from 1 to 12
Identification of Animal(AnimalIdentifierKey) in the Fact table which is having count distinct Period < 12 for an Year
(Year is not available in FactTable, is derived from DimDate table)
Selection of DateKey and Period for the AnimalIdentifierKeys from the above steps
Inserting to the FactTable, of the 5 unique index in Fact table the missing ActPeriod will be inserted for the animal.

I got issues in selection of Animals and Selection of datekey and period using custom procedure. Any inputs on resolving this is highly appreciated. We are on Wherescape 5.6 verion with SQL Server 2008.

.

social.technet.microsoft.com/Forums