none
Star schema or Data warehouse Desgin

    Question

  • Hi All,

    I have the following query, and there are so many queries like this with different codes for different reports etc.. I am thinking to design some reference tables(Master data) to reduce the Case statements in these queries and thinking to create star schema dimensional model. Would it be a good idea? or is there any other best way to achieve the result? The problem is if any new code will come in we need to amend these queries manually. So I want to load this master data on weekly basis. What would be the best approach to achieve the result without changing report queries? Any Suggestions?

    SELECT 
    
      (CASE a.Stage				WHEN 'IC  Investigations'							THEN 'Adjudications'
    							WHEN 'IC Investigations'							THEN 'Adjudications'
    							WHEN 'Investigations New Rules'						THEN 'Adjudications'
    							WHEN 'In the form required'							THEN 'Adjudications'
    							WHEN 'Restoration - Awaiting Hearing'				THEN 'Restoration open'
    							WHEN 'Restoration - Awaiting Referees response'		THEN 'Restoration open'
    							WHEN 'Restoration - Awaiting Summary'				THEN 'Restoration open'
    							WHEN 'Restoration - New'							THEN 'Restoration open'
    							WHEN 'Awaiting HC Hearing'							THEN 'Adjudications'
    							WHEN 'Awaiting HC Meeting'							THEN 'Adjudications'
    							WHEN 'HC Hearing Pre Schedule'						THEN 'Adjudications'
    							WHEN 'HC Meeting Pre Schedule'						THEN 'Adjudications'
    							WHEN 'HC Substantive Order Review'					THEN 'Adjudications'
    							WHEN 'HC Pre NOR'									THEN 'Adjudications'
    							WHEN 'Awaiting CCC Hearing Schedule'				THEN 'Adjudications'
    							WHEN 'Awaiting CCC Adjourned Hearing'				THEN 'Adjudications'
    							WHEN 'Awaiting CCC Hearing'							THEN 'Adjudications'
    							WHEN 'Awaiting CCC Meeting'							THEN 'Adjudications'
    							WHEN 'Awaiting Pre Meeting'							THEN 'Adjudications'
    							WHEN 'CCC - NOR Investigations'						THEN 'Adjudications'
    							WHEN 'CCC Meeting Pre Schedule'						THEN 'Adjudications'
    							WHEN 'CCC Post NOR Legal Review'					THEN 'Adjudications'
    							WHEN 'CCC Pre NOR'									THEN 'Adjudications'
    							ELSE A.Stage -- for In the form required stage or other stage 
    																		
    							END) as Stage,
    
     (CASE a.CaseEventType		WHEN 'CCC Interim Orders Review Meetings'				THEN 'CCC'
    							WHEN 'CCC New Interim Order'							THEN 'CCC'
    							WHEN 'CCC NOR2'											THEN 'CCC'
    							WHEN 'CCC Pre Meeting'									THEN 'CCC'
    							WHEN 'CCC Restoration Hearing'							THEN 'CCC'
    							WHEN 'CCC Review Interim Order'							THEN 'CCC'
    							WHEN 'CCC Substantive Hearing'							THEN 'CCC'
    							WHEN 'CCC Substantive Meeting'							THEN 'CCC'
    							WHEN 'HC Interim Orders Review Meetings'				THEN 'HC'
    							WHEN 'HC New Interim Order'								THEN 'HC'
    							WHEN 'HC NOR2'											THEN 'HC'
    							WHEN 'HC Pre Meeting'									THEN 'HC'
    							WHEN 'HC Review Interim Order'							THEN 'HC'
    							WHEN 'HC Substantive Hearing'							THEN 'HC'
    							WHEN 'HC Substantive Meeting'							THEN 'HC'
    							WHEN 'HC Substantive Order Review Hearing'				THEN 'HC'
    							WHEN 'IC2'												THEN '1.0 IC2/4 - Check split results 1.1'
    							WHEN 'IC3'												THEN '1.0 IC3/5 - Check split results 1.1'
    							WHEN 'IC4'												THEN '1.0 IC2/4 - Check split results 1.1'
    							WHEN 'IC5'												THEN '1.0 IC3/5 - Check split results 1.1'
    							WHEN 'IC Substantive Hearing'							THEN '1.0 ICSH/M - Check split results 1.1'
    							WHEN 'IC Substantive Meeting'							THEN '1.0 ICSH/M - Check split results 1.1'
    						
    							ELSE A.CaseEventType -- for In the form required stage or other stage,						
    							
          END) AS 'Adjudications Case EventType',
          
    
          
    SUM(CASE WHEN a.Age = 0 THEN 1 ELSE 0 END)					'0 months',
    SUM(CASE WHEN a.Age = 1 THEN 1 ELSE 0 END)					'1 months',
    SUM(CASE WHEN a.Age = 2 THEN 1 ELSE 0 END)					'2 months',
    SUM(CASE WHEN a.Age = 3 THEN 1 ELSE 0 END)					'3 months',
    SUM(CASE WHEN a.Age = 4 THEN 1 ELSE 0 END)					'4 months',
    SUM(CASE WHEN a.Age BETWEEN 5 AND 6 THEN 1 ELSE 0 END)		'5 to 6 months',
    SUM(CASE WHEN a.Age BETWEEN 7 AND 9 THEN 1 ELSE 0 END)		'7 to 9 months',
    SUM(CASE WHEN a.Age BETWEEN 10 AND 11 THEN 1 ELSE 0 END)	'10 to 11 months', 
    SUM(CASE WHEN a.Age = 12 THEN 1 ELSE 0 END)					'12 months', 
    SUM(CASE WHEN a.Age BETWEEN 13 AND 14 THEN 1 ELSE 0 END)	'13 to 14 months', 
    SUM(CASE WHEN a.Age = 15 THEN 1 ELSE 0 END)					'15 months', 
    SUM(CASE WHEN a.Age BETWEEN 16 AND 17 THEN 1 ELSE 0 END)	'16 to 17 months', 
    SUM(CASE WHEN a.Age = 18 THEN 1 ELSE 0 END)					'18 months', 
    SUM(CASE WHEN a.Age BETWEEN 19 AND 24 THEN 1 ELSE 0 END)	'19 to 24 months',
    SUM(CASE WHEN a.Age > 24 THEN 1 ELSE 0 END)					'> 24 months',
    SUM(CASE WHEN a.Age >=0 THEN 1 ELSE 0 END)					'Total'
    
    
    
    
    FROM 
    
    (
    SELECT DISTINCT (CH.CaseReference),  
    --PP.FirstName, 
    --PP.Surname, 
    --PP.WISERPINNo, 
    --CH.ReceivedDate, 
    Datediff(Month, CH.ReceivedDate, getdate()) As Age,
    CST.ItemName as Stage,
    --CT.CaseTypeName, 
    --CU.assigneddate, 
    --UserName, 
    --te.TeamName as Teamname, 
    --te.teamid, 
    --UserDetail.UserDetailId,
    P.CaseReference as CaseReference2, 
    P.CaseEventType as CaseEventType, 
    P.StartDateTime
    --P.Outcome : to remove duplicates
    	
    	FROM CaseHeader CH
    	left outer JOIN LookupItem CST
    		on CST.LookupItemID = CH.CaseStageLKID 
    		--AND CST.ItemName NOT IN ('Triage' , 'Closed')
    		
    	JOIN LookupItem LI 
    		ON CH.CaseStateLKID=LI.LookupItemID
    		AND LI.ItemName In ('Open')
    		
    	JOIN CaseType CT
    		ON CT.CaseTypeID = CH.CaseTypeID
    		--AND CT.CaseTypeName = 'Referral'
    		
    LEFT JOIN CaseParty CP
    		ON  CH.CaseHeaderID = CP.CaseHeaderID
    		AND CP.IsDeleted = 0 
    		AND ProfileID = '1ADE1193-F979-4969-889A-D121CD0FB652'
    		
    LEFT  JOIN PartyPerson PP
    		ON  CP.PartyID = PP.PartyID 
    		AND PP.IsDeleted = 0
    		
    	JOIN CaseUser CU
    		ON CH.CaseHeaderId = CU.CaseHeaderId
    		AND CU.AssignedDate =
    							(SELECT		Max(CU.AssignedDate)
    							 FROM		CaseUser CU		
    							 WHERE		CU.IsDeleted = 0
    							 AND		CU.CaseHeaderId = CH.CaseHeaderId
    							 AND		CU.CaseRoleTypeLKId = 'fd843c4e-093b-4eeb-adf3-18903a0c46dd')
    	JOIN UserDetail 
    		ON CU.UserDetailId = UserDetail.UserDetailId
    		
    	JOIN LookupItem 
    		ON LookupItem.LookupItemId = CU.CaseRoleTypeLKId
    		
    	JOIN CaseTeamHistory CTH
    		ON CTH.CaseHeaderID = Ch.CaseHeaderId
    		AND CTH.AssignedDate =
    								(SELECT		Max(CTH.AssignedDate)
    								 FROM		CaseTeamHistory CTH		
    								 WHERE		CTH.IsDeleted = 0
    								 AND		CTH.CaseHeaderId = CH.CaseHeaderId)				
    
    	JOIN TeamMember TM
    		ON TM.UserDetailId = UserDetail.UserDetailId 
    			AND TM.IsDeleted = '0'
    			AND TM.TeamId in (	'0C60877E-61A8-4564-B58C-E54766F89B98',	
    								'0ED0B06B-FB21-4A6F-A278-FBFF2D71FA23',	
    								'23BB6E3D-3E28-4CFA-B6D5-3C2DA97C3326',
    								'A25B93A6-1261-4A2D-AE0E-4D2637342CBC') ---- Reflects cases only allocated to the Case Preparations Team via System Admin only, not under the case details.
    	JOIN Team TE
    		ON TE.TeamId = TM.TeamId
    		
    ---- Joining to the latest event and outcomes for each case, but one off case will not have events scheduled e.g. in the form required stage
    
    LEFT JOIN ( SELECT 
    
    D.CaseReference, D.CaseEventType, D.StartDateTime, D.Outcome
    
    FROM
    
    (
    SELECT	 rk=rank () over (partition by ch.CaseReference order by ch.CaseReference, E11.StartDateTime desc),	 
    ch.CaseReference, Li.ItemName as CaseEventType, e11.StartDateTime, LI1.ItemName as Outcome
     
     FROM		
    	 Event e11
    
    Join CaseEvent ce
    	on ce.EventId = e11.EventId
    	and ce.IsDeleted = 0
    	
    Join CaseHeader ch
    	on ch.CaseHeaderId = ce.CaseHeaderId
    	and ch.IsDeleted = 0
    
    Join LookupItem LI
    	on Li.lookupitemid = ce.CaseEventTypeLKId
    	and LI.IsDeleted = 0
    
    left outer Join CaseEventOutcome CEO
    	ON CE.CaseEventId = CEO.CaseEventId
    	AND CEO.IsDeleted = 0
    
    left outer Join LookupSelection LS
    	ON LS.EntityId = CEO.OutcomeLKSid
    	and LS.IsDeleted = 0	
    	
    left outer Join LookupItem LI1
    	ON LI1.LookupItemId = LS.LookupItemId
    	AND LI1.ISDeleted = 0		
    
    Where 						 
    					
     e11.EventStatusLKId <> '30F7ACE1-E6F9-4D72-B877-422D9B023895'-- FOR SMT REPORT EVENT OUTCOMES NOT INCLUDING CANCELLED EVENTS.							 
    
    and Ch.CaseStateLKId = '6fa9d572-3424-47cf-90d7-2d2b3c6080c5'--: for open cases only)
    and LI.ItemName not in ('IC Interim Order - Review', 'IC Interim Orders Review Meetings', 'IC Interim Order - New')
    ) D
    
    where rk = 1 ) P
    
    ON P.CaseReference = CH.CaseReference
    
    
    -------------------------sub query ends -----------------------------------------------------------------
    
    		
    WHERE UserDetail.IsDeleted = 0
     AND CH.CaseHeaderID not  IN (SELECT CH1.CaseHeaderID FROM CaseHeader CH1
    									INNER JOIN CaseEvent CE
    										ON CH1.CaseHeaderID = CE.CaseHeaderID
    										AND CE.IsDeleted = 0
    									INNER JOIN Event ET
    										ON CE.EventID = ET.EventID
    										AND ET.IsDeleted = 0
    									INNER JOIN CaseEventOutcome CEO
    										ON CEO.CaseEventID = CE.CaseEventID
    										AND CEO.IsDeleted = 0
    									INNER JOIN LookupSelection LS
    										ON LS.EntityID = CEO.OutcomeLKSID
    										AND LS.IsDeleted = 0
    									INNER JOIN LookupItem LI1
    										ON LI1.LookupItemID = LS.LookupItemID
    										AND LI1.ItemCode In ('RTCA','CO83',	'CO98',	'CO54',	'CO68',	'ORD1', 
    															'CO57',	'CO86',	'CO71',	'CF47',	'EO02',	'CE77',	
    															'CE67',	'CF37',	'CO83',	'CO98',	'CO54',	'CO68',	
    															'ORD1',	'CF44',	'CE74',	'CE64',	'CF34', 'SUSP',	
    															'CE76',	'CE66',	'CF36',	'CO84',	'CO55',	'CF45',	
    															'CO99',	'ORD2',	'CO69',	'EO28',	'RD84',	'EO23',	
    															'EO18',	'CE65',	'CF35',	'CE75',	'CO12',	'CE29',	
    															'CE2',	'CE13',	'CE10',	'CE25',	'CCO8',	'CSSP',	
    															'REA5',	'CF46',	'CO56',	'EO01',	'ORD3',	'CO70',	
    															'CO85',	'CF31',	'CO89',	'CE61',	'CE71',	'CO59',	
    															'CF41',	'CO60',	'CE72',	'CE62',	'CF42',	'CO90',	
    															'CF32',	'CE73',	'CO61',	'CF33', 'CO91',	'CF43',	
    															'CE63', 'CO67', 'CO53', 'CO97', 'CO82', 'CE26',
    															'CE30', 'CO14', 'CE27', 'CE31', 'CE26', 'CE30', 
    															'CO13', 'EO16', 'EO21', 'EO26', 'RD84', 'OR02', 
    															'EO17', 'EO18', 'EO27', 'EO22', 'CO67',	'CO53',	
    															'CO97',	'CO82',	'EO21',	'EO16',	'EO26'	,'RD83',
    															'EO17'	,'EO27','EO22', 'CO81', 'CO96',	'CO52',	
    															'CO66',	'CREJ',	'CO95', 'CO51',	'CO65',	'CO80',	
    															'CO58',	'EO03',	'CO73',	'CO87', 'HCB', 'PCCG',
    															'PCC4',	'PCC2',	'PCC3',	'PCCI', 'PCC7', 'PCC8',
    															'PCC5',	'PCC6',	'PCCE',	'PCC0',	'PCCD',	'PCCA',	
    															'PCCG',	'PCCC', 'VR1A',	'VRSH',	'VRS1','VRSM', 'CO12','CE25','CE29',
    															'EOA1', 'EOA3', 'EOB1', 'RDA1'))
    															
    
    ) A
    
    GROUP BY (CASE a.CaseEventType		WHEN 'CCC Interim Orders Review Meetings'				THEN 'CCC'
    									WHEN 'CCC New Interim Order'							THEN 'CCC'
    									WHEN 'CCC NOR2'											THEN 'CCC'
    									WHEN 'CCC Pre Meeting'									THEN 'CCC'
    									WHEN 'CCC Restoration Hearing'							THEN 'CCC'
    									WHEN 'CCC Review Interim Order'							THEN 'CCC'
    									WHEN 'CCC Substantive Hearing'							THEN 'CCC'
    									WHEN 'CCC Substantive Meeting'							THEN 'CCC'
    									WHEN 'HC Interim Orders Review Meetings'				THEN 'HC'
    									WHEN 'HC New Interim Order'								THEN 'HC'
    									WHEN 'HC NOR2'											THEN 'HC'
    									WHEN 'HC Pre Meeting'									THEN 'HC'
    									WHEN 'HC Review Interim Order'							THEN 'HC'
    									WHEN 'HC Substantive Hearing'							THEN 'HC'
    									WHEN 'HC Substantive Meeting'							THEN 'HC'
    									WHEN 'HC Substantive Order Review Hearing'				THEN 'HC'
    									WHEN 'IC2'												THEN '1.0 IC2/4 - Check split results 1.1'
    									WHEN 'IC3'												THEN '1.0 IC3/5 - Check split results 1.1'
    									WHEN 'IC4'												THEN '1.0 IC2/4 - Check split results 1.1'
    									WHEN 'IC5'												THEN '1.0 IC3/5 - Check split results 1.1'
    									WHEN 'IC Substantive Hearing'							THEN '1.0 ICSH/M - Check split results 1.1'
    									WHEN 'IC Substantive Meeting'							THEN '1.0 ICSH/M - Check split results 1.1'
    									ELSE A.CaseEventType -- for In the form required stage or other stage 
    							
          END),
          (CASE a.Stage			WHEN 'IC  Investigations'							THEN 'Adjudications'
    							WHEN 'IC Investigations'							THEN 'Adjudications'
    							WHEN 'Investigations New Rules'						THEN 'Adjudications'
    							WHEN 'In the form required'							THEN 'Adjudications'
    							WHEN 'Restoration - Awaiting Hearing'				THEN 'Restoration open'
    							WHEN 'Restoration - Awaiting Referees response'		THEN 'Restoration open'
    							WHEN 'Restoration - Awaiting Summary'				THEN 'Restoration open'
    							WHEN 'Restoration - New'							THEN 'Restoration open'
    							WHEN 'Awaiting HC Hearing'							THEN 'Adjudications'
    							WHEN 'Awaiting HC Meeting'							THEN 'Adjudications'
    							WHEN 'HC Hearing Pre Schedule'						THEN 'Adjudications'
    							WHEN 'HC Meeting Pre Schedule'						THEN 'Adjudications'
    							WHEN 'HC Substantive Order Review'					THEN 'Adjudications'
    							WHEN 'HC Pre NOR'									THEN 'Adjudications'
    							WHEN 'Awaiting CCC Hearing Schedule'				THEN 'Adjudications'
    							WHEN 'Awaiting CCC Adjourned Hearing'				THEN 'Adjudications'
    							WHEN 'Awaiting CCC Hearing'							THEN 'Adjudications'
    							WHEN 'Awaiting CCC Meeting'							THEN 'Adjudications'
    							WHEN 'Awaiting Pre Meeting'							THEN 'Adjudications'
    							WHEN 'CCC - NOR Investigations'						THEN 'Adjudications'
    							WHEN 'CCC Meeting Pre Schedule'						THEN 'Adjudications'
    							WHEN 'CCC Post NOR Legal Review'					THEN 'Adjudications'
    							WHEN 'CCC Pre NOR'									THEN 'Adjudications'
    							ELSE A.Stage -- for In the form required stage or other stage 
    																		
    							END)
    							
    	Order by Stage
    

    Many thanks,

    MH

     

    MH

    Monday, November 18, 2013 3:01 PM

Answers

All replies

  • You should add new lookup items for those codes instead of CASE statements.

    Monday, November 18, 2013 3:55 PM
  • Sounds like a viable way. But you need to consider that dimensional modelling is quite different than normalized relational models. And it is not a fast solution.

    I think you should take a look (commercials ahead): Kimball or Inmon.

    At least start reading The Data Warehouse Lifecycle Toolkit. It explains the business decisions behind enterprise reporting based on DW/BI (data warehouse/business intelligence). It's giving you the necessary arguments for building a DW/BI solution.

    The key is that you automate the new code additions in the ETL stage. Cause these code translations are stored in the dimension tables you don't need to translate them anymore in your report's SQL statements.

    An interims solution may be a simple translation table, so that you can use a JOIN on the codes instead of calculating it with a CASE expression.

    Monday, November 18, 2013 4:03 PM
  • Thank you so much Tom and Stefan for your prompt reply. One more question, To calculate the age would it be a good idea to create date dimension table and join the date with received date to get the age instead of using datediff function and the grouping on the basis of number of months?

    MH

    Monday, November 18, 2013 4:08 PM
  • To calculate the age would it be a good idea to create an Age dimension table of the form DimAge (Age int, AgeBucket varchar(20)).

    This is unrelated to whether you use datediff() to compute the age, or recalculate all ages nightly.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, November 18, 2013 4:28 PM
  • Thanks David for your reply. What is AgeBucket? Do you mean age bracket on the basis of age? I need to join this with case header to calculate the age, How would you do that if I will not have any link in DimAge?

    MH

    Monday, November 18, 2013 4:52 PM
  • Your case header needs an Age, whether calculated or not.  Typically these are called "Age Buckets".

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, November 18, 2013 5:25 PM