[SQL Server Data Warehousing] Design Pattern Question: Conformed dim used by facts sourced from different systems - inconsistent/reused dim reference keys

Hi all,

I have a location dimension that is sourced from our GIS system

I have a number of fact tables that are sourced from four different systems that I want to join to my location dimension

Only one of the fact table systems references the natural key of the GIS system so no trouble there.

The problem is the remaining 3 fact sources. I plan on using a mapping table to map the GIS natural key to the various disparate and potentially duplicate location references from the 3 fact sources. But I can't think of a way to handle inferred members for entries that don't exist in the mapping table.

What are some of the design patterns to use in this situation?

The simplest one I can think of would be to have either add one global placeholder '-2|Not In Mapping Table|unkown|unkown|unkown' dim record or one for each source .. But that would mean complexity down the line to fix the fact records once I added the mapping record. I'd need to either reload all my facts or do some other complex 'data fix' query to gloss over the fact that i'm not materialising inferred dimensions directly in the dim table. Something I'd rather avoid

Another is creating a new 'natural' key based on the fact source system + its location pair, and use that in the dim and mapping tables. But this'll create a similar problem to above when I add the entry to the mapping table for an inferred member and it points to an existing GIS sourced location. I'd have "logical" duplicates.

Are there any other approaches? any pitfalls of the above that I need to be aware of?

Thanks

---

Adelaide, Australia

.

social.technet.microsoft.com/Forums

[SQL server issues] Created Issue: New to datawarehousing...and want to know how the Adventurework and AdventureworkDW has been developed [17881]

description

New to datawarehousing...and want to know how the Adventurework and AdventureworkDW has been developed..
how the OLTP data has been extracted to AdventureworksDW..datawarehouse system and need some design documents....what to know how the DIM tables are created...and everything

Sign in to add a comment or to set email notifications

.

sqlserversamples.codeplex.com

[SQL server issues] Created Issue: SQL Server 2008 Developer edition sample database install error [18174]

An unexpected error has occured.

description

I'm trying to install Adventureworks sample dB and i get an error.
I successfully downloaded the mdf file however when attach, add and click OK from SSMS I get the error:
 
Msg 5172, Level 16, State 15, Line 1
The header for file 'C:\Program Files\Microsoft SQL Server\AdventureWorks2012_Data.mdf' is not a valid database file header. The FILE SIZE property is incorrect.

.

sqlserversamples.codeplex.com

[SQL server issues] Created Issue: AdventureWorksDW2012 Download file corrupt [19063]

There seams to be an issue with the AdventureWorksDW2012 Data file download. When I try to run this SQL statement:

CREATE DATABASE AdventureWorksDW2012 ON (FILENAME = 'E:\MSSQL11.SQL02\MSSQL\DATA\AdventureWorksDW2012_Data.mdf') FOR ATTACH_REBUILD_LOG;

This is the error I get:

Msg 5172, Level 16, State 15, Line 1
The header for file 'E:\MSSQL11.SQL02\MSSQL\DATA\AdventureWorksDW2012_Data.mdf' is not a valid database file header. The FILE SIZE property is incorrect.

Thanks,Dan

There seams to be an issue with the AdventureWorksDW2012 Data file download. When I try to run this SQL statement:

CREATE DATABASE AdventureWorksDW2012 ON (FILENAME = 'E:\MSSQL11.SQL02\MSSQL\DATA\AdventureWorksDW2012_Data.mdf') FOR ATTACH_REBUILD_LOG;

This is the error I get:

Msg 5172, Level 16, State 15, Line 1
The header for file 'E:\MSSQL11.SQL02\MSSQL\DATA\AdventureWorksDW2012_Data.mdf' is not a valid database file header. The FILE SIZE property is incorrect.

Thanks,Dan

.

sqlserversamples.codeplex.com

[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] Team Foundation Server Issue Work Items not synching to warehouse - TF221122: An error occurred running job Work Item Tracking Warehouse Sync for team project collection or Team

custom field were added to the work item template

[_WitFields]

SK ReferenceNameDisplayName DataType Filedname
139 Acq.TaskSourceSource 162 10083

Values in Field 10083

fld10083
Internal/Tech
NULL
QA
Business Request

This is where the failure is

Exception Message: Conversion failed when converting the nvarchar value 'Business Request' to data type int. Cannot find the object "#workingSet" because it does not exist or you do not have permissions. (type SqlException) SQL Exception Class: 16 SQL Exception Number: 50000 SQL Exception Procedure: prc_Dimension_DimWorkItem_AddUpdate 

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] Forms templates for documenting DW analysis & design, + naming conventions

A small DW project is on the horizon & I will be meeting with a key skate-holder to understand what they are wanting. I would like to have a set document templates to help prompt me to ask the right questions, use as a checklist & reflect back to this person & other users my understanding of what they want.

Can anyone recommend a set of document templates for DW analysis & design?

I would also like to have some standards for naming conventions for DB tables, SSIS objects, SSAS objects etc. So if anyone could refer me to some it would be appreciated.

Thanks in advance.

.

social.technet.microsoft.com/Forums