6

Sample data with the Date column typed as date: CREATE TABLE dbo.Staff ( [ID] integer NOT NULL, [Surname] varchar(5) NULL, [FirstName] varchar(4) NULL, [Office] varchar(9) NULL, [Date] date NOT NULL, PRIMARY KEY (ID, [Date]) ); INSERT INTO Staff ([ID], [Surname], [FirstName], [Office], [Date]) VALUES (001, 'Smith', 'Bill', 'Melbourne', '...


5

Making SSAS hierarchies on slowly changing dimensions is a bit of a fiddle. You need to make surrogate keys for each historical version at each level of the hierarchy. Then the key has the actual business facing name, which the user selects or reports by. As an example, imagine worker BloggsJ in Division1, which is in LineOfBusiness1. Now Division1 gets ...


4

I was able to do this with a recursive CTE so it's not that different from a cursor. Also these do not tend to scale well over large volumes. Have a look through the code and see what you think. ;WITH cte AS ( SELECT 0 x, Change, ID, Surname, FirstName, Office, [Date] FROM dbo.Staff WHERE Change = 0 UNION ALL SELECT x + 1, s.Change, c.ID, ISNULL( s....


4

Checking whether the dates are contiguous You don't say which DBMS you're using here, but you're using SSAS so I'm guessing SQL Server. If you're on a recent enough version, using LAG and LEAD in window functions can be really handy for this kind of task. You can order the rows by the start date or by an incremental ID if you have one, and then use these to ...


3

As you are seeing, one of the benefits of Kimball Dimensional Modeling is that the data warehouse design is essentially your SSAS design. While there are always exceptions, you can typically select a table in the DSV and immediately move to hierarchy design, cube relationships etc. I'd recommend moving to the DDS with the caveat that you phase out the NDS. ...


3

You actually have 2 questions in one question. If you create a new question for the attributes it would be neater and I'll cut/paste half of this as an answer there :) Nullable Parent Level You probably don't want NULLs in your OLAP dimensions, and Kimball seems to agree. Nulls should also be avoided when we can’t provide a value for a dimension ...


3

If we're explicitly discussing Slowly Changing Dimensions in a Data Warehouse scenario and you're following the Kimball methodology, there's actually more than 2 different ways of doing this. The Kimball methodology has 7 different ways of recording SCDs (scroll down to Slowly Changing Dimension Techniques in the link). Types 1, 2 and 3 are apparently the ...


3

Usually, dimension tables contain a single valid time (start and end date) for all fields and SCD2 would apply to the complete record. It is good practice to use an non-null end value ahead in time to mark currently valid records as this simplifies queries. An end date in the past would signify deletion or any other semantic you define (like person left ...


2

Usually, we use SCD-type-4 when a dimension(SCD Type 2) grows rapidly due to the frequently changing of its attributes. These frequently changing attributes will be removed from the main dimension and added in to a new one known as Mini-Dimension. ==> Mini Dimension do not store the historical attributes, but the fact table preserved the history of ...


2

Does DimCategory have any Type 2 attributes that you are tracking? If it is not, then you would only need to add a new record to your bridge table based on whenever a Type 2 attribute in DimPerson changes since you will be inserting a new record in DimPerson with a new surrogate key. You would then need to add or update your bridge table with this new ...


2

You could use a window MAX() to find the date of the last column value so far, for each column: SELECT *, LastSurnameDate = MAX(CASE WHEN Surname IS NOT NULL THEN [Date] END) OVER (PARTITION BY ID ORDER BY [Date] ASC), LastFirstNameDate = MAX(CASE WHEN FirstName IS NOT NULL THEN [Date] END) OVER (PARTITION BY ID ORDER BY [Date] ASC), ...


2

Tuple versioning Will require two updates for each new piece of data - one to set the end date of the old row & one to insert the new row. The index(es) most likely will contain the interval dates making them wider and marginally less efficient. Historical queries (AS OF < date >) are simpler. DELETE can be a logical delete without further code. The ...


2

f you can find a way to make this more efficient/optimal, feel free to edit Without knowing anything about indexes etc, I tried filling up the staging table with some sample data: (FoodName,FoodCategory) values('Chicken','Meat') go 1000 insert into dbo.Stagingfood (FoodName,FoodCategory) values('Veal','Meat') go 10000 insert into dbo.Stagingfood (...


2

SQL Server has a hierarchyid type defined to deal with this kind of system. Consider this example: IF OBJECT_ID(N'dbo.Categories', N'U') IS NOT NULL DROP TABLE dbo.Categories; GO CREATE TABLE dbo.Categories ( CatID int NOT NULL CONSTRAINT PK_Categories PRIMARY KEY CLUSTERED IDENTITY(1,1) , hid hierarchyid NOT NULL , ...


2

Setup some sample data: create table fact_picked (Emp_Name varchar(30) ,[Date] date ,Apples_Picked int); insert into fact_picked values ('John','10/17/15',175), ('John','05/01/17',100); create table dim_company (Emp_Name varchar(30) ,Company varchar(30) ,Effective_Since date); insert into dim_company values ('John','...


1

I think that in order to keep the logical aspect of a dimension you should use a third table to relate both dimensions: PersonScd GroupOfCars CarScd --------- ------------- ------ PersonBK <---------> <-----> PK_Car Unique_Person_code ...


1

FYI, here's the answer I have at the moment using a while loop. Note, I've also added a column with the change id (which can easily be added using something like ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE) or similar. drop table if exists Staff; CREATE TABLE Staff( [Change] int, [ID] int, [Surname] varchar(5), [FirstName] varchar(4), [...


1

I wouldn't organize your "helper table" that way. I would keep only {Region, Salesman, StartDate}. To create a view of intervals, join that table to itself to create data ranges where a.StartDate < b.StartDate. Use an outer join, so that NULL represents an EndDate of "now", which you can coalesce with the current date or other, as appropriate. To ...


1

To make gender a freestanding "dimension" is, more likely than not, an attempt to normalize your dimensional model. Unless you have attributes associated with gender then how is it a dimension? Is it possible to have a conformed gender dimension? As for diagnosis are you modeling the process or the values? Very different requirements and issues to deal with....


1

I think, in your case a derived table is necessary to isolate querying number of mutations of postcodes per customer: SELECT c.postalcode , sum(s.SaleAmount) SaleAmount , count(postcode_mutations.customerid) as CntCustomerChangedPostCode FROM dbo.Sale s JOIN dbo.Customer c on s.customerid = c.customerid LEFT JOIN ( SELECT CustomerID FROM [dbo].[...


1

in the BIDS, open your project and double click on your DSV file, you can right click any where and add a table and then do the rest of steps like adding a relation look at images Step 1 Step 2 Is that what you meant


Only top voted, non community-wiki answers of a minimum length are eligible