12
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, here's my attempt. I will update this with the magic you use to determine things like Fiscal_MonthNumber and Fiscal_MonthName, because right now they're the ...
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', '...
6
I expect I would have a drive_usage fact table with a link to a snapshot time dimension, a drive dimension, a computer dimension and the various numerical facts about the drive at that instant in time.
There should probably be nothing regularly changing in the drive dimension - I guess it depends upon your definition of drive - is it a physical drive or a ...
5
You might want to decompose the support tickets fact table into transactions; User w on Date x moved ticket y to state z etc. This would facilitate metrics like "bounced tickets" etc. which service desk managers always seem to be keen on. This could be supplemented with the Accumulating Snapshot table you already have. Look here http://www.kimballgroup.com/...
5
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 dimensions, and not the cube, the measures will never be updated.
You could Process Update the dimensions which wouldn't unprocess the cube but would drop the indexes ...
answered Jun 22 '15 at 13:35
Tom V - try topanswers.xyz
15.1k7 gold badges53 silver badges82 bronze badges
5
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 {[Measures].[Your Measure Here]} on 0,
[DimAccounts].[AccountPK].[AccountPK].Members on 1
from [Your Cube]
where {
Filter(
[DimAccounts].[CreatedOn].[CreatedOn]....
5
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 whether an event occurred at 8 seconds or 42 seconds past the minute, create your time dimension at the grain of 1 minute.
As you did not tag your RDBMS, I ...
5
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 the fact table.
Either change your ETL to copy the max_weight in your fact table, or use a named query in your DMV joining your fact table to your dimension ...
answered Sep 5 '17 at 9:26
Tom V - try topanswers.xyz
15.1k7 gold badges53 silver badges82 bronze badges
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....
3
Mondrian OLAP server
http://mondrian.pentaho.com/
Pentaho Schema Workbench (for creating the cube schema)
http://mondrian.pentaho.com/documentation/workbench.php
Pentaho Aggregation Designer (for finding useful aggregations and building them)
http://infocenter.pentaho.com/help/index.jsp?topic=%2Faggregation_designer_guide%2Fconcept_pad_overview.html
...
3
I'm not sure that you need "natural" keys, but you probably do need to maintain a key mapping of sorts. So you need to understand what relationships map between your source and target systems, identify the keys for those relationships and build your key mappings from there.
I had a previous question on this called "What is the best practice for mapping ...
3
You need to overcome your scruples and materialize the list of users and allowed clients into a dimension, then apply security to that dimension. If you have Enterprise Edition you can hide the dimension from the users.
2
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 (either
ISO8601 strings or the DB's datetime object) and only allow limitted
filtering.
Putting a date attribute into an existing dimension makes sense if the ...
2
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 the attribute cannot be used as a level in a user hierarchy; the attribute hierarchy exists as a member property only. However, a disabled attribute hierarchy ...
2
To know how many tickets are active on a particular day, you need to know both when a ticket first became active and when it was closed. Your design already contains this data, so the count can be generated without adding any data. This can be done by calculating the number if days between the open and close and then joining that with a data set large ...
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
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 set it to false) and Parallel.
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process>
<...
2
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.
Keep in mind that, unless you specify the username and password the processing is done using the Analysis Services service account while when you browse data from ...
answered Sep 23 '16 at 11:16
Tom V - try topanswers.xyz
15.1k7 gold badges53 silver badges82 bronze badges
2
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 info, we simply modified the formula to
create MEMBER CURRENTCUBE.[Measures].[% WT]
AS [Measures].[Weight]/[Dim Truck].[Max_weight].currentmember.properties("...
1
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 MemberWithDataCaption of the Parent and any translations
In the formatstring the first star is replaced with the original
name, succeeding star will not be replaced.
Examples:
...
1
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 database but it is used twice with different names for the cube.
Second each Week dimension has to be connected to a measure group. If you use only one measure ...
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
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 and once for the parent-product. But SSAS does not know which is which or when you want to use it only once, which on you mean. Do you want to go left or right ...
1
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 this case, I would design an Employee_Dimension table which had all relevant columns about an employee which you might want to query against, along with an ...
1
We are familiar with the IBM toolset where I work. IBM's Cognos toolset can read star schema, especially since IBM recommends star schema for mart design.
1
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 a dimension table, causing undercount.
In your dimension table, include a record for each scenario where a transactional database might store a NULL. All of ...
1
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 these values are present as this will provide the most accurate results.
If you can't remove/correct the NULL values then you are limited to filtering the ...
1
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, business rules) , adding a dimensional value to represent 'null' would be acceptable, if less technically correct. If you choose to implement this solution, ...
1
Sounds like you've already implemented some form of Slowly Changing Dimension, dealing with times, but need to also deal with user preferences as well? For ease, I'm going to use Type 2 SCDs and assume temporal changes, but you could obviously swap those out for a more appropriate approach as your specific solution requires. I'm also going to imagine the ...
1
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 paradigm.
All that invoice header information can be tucked into a couple of extra dimensions. This will just add a couple of INT columns to your fact table, ...
Only top voted, non community-wiki answers of a minimum length are eligible
