468 questions
0
votes
0
answers
43
views
Dimensional modeling when a source system overwrites dimension history
I am designing a data warehouse and need guidance on a dimensional modeling issue related to overwritten dimension history.
Setup
I have a fact table at the following grain:
• Transaction date
• ...
- 139
Best practices
0
votes
0
replies
15
views
Is it correct to store many transaction-level descriptive attributes directly in the fact table if they have a 1:1 relationship?
I'm designing a Kimball-style data warehouse. My fact table has the grain: 1 row per user per transaction. Each transaction ID appears only once in the fact table.
The transaction itself has ~15 ...
- 69
0
votes
1
answer
38
views
Dimensional Modeling- How to handle Code-Name-Description lookup table
Is there a best practice to handle OLTP lookup tables( code-name-description tables) when converting to Dimension? Eg, the ACCOUNT table in OLTP has almost 15 codes (Eg: TRADING_PLATFORM_CD, ...
- 61
1
vote
0
answers
116
views
dimensional modeling from two different source systems for the same business process?
I have two transactional systems that is used for the same operational business activity: purchase order transactions. One system is legacy and the other is a new modern one. They have different ...
- 801
0
votes
0
answers
143
views
Having Business Key in Kimball style fact tables
Many Kimball style star schema articles suggest that not to have a BK in Fact tables, the grain/uniqueness of Fact tables are supposed to be maintained by the combined SKs from its Dimension tables.
...
- 1,635
0
votes
1
answer
76
views
Get latest values from a SCD dimension table
I have a requirement where I maintain type 2 SCD in my dimension table as below.
For example on day_1 the material code M-01 has a name mat_1_old - now the transactions on day_1 are mapped with ...
0
votes
2
answers
65
views
Should sales order number and purchase order number be placed in FactOrder table or should it have its own dimension?
I have FactOrders with date,saleid,productid,salesorderno,purchaseorderno,qty,price
There are date and product dimension that link to FactOrders on date and productid respectively.
Should sales order ...
- 9,896
2
votes
0
answers
172
views
Complex groupby aggregation with cartesian product of multi-dimensional data over ManyToMany field
I have the following problem with a complex aggregation in postgres (16). The datamodel (CREATE Statements and ER-diagram) and an example of the required result set are to be found under the question.
...
- 63
0
votes
0
answers
433
views
Attempting to build a Multi-fact Relationship model in Tableau - I don't seem to be given the option to add a second base table?
I am in a fresh Tableau workbook, Professional Edition v.2024.1.3 connected to a MS SQL Server database
I have two fact tables that are unrelated to each other, but share several dimension tables.
I ...
0
votes
0
answers
63
views
DWH Model - Relationship between Fact tables
I have been given a new requirement which in Power BI would probably be fixed in Power Query so each data set might not have an issue with multiple fact tables but with how I store the data I am ...
- 21
1
vote
0
answers
137
views
Is it possible to create dimensional OLAP tables and an ETL process within an existing OLTP Postgres database?
I am a backend (mostly Go) developer working on a point-of-sale adjacent platform that processes and stores millions of orders and adjacent customer data into a Postgres database. The data is well ...
- 176
1
vote
0
answers
103
views
Can we create a dimension model without surrogate keys
My requirement is to create a dimension model using ELT techniques . My dimensions are dynamics and any data load issues will have domino effect on subsequent records and their surrogate keys. Is ...
- 21
1
vote
0
answers
225
views
How to handle multiple SCD2 rows out-of-order with SQL
I am looking for a solution on how to handle rows that should be placed in a SCD2 table. In DLT (Delta Live Tables) this is easily solved by using apply_changes with sequence_by and primary key ...
- 35
0
votes
1
answer
105
views
Indexed parent-child hierarchy table to fact table
I have a potential fact table that i want to use as the center of a star schema. It has a indexed parent-child hierarchy structure which i want to flatten using SQL. The table looks like this:
Id
...
0
votes
1
answer
56
views
Model the number of available spots in dimension or fact table?
We have a dimension table with day care facilities and a fact table where we receive the monthly number of taken spots per age category (3 cetagories).
Per facility we also have the number of ...
0
votes
1
answer
123
views
How to build Dimensional model for commission received and return
I am working on the Insurance broker Dimensional model and we have Policy table and relevant other tables like Customer and date table..
Now we are working for further to add some more fact and ...
- 11
0
votes
0
answers
75
views
Is combining a Junk Dimension with a Conformed Dimension in a single table advisable for efficient data management?
In the process of optimizing the product dimension in the data lake for PowerBI analytics, a challenge has emerged where each department has a distinct product dimension with specific features. This ...
- 89
0
votes
1
answer
122
views
How to model fact table if its column connects to different dimensions based on record type?
I'm new to Kimball dimensional modelling, and I cannot solve how to model this case. I look forward to your help on this.
Business case: Our company leases our Equipment (i.e. trucks, excavators, ... ...
- 519
1
vote
0
answers
118
views
Power Query - Data model for multiple dimension tables with start and end dates
I am hoping for some assistance with data modelling. I have created a sample database to show my problem.
https://1drv.ms/u/s!Ap6q8W-mvm27hKRL1J85BopnnrfYRQ?e=tgASW4
Essentially I have a client and ...
- 11
0
votes
0
answers
505
views
Dimensional Modeling: Joining Dimensions together without a Fact Table
I have been working in a Data Warehouse environment for a couple years. From my experience dimensions should not be joined together without a fact table. My understanding is that unless the dimensions ...
- 13
0
votes
1
answer
208
views
Building dimensional model from multiple sources
I have a dimensional model built from multiple sources - online and retail. I have following dims - date, teammember, division, store. And fct - sales.
fct table
date_id
teammember_id
division_id
...
- 3
0
votes
1
answer
156
views
How to handle order line level dimension when used along side a Order level measure?
I have a header-detail type of requirement.
For example:
My dimensions are product, customer, DateTable
My fact table is FactOrders (columns: order num, prod_id, cust_id, date, num of items, total ...
- 9,896
0
votes
0
answers
168
views
How to model a header detail level granularity table alongside a header level fact table when a certain dimension is only applicable to the header?
I have the following model:
My dimensions are product, customer, DateTable
My fact tables is:
FactSales (columns: order num, prod_id, cust_id, date, num of items, total amount, cost of pkg, lineitemid,...
- 9,896
1
vote
1
answer
368
views
How to model a header detail requirement when header has values that cannot be apportioned to details?
I have a header-detail type of a requirement.
For example:
My dimensions are product, customer, DateTable
My fact table is FactOrders (columns: order num, prod_id, cust_id, date, num of items, total ...
- 9,896
0
votes
1
answer
175
views
In a dimensional modelling context, is it good practice to have a dimensional attribute which is derived/calculated from a fact table? [closed]
In a business where customers may execute multiple sales orders over time, the dimensional model contains two tables:
Sales_FT: Tracks Sales at sale-level, including time-stamp of sales
activity
...
- 3
0
votes
1
answer
168
views
Not quite understand a concept in Kimball's dimensional modeling
I have read through the idea "Behavior Tag Time Series" several times but couldn't understand
Here is the explanation in the book, but still not make sense:
"Almost all text in a data ...
- 1,514
0
votes
1
answer
135
views
How to deal with multiple facts in dimensional modelling
I have a scenario where the technical team is requesting for single dimensional model combining different subject areas like finance, sales,etc.
eg: Fact table 1 -> Finance area gives P&L ...
- 41
0
votes
1
answer
208
views
How to deal with Foreign Keys into Dimension Tables
I am trying to separate the following fields into a new dimension table called Territory.
As you can see, there is no TerritoryKey in the Fact Table I have.
As the columns should not repeat in a Star ...
0
votes
0
answers
278
views
Building a Snapshot Fact Table
I'm working on my first DW project and would appreciate some help.
I’m at the stage now where I want to populate my fact table with already-existing data.
(using Snowflake btw)
The way I intend for it ...
0
votes
0
answers
59
views
Implementing date dimensions in DW?
Working on my first data warehouse project and learning online on the way using tutorials, articles, youtube videos etc.
I'm using data meant to mimic an OLTP database for a dvd rental company, and I'...
1
vote
1
answer
300
views
Dimensional Modeling: How do I handle a hierarchy in a factless fact table?
I'm kind of new to dimensional modeling. It seems to me that a lot of my data do not have specific measures. For instance, we have Organizations, and each has 1 or more Locations. There are ...
- 1,281
0
votes
2
answers
395
views
Fact Table and Grain - Repeating Measures?
Suppose I have tables as indicated below. I'm trying to create correct fact tables.
If I combine both tables into one fact table, I would be repeating the sales measure since each sale contains at ...
- 247
0
votes
2
answers
352
views
How to handle repeating values in fact table due to few numeric columns being at a higher grain?
I am looking at a fact table which has a certain grain. So each record represents a transaction at that grain. Let's say the grain is header detail sub-detail.
But there are some numeric columns in ...
- 9,896
0
votes
1
answer
97
views
Replacing a Date Range BETWEEN clause using single Join column
Database: Snowflake
I have a slowly changing Fact (yes, that's correct, fact not Dimension) that uses EFFECTIVE_TS and EXPIRATION_TS for the periodicity.
The Query uses <input_date> BETWEEN ...
- 61
0
votes
0
answers
69
views
Dynamic dimensional model per company in SQL
I'm designing system where each table supports dimensions. There are a couple of ways achieving it by using snowflake, star schemas. Unfortunately I don't see how I can apply any of this in my use ...
- 362
0
votes
0
answers
103
views
Hierarchy vs separate Dimensions
I understand that Kimball recommends to flatten out hierarchy in single table. Eg: Product->Product Sub Category->Product Category should be stored as follows:
Product desc - Product Sub ...
- 61
0
votes
0
answers
323
views
bridge Vs Factless Fact
Using snowflake database, Brokerage industry , Plan Dim stores contract details about stock plan services between the service provider and Client org, while Custody Account Dim stores Custody accounts ...
- 61
1
vote
1
answer
122
views
Should product hierarchy be added to 1 or multiple dim tables?
I have product hierarchy as follows:
Category
Sub category
Product
When does it make sense to have all these 3 columns in 1 table called Prooduct, or to make 3 tables - 1 for each?
- 9,896
0
votes
1
answer
274
views
Is a dimension or fact table more appropriate for this scenario?
I'm struggling as to whether I should have this data live as a fact or dimension table, and would like some help.
In general terms, the data and business process is a customer's/accountholder's ...
- 3,021
0
votes
1
answer
146
views
SQL SCD2 weekly statistic grouped by shipping date
I've been having one of those weeks...
I am creating a simple POC for the Director, who is looking for weekly stats of order values shipping.
The scenario they asked is "At the end of each week (...
- 11
0
votes
1
answer
141
views
Creating a period-flag where the ending date is based on an inconsistent holiday
I'm working on a date dimension table for my business, including your standard date measurements as well as holidays. Something I'm trying to add is a flag for certain important periods for the ...
- 1
0
votes
1
answer
197
views
Populate Dim and Fact Table from Source Table which already has a Natural Key
I have a Source Table which looks like as below (pls focus on the show_id column)
[
And below is the Dim and Fact Table Diagram from my school's guide:
They put the show_id from the Source Table ...
0
votes
2
answers
96
views
How should I model a business process and its subsequent related processes and actions?
I feel it is similar to the age old Sales --> Orders setup but I can't seem to finalize a solution.
I have a Business process of "Complaints" which are placed into a fact table which are ...
- 35
0
votes
1
answer
454
views
How to deal with numeric values in Dimension?
I have a ‘Project’ dimension and it has a discount value (in percentage). My problem is I cannot use a column from a Dimension directly in a measure…
I have to do Discount x Revenue. (Usually my ...
- 704
0
votes
1
answer
175
views
Using EFFECTIVE_TS and EXPIRATION_TS on FACT tables
I have a requirement to create a Fact table which stores granted_share_qty awarded to employees. There are surrounding Dimensions like SPS Grant_dim which stores info about each grant, SPS Plan Dim ...
- 61
0
votes
0
answers
129
views
Create a dimensional table from an event data stream
Possibly a bit of an odd question given that dimensional tables mostly get created by wizards in RDBMSs as far as I have seen. But I want to see if I can create a dimensional table with a SQL query ...
- 285
1
vote
2
answers
935
views
In Data Warehousing, Should a Data Model have less+large Dimension Tables or split into more+small Dimension Tables?
I've just started learning Data Modeling in DW. And now I'm a bit confused about choosing with field to build a Dimension Table and whether should I split into many DIM tables
For example, I have a ...
-1
votes
2
answers
292
views
Surrogate keys in star schema hierarchy dimension
Is it necessary to have surrogate keys for each hierarchy level above the lowest level in a dimension table?
Row City_Key City_Name State
1 1234 Chicago Illinois
2 3245 ...
- 1
0
votes
0
answers
47
views
Fact table in dimensional modeling has primary key duplicates
I have a fact table at the grain of sales order. The issue is that people want to see the invoice number but each sales order can have multiple invoice numbers. Which causes my table to have ...
- 62
-1
votes
2
answers
70
views
Problem placing attribute in dimensional layout
I am doing a small exercise, I need to create a small dimensional design that deals with the tsunamis that have occurred in different countries over the years. I have created a "Country" ...
- 65