Skip to main content

Questions tagged [dimensional-modeling]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
0 answers
13 views

Synthetic or derived fact tables

I have a doubt on how to model a situation where the granularity of what we show in the reports does not derive directly from measurements (i.e. classic "facts"), but rather, is ...
user2412218's user avatar
1 vote
0 answers
12 views

PowerBI Data Modeling: Modeling with sub-dimension and sub-facts

We collect test data from network tests. There is common dimensions and facts for all the different tests, But there are also some facets of the dimensions and facts that are unique. If this was an ...
Dr.YSG's user avatar
  • 399
0 votes
0 answers
78 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 ...
Sam Johnson's user avatar
0 votes
1 answer
56 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/ ...
user45867's user avatar
  • 1,683
-1 votes
1 answer
298 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 ...
Dhruv's user avatar
  • 103
0 votes
1 answer
1k 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 ...
user3118602's user avatar
2 votes
1 answer
418 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,...
user3118602's user avatar
0 votes
1 answer
61 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 ...
user3118602's user avatar
0 votes
1 answer
604 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-...
Mads's user avatar
  • 31
1 vote
2 answers
422 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. ...
variable's user avatar
  • 3,544
0 votes
0 answers
135 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 ...
Avi's user avatar
  • 265
0 votes
1 answer
129 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 ...
Michael Ramos's user avatar
2 votes
1 answer
835 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 ...
Irfan Gowani's user avatar
1 vote
1 answer
466 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 : ...
Pantea's user avatar
  • 1,432
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 ...
user3118602's user avatar
1 vote
1 answer
165 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 ...
d11wtq's user avatar
  • 175
0 votes
1 answer
60 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 ...
adam's user avatar
  • 1
0 votes
0 answers
186 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 ...
Dave Copeland's user avatar
1 vote
1 answer
41 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 ...
sksamuel's user avatar
  • 113
1 vote
1 answer
108 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? ...
tale852150's user avatar
1 vote
3 answers
634 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 ...
JustGiveMeData's user avatar
0 votes
0 answers
81 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 ...
Yankee's user avatar
  • 131
0 votes
0 answers
251 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 ...
ryantuck's user avatar
  • 173
0 votes
1 answer
44 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 ...
jordaniac89's user avatar
0 votes
1 answer
147 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 ...
Michal Karbownik's user avatar
1 vote
0 answers
318 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 ...
pencil_sharpener's user avatar
0 votes
0 answers
197 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-...
danieltisdale's user avatar
1 vote
1 answer
613 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 ...
tale852150's user avatar
3 votes
0 answers
158 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 ...
user3505886's user avatar
0 votes
1 answer
72 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-...
Daniel R's user avatar
1 vote
0 answers
166 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 ...
GettingItDone's user avatar
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 ...
KingOfTunis's user avatar
2 votes
1 answer
113 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 ...
Sean's user avatar
  • 125
0 votes
0 answers
62 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 ...
Amira Bedhiafi's user avatar
0 votes
1 answer
138 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 ...
Steve's user avatar
  • 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 ...
Arpit Agrawal's user avatar
3 votes
1 answer
15k 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 ...
Arpit Agrawal's user avatar
0 votes
1 answer
666 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 ...
codedawg82's user avatar
1 vote
0 answers
982 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) ...
Bee-Dub's user avatar
  • 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 ...
Bee-Dub's user avatar
  • 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 ...
Brandon W Warner's user avatar
-1 votes
1 answer
784 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"...
Thufir's user avatar
  • 121
0 votes
1 answer
176 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 ...
Atmo101's user avatar
0 votes
0 answers
116 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 ...
Sohi's user avatar
  • 1
3 votes
2 answers
860 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 ...
Will E's user avatar
  • 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:...
ggle's user avatar
  • 173
5 votes
1 answer
895 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 ...
Andrew Wolfe's user avatar
1 vote
0 answers
205 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 (...
Nika's user avatar
  • 43
0 votes
2 answers
156 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 ...
Nika's user avatar
  • 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 ...
Nika's user avatar
  • 43