where to find the {script/stored procedure} in SQL SERVER DATAWAREHOUSE which populated DimTime table

All Replies

• Tuesday, December 11, 2012 5:27 AM

   

   
  

  0

  Hi,

  1) Search in Database->programmability->stored procedure

  What are the columns you have in your time dimensions?

  Thanks,
  Vipin

  • Reply
  • Quote

   
• Wednesday, December 12, 2012 10:35 AM

   

   
  

  3

  There are 4 ways your DimTime might have been generated

  1. Manually as a physical table prior to OLAP.  Created manually as a table prior to ssas and populated with a script / stored proc.
  2. As a view prior to OLAP.  A view based on the data used within your Facts so that the dimension matches the true date range.
  3. From SSAS.  SSAS has an option to "Generate a time table in data source" which results in a physical table being generated outside of your OLAP environment prepopulated with a data range
  4. Within SSAS only.  SSAS can generate a table within your OLAP environment by the dimension option "Generate a time table on the server".  This will not have a supporting database table.

  Options 2 and 4 are unlikely in your case if you can see a physical database table.  If option 3 has been used, you will need to generate a script to insert records to extend the table range.

  If you cannot find any exising script /  SP, then tailor the folowing to your table definition;

  	DECLARE @CurrentDate date, @StartDate date = '20130101',@DateEnd date = '20140101'
  	SELECT @CurrentDate =@StartDate
  	
  	WHILE @CurrentDate <= @DateEnd
  	BEGIN 
  		INSERT INTO DateDim2  (Id,  SqlDate, name, SqlWeek, week, SqlMonth, month, year, DayOfWeek, Weekend) 
  		VALUES	( CONVERT(VARCHAR(8), @CurrentDate,112) -- id
  				,  @CurrentDate --sql date
  				, CONVERT(VARCHAR(11), @CurrentDate,106) --name
  				, DATEPART(year, @CurrentDate) * 100 + DATEPART(wk, @CurrentDate) --sqlweek
  				, convert(varchar(4),DATEPART(year, @CurrentDate)) + ' Wk:' + convert(varchar(2), DATEPART(wk, @CurrentDate)) --week
  				, DATEPART(year, @CurrentDate) * 100 + DATEPART(month, @CurrentDate) --sqlmonth
  				, convert(varchar(4),DATEPART(year, @CurrentDate)) + ' Mth:' + convert(varchar(2), DATEPART(month, @CurrentDate)) --month
  				, DATEPART(year, @CurrentDate) 
  				, DATEPART(dw, @CurrentDate) 
  				, CASE WHEN DATEPART(dw, @CurrentDate) in (1,7) Then 1 ELSE 0 END --Sunday and Saturday
  				) --year
  				
  		SET		@CurrentDate = dateadd(day, 1, @CurrentDate)
  	END

  
  

  • Marked As Answer by Testing1.1 Tuesday, December 18, 2012 10:16 PM
  •  
  • Reply
  • Quote

   
• Monday, December 17, 2012 3:12 PM

   

   
  

  1

  Hi,

  • first of all, I would suggest, you need to pre-populate the time dimension well ahead, for multiple years may be for another 20-30 years. Its not a huge data less than a MB may be.This dimension is a one time population dimension.
    
  • You can find the SP that you used to populate that table is as below. This will give you all the objects that refer to this table.

  You can then find out which SP was used.
  

  select distinct OBJECT_NAME(id) from sys.syscomments
  where text like '%DimTime%'

  • Proposed As Answer by Dattatray Nale Monday, December 17, 2012 3:13 PM
  •  
  • Reply
  • Quote

   
• Tuesday, December 18, 2012 10:54 PM

   

   
  

  0

  Hi Daryl,

  Thanks for your reply. Below is my DimTime table definition.

  CREATE TABLE [dbo].[DimTime](
  [PK_Date] [datetime] NOT NULL,
  [Date_Name] [nvarchar](50) NULL,
  [Year] [datetime] NULL,
  [Year_Name] [nvarchar](50) NULL,
  [Half_Year] [datetime] NULL,
  [Half_Year_Name] [nvarchar](50) NULL,
  [Quarter] [datetime] NULL,
  [Quarter_Name] [nvarchar](50) NULL,
  [Month] [datetime] NULL,
  [Month_Name] [nvarchar](50) NULL,
  [Week] [datetime] NULL,
  [Week_Name] [nvarchar](50) NULL,
  [Day_Of_Year] [int] NULL,
  [Day_Of_Year_Name] [nvarchar](50) NULL,
  [Day_Of_Half_Year] [int] NULL,
  [Day_Of_Half_Year_Name] [nvarchar](50) NULL,
  [Day_Of_Quarter] [int] NULL,
  [Day_Of_Quarter_Name] [nvarchar](50) NULL,
  [Day_Of_Month] [int] NULL,
  [Day_Of_Month_Name] [nvarchar](50) NULL,
  [Day_Of_Week] [int] NULL,
  [Day_Of_Week_Name] [nvarchar](50) NULL,
  [Week_Of_Year] [int] NULL,
  [Week_Of_Year_Name] [nvarchar](50) NULL,
  [Month_Of_Year] [int] NULL,
  [Month_Of_Year_Name] [nvarchar](50) NULL,
  [Month_Of_Half_Year] [int] NULL,
  [Month_Of_Half_Year_Name] [nvarchar](50) NULL,
  [Month_Of_Quarter] [int] NULL,
  [Month_Of_Quarter_Name] [nvarchar](50) NULL,
  [Quarter_Of_Year] [int] NULL,
  [Quarter_Of_Year_Name] [nvarchar](50) NULL,
  [Quarter_Of_Half_Year] [int] NULL,
  [Quarter_Of_Half_Year_Name] [nvarchar](50) NULL,
  [Half_Year_Of_Year] [int] NULL,
  [Half_Year_Of_Year_Name] [nvarchar](50) NULL,
  [GL_YEAR] [varchar](50) NULL,
  [GL_PERIOD] [varchar](50) NULL,
  [GL_MONTH] [varchar](50) NULL,
  [GL_QUARTER] [varchar](50) NULL,
  CONSTRAINT [PK_Time1] PRIMARY KEY CLUSTERED 
  (
  [PK_Date] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]

  below is the sample data from DimTime table.

  Please suggest me any idea to extend the DimTime table range. 
  

  • Reply
  • Quote

   
• Tuesday, December 18, 2012 10:55 PM

   

   
  

  0
  
  • Reply
  • Quote