Questions tagged [dimensional-modeling]
The dimensional-modeling tag has no usage guidance.
93
questions
0
votes
0
answers
43
views
How Do I create history table that allows me to retrieve the times when a row active by year & month?
I want to be able to retrieve the in-between months when a client was active but from a historical table.
Table: DimensionEpisode
episode_id
client_id
episode_status
discharge_date
admission_date
...
- 21
0
votes
1
answer
35
views
Architecture for "Overlapping Dimensions"?
Take a SQL RDBMS data warehouse -- typical facts and dimensions type layout.
Say you want Orders x Country.
Maybe a date field, an orders field, a country field.
And then if you want a report/ ...
- 1,651
-1
votes
1
answer
160
views
Understanding OLTP and OLAP
I know what is the meaning of OLAP and OLTP and who is specialized to do what. Currently, I'm just studying data modelling theoretically and I don't know the practicality of the differences.
I wanted ...
- 3
0
votes
0
answers
20
views
Paired Column modelling from fact to dimension tables
looking for some feedback and opinions on modelling a table that captures its elements in paired columns.
The source inserts a row with a value in two columns for each pairing. In the example below ...
- 101
0
votes
1
answer
892
views
Handling multiple dates on fact table with performance concerns
I am working on a simple data marts and has once again stumbled with the handling date and time.
This is a design on a vehicle maintenance form records to collect the status record/remarks for ...
- 147
2
votes
1
answer
291
views
Data marts - to create multiple database or consolidate in one?
Take this as an example (image taken from Google):
All data pipeline diagrams show that there are multiple data marts for each different business units or specific workscope (in this case, Purchasing,...
- 147
0
votes
1
answer
51
views
How do we assign FK to a newly loaded data in fact table?
While reading up on SQL, I got confused on how a newly loaded data into the fact table will get its FK. Say using the example below (image taken from Google):
This is my thoughts on how the data is ...
- 147
0
votes
1
answer
484
views
Boolean flag in fact table
Imagine we have received the results of a health survey on daily consumption habits of 3 different items, like the following:
Id
Date
Age
Country
CigarettesPerDay
CoffeesPerDay
BeersPerDay
1
2021-12-...
- 31
1
vote
2
answers
367
views
What is the disadvantage of not creating surrogate key in DW?
I want to create a data warehouse from 1 OLTP database. The tables in the OLTP db have got integer primary keys. So they are the business keys.
The tables are: Client, customer, products and sales. ...
- 3,442
0
votes
0
answers
120
views
Key attributes in junk dimension table
I have a fact tables with a composite primary key of 10 key columns.
6 out of 10 of the key columns are short, and with low cardinality, and also "mostly" unrelated to each other.
I am ...
- 265
0
votes
1
answer
119
views
What database & design would you use for legos [closed]
I'm trying to conceptualize an interesting problem I am designing for. Using lego's (the plastic construction toys) as an analogy seems to work well. If this is a fairly recognizable problem, then I'd ...
- 103
2
votes
1
answer
655
views
Dimension modelling for HR with Employee Dimension and multiple departments in a Data warehouse
What is the best way to configure a dimension model (preferably star schema) when we have the following requirements?
There is an Employees table (25 attributes) where we are required to make some of ...
- 21
1
vote
1
answer
442
views
How to handle NULL Dates in an "Accumulating fact table"
One type of fact table in Dimensional modeling is Accumulating Snapshot fact Table. if you think you need to review the meaning and definition of this type , you can take a look at this article :
...
- 1,412
1
vote
1
answer
2k
views
Connecting dimension tables in a multi-fact star schema database warehouse?
I am new to data warehouse modelling so please bear with me.
In database warehouse modelling, the star schema is typically a fact table with multiple dimensions connected directly to it. However, what ...
- 147
1
vote
1
answer
147
views
Summarizing over Outrigger Dimensions Directly (e.g. Total Customers by Demographic by Signup Date)
According to Kimball:
https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/outrigger-dimension/
A dimension can contain a ...
- 175
0
votes
1
answer
57
views
Data modeling for Warehouse
I am learning about database warehouses and data modeling. I came across this blog post. In the section, "DIFFERENT MODELS HAVE DIFFERENT PURPOSES" the author mentions that depending upon ...
- 1
0
votes
0
answers
181
views
Schema and/or reporting SQL for customer acquisition cost?
This a design question around creating fact/dimension tables to query customer acquisition cost and how to properly model the tables and query them.
Business Rule
We spend some money per day per ...
1
vote
1
answer
37
views
Type 2 Dimension for a fact measure
I have a need to store products along with their prices and quantities (and many other things) and track the history.
Now, given both price and quantity are stored as integers, it is my understanding ...
- 113
1
vote
1
answer
98
views
Transactional versus Dimensional model for file upload web application -- preferred approach?
For a web application whose sole purpose is to allow users to upload raw file data at any time (to database tables), does a transactional or dimensional model make more sense for such an application?
...
- 237
1
vote
3
answers
485
views
The right way to model multiple FACTs schema
Background
I'm in a process of designing a database (using a STAR schema).
There are three tables to model: products, tests, states.
The database will be used to store results of tests conducted on ...
- 13
0
votes
0
answers
77
views
How to deal with equally growing fact/dimension tables in datawarehouse design?
I have a source data set with:
customer
customer_product_purchase
customer_support_plan_purchase
customer_support_request
All of them have a relationship such that a support request is raised ...
- 131
0
votes
0
answers
231
views
How to correctly define foreign keys to heterogenous dimensions in fact table?
Question
What is the best practice for defining foreign keys in a fact table to multiple different and mutually-exclusive dimension tables?
Example Case
Business process
I am creating a ...
- 163
0
votes
1
answer
40
views
Deciding what values should be used in a data warehouse dimension?
I'm beginning to look at data warehousing and I'm coming across examples where, for instance, we have check payment transactions. I understand that this should be the "fact" and we would have ...
- 103
0
votes
1
answer
135
views
In a data warehouse, should a measure be based on a fact or a dimension?
Let's say there is a data warehouse created from a shop data. A fact is a single purchase of a product. There is a dimension that describes a customer.
There is a need to create a measure that ...
- 101
1
vote
0
answers
303
views
Star schema with both many-to-many relations and inheritance
The problem
I am trying to make a star schema of the following situation, concerning transactions on a stock market. I have summarized the scenario here:
Each transaction traded on the market has
1 ...
0
votes
0
answers
172
views
How to handle table that has an id which references multiple dimension tables?
I'm having a hard time figuring out how to create a lookup table or a table that references multiple dimension tables. I've got a "tracking" table that tracks the number of impressions for an e-...
1
vote
1
answer
568
views
In the practice of Kimball's Dimensional data warehouse design model, do lookup tables (not dimensions) contain a sid column?
Trying to create a simple lookup table for a drop down on a BI web page.
The table contains 2 columns, an key and a description.
What is the best practice with regard to Dimensional Modeling when ...
- 237
3
votes
0
answers
138
views
Store email in a separate Dimension or in a Degenerate Dimension?
I just started learning about dimensional modeling and I am creating a star to analyse email newsletter signups for an online business.
I have a fact table that records the signups and links to a ...
- 131
0
votes
1
answer
62
views
Database model / design best practice
I'm looking for a some guidance/advice on how to best model my data.
Some background: I have location information that I'm trying to model. There are two categories of locations (company and non-...
- 9
1
vote
0
answers
156
views
How does one determine the number of FACT tables that are required in a data-warehouse? [closed]
When analysing data sources that need to be included in the data-warehouse (Kimball), how does a developer determine whether a schema should be a star type or constellation type?
Business processes ...
- 109
0
votes
1
answer
2k
views
How to optimally structure a table with sparse data
I am working on a project to create a series of tables that are easier to use and more intuitive for fairly naive SQL users. Because of the structure of our data, we are running into a trade-off ...
2
votes
1
answer
109
views
Implementing a degenerate dimension with an alphanumeric varchar(30) at the transaction level?
I am currently working on designing my first data model and I am confused about this particular implementation of degenerate dimensions. According to Kimball's design tip 46, a degenerate dimension ...
- 125
0
votes
0
answers
60
views
Dimension attribute with one-to-many relationship
I have a request for an attribute in a dimension that has a one-to-many relationship with the lower level of the dimension.
Here is the case:
Dimension: Employee
Attribute: Immatriculation
I have ...
- 131
0
votes
1
answer
122
views
How to model changing OrderLine rows in fact table
In our business, we have Orders that are made up of OrderItems. Over time, these Orders can change status (e.g. Received, Challenged, Planned, Completed). During this process, the OrderItems for an ...
- 21
1
vote
1
answer
1k
views
What is the meaning of "dimension key"?
I am a beginner trying to understand the basic concepts of database's. I know several types of keys (Primary key, Foreign key, candidate key, super key, surrogate key.....etc). While learning about ...
2
votes
1
answer
13k
views
what is the difference between 'order' and 'order line'?
I was trying to understand degenerate dimensions. But I quite often encounters the term "order line" as grain in an transaction table. Could someone please explain the difference between the order and ...
0
votes
1
answer
646
views
Data Warehouse Modeling: Sales Order Charges [closed]
I have two databases systems that I'm trying to dimensional model a sales order business. Both operational data stores have a Sales Order header and detail table. However, one of them has an ...
- 534
1
vote
0
answers
921
views
Dimensional Model for CRM Sales Opportunity and Sales Leads: Fact or Dimension?
I'm working for a business lender, and trying to come up with a basic dimensional model around the subject area of loan approvals.
We use a CRM, (Salesforce) and one of the core objects is a (Sales) ...
- 41
3
votes
1
answer
1k
views
Dimensional Modeling of a Loan Approval Process and Fact Grain(s)
Context:
I'm modeling for a lending company and I'm modeling our loan approval process. I have some basic dimensions relative to our business figured out:
Time(day)
Account
Opportunity
...
- 41
1
vote
0
answers
29
views
Fact Grain & Modeling Loan Approval Proccess [duplicate]
I'm modeling for a lending company and I'm modeling our loan approval process. I have some basic dimensions relative to our business figured out:
Time(day)
Account
Opportunity
Channel
And I and ...
-1
votes
1
answer
764
views
How to normalize CSV invoice data?
For flat data which comes in with fields like:
name
address
status
class
version
user
mode
CSV files are regularly received, with the above as typical columns. Some fields, such as "class"...
- 121
0
votes
1
answer
175
views
Can multiple fact tables have the same structure? [closed]
I'm designing the schema of a data base and I have two solutions :
the first is to use one fact table linked to multiple dimensions.
the second is to use multiple fact tables with same structure for ...
- 3
0
votes
0
answers
111
views
How to Represent a VARCHAR Column with XML Data in a Fact Table?
I have SQL Server 2016 environment and I have decided to design a source table with holds some sort of event data as a transactional fact table. The table has about a million rows, and generates about ...
- 1
3
votes
2
answers
705
views
What is the industry term for superset of "metrics" and "dimensions"? [closed]
If I want to refer to measures/metrics and dimensions with a single term, what would be a good industry-accepted expression?
Metrics is used commonly in UIs as well such as Google Analytics or ...
- 39
1
vote
0
answers
1k
views
Kimball Cross Reference Table/Bridge Table
I'm currently struggling to understand how to apply Kimball methodology to a certain scenario and talk of Outriggers/Cross Reference/Bridge tables are starting to get confusing.
Consider the scenario:...
- 173
5
votes
1
answer
812
views
If a DW fact table is not uniquely identified by all dimensions, what problems?
This is a bit of a thought problem I've been wrestling with. I have a visceral antipathy to the notion of duplicate combinations of dimension values in a fact table. I've read a lot about the ...
- 584
1
vote
0
answers
197
views
Are there differences between a Fact Constellation Schema and a Galaxy Schema?
I am currently working on the two schemes and I'm just not sure if they are the same or if there are differences. In the literature both are often synonymous. Now I've read that there are differences (...
- 43
0
votes
2
answers
144
views
Is there a recommended order to create a DV model and star schema?
I want to create and implement a Data Vault Model. In the presentation layer, I want to create a View based on a star schema. Now I would like to know if there is a certain order in creating the ...
- 43
3
votes
2
answers
1k
views
Should I use separate address dimensions?
I have a Star Schema model with two Dimensions (User and Store) each of them have addresses, which I store.
Each User and Store can just have one Address. Is it recommended to split the Dimension ...
- 43
5
votes
2
answers
6k
views
What are the difference between a "dimension" table in a star-schema and a "lookup" table in a relational database?
I'm trying to design a star-schema fact table along with some dimension tables that will surround it. If I reuse the natural key called customer_key in both the fact_table and dim_customer then I don'...
- 153