Questions tagged [dimensional-modeling]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
0answers
146 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) ...
0
votes
0answers
4 views

why key structure not star schema [closed]

I can see that in many projects that instead of star or snowflake schema,"key" structure is used, can anyone tell me the benefits of using such structure to populate fact tables instead of star/...
1
vote
0answers
21 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
0answers
15 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-...
2
votes
3answers
1k 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 ...
0
votes
2answers
88 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 ...
1
vote
1answer
28 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 ...
1
vote
2answers
420 views

Dimension for Product Properties based on Product Type - Sparse Dimension?

I am creating dimension of product properties for sales facts. Property of product depend of product type. For example: - Type = smartphone. Properties = model, OS, size - Type = book. Properties = ...
0
votes
0answers
53 views

Cascading One-to-Many relationships modeling

Are there any drawbacks or better alternatives of this non relational model ? I note that it remains easy to understand but the concern is with the code interacting with. First, as I was introduced ...
0
votes
1answer
589 views

Periodic snapshot fact table with monthly grain - Question on dimensions

I am fairly new to data-warehousing but have read my fair share of books and online tutorials to gain an introductory understanding of the basic components that build up a data warehouse. My end goal ...
1
vote
0answers
25 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 ...
0
votes
1answer
43 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-...
4
votes
1answer
1k 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 ...
1
vote
0answers
14 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 ...
0
votes
1answer
123 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 ...
0
votes
0answers
5 views

Unconventional Fact Table - general event type with different event sub-types in one table

I am working on a DW project and currently beginning preliminary modeling. The core raw data I am beginning with isn't so much like the classic DM example cases of sales with dollar values, etc. I ...
0
votes
0answers
20 views

Dimensional Modeling in Redshift - How wide to go?

I am playing with Redshift as a solution for a data warehouse I am architecting and I am trying to get my head around best practices for dimensional modeling in this case. I am using one source ...
2
votes
1answer
31 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 ...
0
votes
0answers
40 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 ...
0
votes
1answer
18 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 ...
0
votes
0answers
13 views

Rolling up/aggregating a duration measure in OrderLine fact table

I am starting to model a datawarehouse, and would like some suggestions. We have Orders and OrderLines. I have read that it is often recommended to model this as one OrderLine fact table at the ...
1
vote
1answer
40 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 ...
0
votes
1answer
725 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
1answer
176 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 ...
1
vote
0answers
16 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 ...
3
votes
1answer
291 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 ...
-1
votes
1answer
364 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. ...
0
votes
1answer
120 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 ...
0
votes
0answers
51 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 ...
2
votes
2answers
736 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 ...
3
votes
2answers
201 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 ...
1
vote
0answers
146 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 (...
9
votes
6answers
10k views

Dimensional Modeling and ETL in Redshift

I have been researching Amazon's Redshift database as a possible future replacement for our data warehouse. My experience has always been in using dimensional modeling and Ralph Kimball's methods, so ...
1
vote
0answers
534 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:...
5
votes
1answer
72 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 ...
3
votes
2answers
159 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 ...
1
vote
2answers
290 views

Dimensional Model for inheritance in dimensions

How are dimensions typically modeled in a dimensional model if one dimension logically inherits from another dimension? For example, assume I have an Employee dimension and a Driver dimension, where ...
4
votes
2answers
3k 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'...
1
vote
0answers
179 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 ...
1
vote
3answers
481 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....
0
votes
1answer
692 views

SSAS MD dimension joins to same table twice (but processing fails as attribute has 2 names for same ID)

My cube has a product dimension, and each product has an "availability" status attribute. In the database, SellableItemAvailabilityId in DimSellableItemVersion (which contains products) joins to Id in ...
2
votes
0answers
88 views

When to Converge Dimensions in a Data Warehouse When There Are Few Common Attributes

I have two StudentDetails dimension tables in my data warehouse and I want to know whether I should converge them, considering that they have limited attributes in common (keeping in mind Kimball DW ...
3
votes
3answers
2k views

To normalize or not to normalize for few distinct values

Assuming in a Postgres database, you have a table called party, which can have less than 5 well-defined party_types such as 'Person' or 'Organization'. Would you store the party_type in the party ...
5
votes
2answers
7k views

What is the difference between bridge table and helper table in data warehouse's context?

From what I could understand :- Bridge tables are used when a dimension table can not be directly associated with a fact table. e.g. In a bank's data warehouse a fact table of balance of a customer ...
1
vote
1answer
138 views

Dimension Modelling Senario Data Warehouse Design

I'm looking for some advised on how to model the following scenario in our data warehouse. We have the following entities Staff Mobile Devices Computers Mobile Bills If feels like its simpler to ...
2
votes
2answers
2k views

Best way to capture historical changes between levels in a dimensional hierarchy

What is the cleanest way to capture changes between levels in a dimensional hierarchy? I have the dimensional hierarchy [Area] > [Region] > [Location], where Area is the parent to Region and Region ...
1
vote
1answer
2k views

SSAS Hierachies duplicates when browsing

The dimensions in the cube are giving us duplicates, there is no error while processing the cube, but when the cube is browsed we could see duplicates. There is a hierarchy in our dimension, the ...
6
votes
1answer
266 views

Designing a dimensional DB off a normalized source that already implements SCD's

I have built an SSIS ETL to integrate various data sources (one from MySQL, two from SQL Server) into a single SQL Server relational and normalized database, which I've called [NDS]. The SSIS ETL ...
1
vote
1answer
43 views

What if the lowest granularity level can legitimately be null?

I have a normalized database that I'm going to be using as a source for a dimensional data store; one of the facts I'm collecting is Orders, the normalized schema looks roughly like this: However ...
1
vote
2answers
526 views

Ordering of a dimension in sql server datawarehouse

Built and deployed a simple cube with a date dimension. The dimension has things like day of month. How do i sort it? it always shows in the reports as 1, 10, 11, 12, ... 19, 2, 20, 21 Design. ...