Questions tagged [dimensional-modeling]

The tag has no usage guidance.

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

Data warehouse Design: Parking lot

I came across a data warehousing practice question which Involved design a data warehouse for vehicle parking data, Design Considerations: Vehicle -> 2 wheeler -> Weekday -> 1$ (fare per hour)...
user avatar
  • 101
0 votes
0 answers
22 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-...
user avatar
  • 1
0 votes
0 answers
12 views

Data Vault can we split satellite into multiple ones to load each data type into them?

We are loading data into a satellite table holding values of some key indicators. Because of limitations from the data source, we are ingesting all data types into this sat. In other meaning, integers,...
user avatar
  • 141
1 vote
2 answers
71 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. ...
user avatar
  • 2,070
0 votes
0 answers
40 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 ...
user avatar
  • 266
0 votes
1 answer
52 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 ...
user avatar
  • 103
2 votes
0 answers
104 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 ...
user avatar
1 vote
1 answer
110 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 : ...
user avatar
  • 1,294
0 votes
1 answer
327 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 ...
user avatar
1 vote
1 answer
33 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 ...
user avatar
  • 175
0 votes
1 answer
40 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 ...
user avatar
  • 1
0 votes
0 answers
90 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 ...
user avatar
1 vote
1 answer
21 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 ...
user avatar
  • 113
1 vote
1 answer
51 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? ...
user avatar
1 vote
3 answers
165 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 ...
user avatar
0 votes
0 answers
44 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 ...
user avatar
  • 121
0 votes
0 answers
120 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 ...
user avatar
  • 153
0 votes
1 answer
34 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 ...
user avatar
0 votes
1 answer
44 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 ...
user avatar
1 vote
0 answers
206 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 ...
user avatar
0 votes
0 answers
47 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-...
user avatar
1 vote
1 answer
243 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 ...
user avatar
3 votes
0 answers
90 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 ...
user avatar
0 votes
1 answer
53 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-...
user avatar
1 vote
0 answers
40 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 ...
user avatar
  • 109
0 votes
1 answer
1k 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 ...
user avatar
2 votes
1 answer
58 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 ...
user avatar
0 votes
0 answers
44 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 ...
user avatar
0 votes
1 answer
40 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 ...
user avatar
  • 1
1 vote
1 answer
605 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 ...
user avatar
2 votes
1 answer
8k 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 ...
user avatar
0 votes
1 answer
539 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 ...
user avatar
0 votes
0 answers
520 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) ...
user avatar
  • 31
3 votes
1 answer
729 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 ...
user avatar
  • 31
1 vote
0 answers
26 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 ...
user avatar
-1 votes
1 answer
628 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"...
user avatar
  • 121
0 votes
1 answer
159 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 ...
user avatar
0 votes
0 answers
80 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 ...
user avatar
  • 1
3 votes
2 answers
331 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 ...
user avatar
  • 39
1 vote
0 answers
847 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:...
user avatar
  • 173
5 votes
1 answer
444 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 ...
user avatar
1 vote
0 answers
164 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 (...
user avatar
  • 43
0 votes
2 answers
116 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 ...
user avatar
  • 43
3 votes
2 answers
653 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 ...
user avatar
  • 43
5 votes
2 answers
5k 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'...
user avatar
  • 153
2 votes
2 answers
1k views

Dealing with different types in a fact table

I'm working with dimensional modelling for the first time, trying to build a data warehouse that pulls in data from an OLTP database, and I'm having some trouble figuring out what to do with this type ...
user avatar
  • 121
1 vote
0 answers
249 views

Permission error while browsing the Dimension when Dimension Security is defined on one of the attribute

I am using SSAS SQL Server for dimension modelling, and the dimension is defined as below: Dimension Name: Department Dimension Attributes: Location DepartmentCode DepartmentType I have ...
user avatar
1 vote
3 answers
750 views

Should a Fact table contain a column with a single unique value?

In order to simplify reporting, I am wondering if for each of the Fact tables we have, whether I can include a column that states the name of the Fact table, albeit in a more user friendly format e.g....
user avatar
4 votes
1 answer
2k views

Boolean as measure in fact table or dimension/attribute value

My fact table consists of complaints with typical dimensions of who, what and when. We have a target that the complaint should be responded to within a certain time period. I'm not sure on how I best ...
user avatar
2 votes
3 answers
4k views

Should dates in dimensional tables use dimDate?

Assuming my dimDate has a surrogate key. Should all the date columns in the dimensional tables (not the fact tables) store the surrogate key of date dimension? Or just plain date? For example, in ...
user avatar
  • 1,465