13 votes

Best approach for populating date dimension table

UPDATE: for a more generic example of creating and populating a calendar or dimension table, see this tip: Creating a date dimension or calendar table in SQL Server For the specific question at hand,...
user avatar
6 votes

Consolidating a row of data, based on previous rows

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] ...
user avatar
  • 71k
5 votes
Accepted

MDX - Getting the last 6 month Dynamically

Assuming you have properly set the ValueColumn property of your CreatedOn attribute in your dimension (so that MemberValue comes back a date data type) then the following should work: select {[...
user avatar
  • 1,365
5 votes
Accepted

Time dimension or timestamp in fact table?

I would recommend including both on the fact table. The dimension should be used for filtering and grouping, while the time stamp value can be used in detailed reports/queries. Unless you care about ...
user avatar
5 votes

Is processing required dimensions enough or to process the entire cube?

I think you are confusing things. First of all, processing the cube and processing the dimensions is sort of separate. I think you mean "processing the entire database". If you process only ...
user avatar
  • 15.4k
5 votes

How to calculate a percentage over a dimension attribute and get correct aggregates?

Your main problem is that you are storing a measure in your dimension. You will keep running into issues with multiselect and or aggregation unless you store the measure where it belongs, namely in ...
user avatar
  • 15.4k
4 votes
Accepted

Consolidating a row of data, based on previous rows

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. ;...
user avatar
  • 10.2k
3 votes

How to optimize performance for dimension attributes?

MSDN states: The value of the AttributeHierarchyEnabled property determines whether an attribute hierarchy is created. If this property is set to False, the attribute hierarchy is not created and ...
user avatar
2 votes

Consolidating a row of data, based on previous rows

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 ...
user avatar
  • 21.2k
2 votes

Problem with Processing Dimensions with Multiple Facts

You need to process D1 and D5 in the same transaction. Then both will complete processing before measure groups are processed. Make sure you mark Transaction=true (the default unless you explicitly ...
user avatar
  • 1,365
2 votes

Error when only deploying a dimension on SSDT

I don't speak Portuguese very well but it looks as if it's saying that it can't connect to the datasource. Please verify if your Analysis services service account has access to the data source. ...
user avatar
  • 15.4k
2 votes

How to calculate a percentage over a dimension attribute and get correct aggregates?

Probably some MDX COUNT function would do the trick, but as the real fact table is somehow more complex and there is already a constant value of 1 identifying this specific rows among other related ...
user avatar
  • 161
2 votes

In star schemas, what should I do when a dimension has a date attribute?

Old question & I know the original posters are inactive, but since it came up in a Google search, I thought I'd add my thoughts. 1) Make Start_Date and End_Date regular, atomic attributes (...
user avatar
  • 121
2 votes
Accepted

How to handle NULL Dates in an "Accumulating fact table"

You say "null should be avoided in a foreign key column," but are these dates really FKs? Most likely, these dates are just attributes of an order, and if the dates are unknown (and the ...
user avatar
  • 14.4k
2 votes

How to store high-dimensional (N > 100) vectors and index for fast lookup by cosine similarity?

I'm not aware of any existing modules that will do this for you. It will be hard to index this in a traditional sense of jumping to a specific and small part of the index which covers all possibly-...
user avatar
  • 32.1k
1 vote
Accepted

SSAS Cube Only Displays Total Values

The Customer dimension is joined to the measure group at a non-key granularity. If the Customer Name attribute isn't related to the attribute you join on then data repeats in reports. You need to do ...
user avatar
  • 1,365
1 vote
Accepted

In SSAS Parent-Child-Hierarchy how to use MembersWithDataCaption

My problem was I used a translation (german) and left the translation for MemberWithDataCaption empty. An other pitfall is a formatstring without a '*' does not work. Answers: Set ...
user avatar
1 vote

How to sum measure value for all records between two dimension values

First you need two cube-dimensions because you cannot use one dimension on more than one axe of a MDX-select. You can use role-playing dimensions, that means there is only one "Week" dimension in the ...
user avatar
1 vote

Consolidating a row of data, based on previous rows

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 ...
user avatar
1 vote

SSAS MD dimension joins to same table twice (but processing fails as attribute has 2 names for same ID)

You have a product dimension using all 3 tables. The dimension key is DimSellableItemVersion.SellableItemId and you can use SellableItemAvailability.Id two times as an attribute, once for the product ...
user avatar
1 vote

Dimensional Model for inheritance in dimensions

While in a traditional OLTP system you may need to be concerned about normalizing and inheritance, in a typical data warehouse system you will want to denormalize to accelerate query responses. In ...
user avatar
  • 2,307
1 vote

Datawarehouse - Dimension, Fact, or Degenerative dimension

A two-tier fact table is usually more trouble than it's worth. That's your OLTP instinct to normalize kicking in - fight it! It took me several months to get my head around the warehouse/star schema ...
user avatar
1 vote

Data Warehouse measures without a corresponding dimension

A data warehouse exists to provide the business with usable data. A NULL value does not tell your users anything; you can do better. Also, a NULL value will drop out when joining your fact table to ...
user avatar
1 vote

Data Warehouse measures without a corresponding dimension

Essentially you have NULL values in salesCategory column. Having NULL values in fact tables is generally bad because of the problem your are facing. The best thing to do is correct your data so that ...
user avatar
1 vote

Data Warehouse measures without a corresponding dimension

The standard approach for handling missing dimensional data would be to enter a null value for the relevant data in the fact table. If this approach is problematic for some reason (table constraints,...
user avatar

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