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,...
- 176k
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] ...
- 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 {[...
- 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 ...
- 505
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 ...
- 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 ...
- 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.
;...
- 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 ...
- 131
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 ...
- 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 ...
- 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.
...
- 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 ...
- 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 (...
- 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 ...
- 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-...
- 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 ...
- 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 ...
- 105
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 ...
- 105
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 ...
- 53
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 ...
- 105
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 ...
- 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 ...
- 5,772
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 ...
- 5,772
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 ...
- 5,635
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,...
- 1,359
Only top scored, non community-wiki answers of a minimum length are eligible