Hey there, when trying to create a conformed dimension in a dimensional model, say you have several data sources from the applications with the same information but the data is saved and changes at a different grain in the source systems. The descriptors are the same but some have more information than others. Would you still try to create a conformed dimension from these sources? Or separate them? For example, take the following 3 table variables to mimic what's going on. THis is actually a lot cleaner than how the data is in the systems:
DECLARE @RouteRidership TABLE (RouteId INT, RouteAbbr VARCHAR(30), RouteName VARCHAR(50))
DECLARE @CADRoutes TABLE (RouteId INT, RouteAbbr VARCHAR(50), RouteName VARCHAR(75), Direction VARCHAR(5))
DECLARE @SchRoutes TABLE (RouteId INT, RouteAbbr VARCHAR(30), RouteName VARCHAR(40), ScheduleId INT)
INSERT INTO @RouteRidership (RouteId, RouteAbbr, RouteName)
VALUES
(1, 'BRT', 'Bus Rapid Transit')
,(2, 'Main', 'Main Street Shuttle')
INSERT INTO @CADRoutes (RouteId, RouteAbbr, RouteName, Direction)
VALUES
(32, ' BRT', ' Bus Rapid Transit', 'NB')
,(33, ' BRT', ' Bus Rapid Transit', 'SB')
,(34, ' Main', ' Main Street Shuttle', 'EB')
,(35, ' Main',' Main Street Shuttle', 'WB')
INSERT INTO @SchRoutes (RouteId, RouteAbbr, RouteName, ScheduleId)
VALUES
(13, 'BRT', 'BRT', 1)
,(14, 'BRT', 'BRT', 2)
,(15, 'BRT', 'BRT', 3)
,(28, 'Main', 'Main',1)
,(42, 'Main', 'Main',2)
,(55, 'Main', 'Main',3)
You'll notice that the data is similar, but the first table has a row for every route, the second has a row for the route and direction and the last only has the route, but it changes with every new schedule (schedule id). I've thought about getting them all to the level of the first source just so we could have every version of a route name from all the sources at the route level, but where it changes based on periods...Route is a central attribute in our business and we want to use common names, but the facts will be very different from one business area to another.
Thanks!