Questions tagged [star-schema]

The tag has no usage guidance.

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

Handling updates in OLAP system for e-commerce system using immutable stores

I have a digital commerce system that we have built in-house and it has been working fine. We now want to implement OLAP to provide our customers with advanced reporting and BI features. While looking ...
brahmana's user avatar
  • 101
1 vote
0 answers
120 views

Data warehouse: solving many-to-many relationship with duplicates

I'm learning dimensional modeling and have a question. I understand those basic examples such as a FactSale has DimItem, DimLocation. But when it comes to complicated cases with many-to-many ...
Arxz's user avatar
  • 11
0 votes
1 answer
69 views

SQL All Procs have WITH RECOMPLILE so how to maintain a bloated plan cache

I'm working a star schema data warehouse created in Azure SQL database where the last developer included WITH RECOMPILE on all the Procs. This I believe was because the ETL only executes these Procs ...
Geezer's user avatar
  • 441
0 votes
1 answer
131 views

In a star scheme, How to define the fact table when the data has different levels of dimensions?

In a PowerBI project, I need some help to define the fact table/s. Eventually, I would like to have a nice star scheme to preset some basic visuals as stacked bar etc. to the client. I'm new to BI and ...
ShayD's user avatar
  • 1
1 vote
0 answers
168 views

Star Schema design considerations

We receive patient data that includes case level info (hospital id, patient id, length of stay, surgery date) as well as surveys/educational material that the patient completed as well as their ...
CandleWax's user avatar
0 votes
0 answers
798 views

ERD to Star Schema

I have a simple ERD diagram as represented below for a supplier for inventory goods. What would be the best way to turn this ERD into a Star Schema with one Fact Table and a few dimension tables? To ...
Trelaquix's user avatar
-1 votes
1 answer
334 views

Efficient dimension and fact joining

I have large fact table, and a much smaller dimension table in a simple star schema: --1. CREATE TABLE dbo.Dim ( Id INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED, CustomerName VARCHAR(2000) ) --index ...
Avi's user avatar
  • 265
2 votes
1 answer
708 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
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
158 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
1 vote
2 answers
695 views

text unique to the grain should be stored in fact table or dimension table?

I am modeling a star schema for user reviews using the yelp data set. Each user review has a business dimension key, user dimension key and a bunch of data associated with the review. All of the ...
bfmcneill's user avatar
0 votes
0 answers
182 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
0 votes
1 answer
192 views

Is Star/Snowflake database design good for applications? What is the alternative?

I need to solve an exercise for a pharmaceutical company, they ask me to create a database structure from scratch given a few informations. Because I want it to be scalable and "future proof"...
Francesco Mantovani's user avatar
1 vote
1 answer
371 views

designing a star schema

I am a newbie and would appreciate suggestions/material to research I have 3 types on information in a single table problems reasons fixes problem data is related to reasons by 1 to many reasons ...
user2543622's user avatar
1 vote
3 answers
536 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 ...
Maciejo95's user avatar
0 votes
1 answer
2k views

How to Store a Many-to-Many Relationship Between Fact Tables in a Data Warehouse

What are some structures and models to store many-to-many relational data between two fact tables in a data warehouse? Currently, I am using a mapping table which includes the primary keys from both ...
Edward's user avatar
  • 1
0 votes
0 answers
80 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
1 vote
0 answers
312 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
3 votes
0 answers
142 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
149 views

What can I do to speed up this query which aggregates over an interval?

I have a star schema database in Redshift. I'm running an aggregation on a fact table order_facts with the following relevant columns: total - FLOAT - total order cost payment_date - INTEGER - ...
DeeDee's user avatar
  • 101
1 vote
2 answers
2k views

Design star schema for relatonal database

I'm a newbie to data warehousing and I've been reading articles and watching videos on the principles but I'm a bit confused as to how I would take the design below and convert it into a star schema. ...
J. DOE's user avatar
  • 47
4 votes
1 answer
7k views

Design star schema for many-many relationship

What are the steps/rules to build a DW star schema design from a production database; specifically, how do you handle many-to-many relationships. I understand how to take basic data including a many-...
hqt's user avatar
  • 199
0 votes
2 answers
2k views

Cons of using a star schema in a columnar database

My organisation is implementing a new data warehouse using a columnar data store (Redshift) and the performance is currently abysmal. There are many causes for this but I think the key reason is ...
roastbeeef's user avatar
0 votes
2 answers
151 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
6 votes
1 answer
1k views

When are dimension tables still needed using clustered columnstore indexing?

I am using MS SQL Server 2016 Clustered Columnstore Indexing (let's call it CCI) in my reporting database. In initial designs I was thinking star schema but then I started playing with CCI. Now I ...
Cyndi Baker's user avatar
5 votes
2 answers
7k 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'...
Krolique's 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 ...
crf's user avatar
  • 121
7 votes
2 answers
4k views

Benefit of having time dimension in a star schema?

What would be the benefits of having a time dimension in a star schema over having the time attributes in the fact table itself? For example: I have a transaction data with user information for ...
rolladice's user avatar
20 votes
2 answers
21k views

Difference between star schema and data cube?

I am involved in a new project where I have to create a data cube from the existing relational database system. I understood that the existing system is not properly designed, and I am not sure where ...
Rathish Kumar B's user avatar
2 votes
1 answer
449 views

Star schema dimension table design [closed]

In a star schema, can a dimension table such as customer_dim have a column that references another dimension? For example: customer_dim customer_id first_name last_name email ...
john's user avatar
  • 153
5 votes
1 answer
3k views

Building slowly changing dimension on a Fact/Dimension Star Schema

I have heard textbook definitions of how to design a star schema regarding what goes in the fact table and what goes in the dimension tables, such as: The fact table should contain core information ...
Michael Plautz's user avatar
1 vote
0 answers
104 views

Reversing many-to-many relationships when transforming data into a galaxy schema

I'm building a galaxy schema for our analytics and sales data from our normalized data tables. I'm unsure how to treat many-to-many relationships in the facts tables. For example, for each sale, we ...
Eran Galperin's user avatar
2 votes
2 answers
237 views

Cleanup / Prune Unreferenced Data in Dimension Tables

We have a star schema data warehouse running on MySQL 5.6. We keep a rolling 18 months of data in our fact tables using partitions by month. We have a number of dynamic dimension tables that are ...
Shaun's user avatar
  • 31
2 votes
1 answer
933 views

Data into Star Schema's fact or dimension table?

I'm working on creating a Star Schema for my internship that I want to use for reporting on the company's pre-sales process. The aspects of the pre-sales process that I want to report on, are: Sales ...
JDL's user avatar
  • 51
1 vote
0 answers
231 views

Star Schema Design - Multiple Sources

I am planning the design of a star schema for a data warehouse. The data will be loaded from 2 separate staging tables (Client and System). The end user will need to be able to compare any ...
K09's user avatar
  • 1,394
1 vote
1 answer
923 views

Star Schema - One dimension or split

We have a Data Warehouse, which has two dimensions, Product and ProductLine. Product has just one entry and there is just one ProductLine with a description that changed from transport to Transport. ...
Josh Alvo's user avatar
  • 105
4 votes
2 answers
3k views

Star Schema from Relational Database

I'm a newbie to data warehousing and I've been reading articles and watching videos on the principles but I'm a bit confused as to how I would take the design below and convert it into a star schema. ...
saj's user avatar
  • 93
0 votes
1 answer
148 views

FK Issue with Fact Table

I have a Datawarehouse star schema. One Dim table is 'DimTweet'. This contains tweet details. For a selected date I want to be able to display the number of tweets. However, I have a problem in my ...
K09's user avatar
  • 1,394
1 vote
2 answers
603 views

Non numeric attributes in fact table (to track data source)?

I am creating a data warehouse using star schemas, following the Kimball methodology. One of the rules is only put numeric values in fact tables. I am considering putting an "id" in a fact table, in ...
user2800708's user avatar
2 votes
2 answers
3k views

Do valid to/from columns on a fact table make sense?

I recently came across "valid from" and "valid to" columns on a fact table. Obviously this is common for dimensions but I've not seen them on fact tables before and can't find any info suggesting they'...
Matt's user avatar
  • 1,043
1 vote
0 answers
822 views

how many fact tables do I need given I want to build an OLAP for Quotation Line Item and Purchase Order Line Item report?

My client wants a report that looks something like this on a daily basis. +---------------+-------------------------+-----------------+---------------------+-------------+ | ProductNumber | ...
Kim Stacks's user avatar
0 votes
1 answer
4k views

What to do when facts change frequently in a star schema for data warehouse?

I have a Quotation Management system that I built as a CRUD web app. Over time, I realized I need to improve it so that management can have their reports. Hence I studied the Kimball method of data ...
Kim Stacks's user avatar
4 votes
1 answer
1k views

What known strategies, if any, can I use to validate a Slowly Changing Dimension?

I have a customer dimension in our data warehouse (SSAS 2014 Multidimensional). It is set up as a Type II SCD, with just Start and End Date fields to track changes. (null end date = current). As our ...
IronicMuffin's user avatar
3 votes
0 answers
2k views

How to insert values into Bridge Table in star schema?

I am modelling my database with respect to star schema with bridge tables. My master Table looks like below. I need to convert this format to star schema. MasterTable StudentNumber | Subject1 | ...
Nagej's user avatar
  • 31
8 votes
2 answers
18k views

Best approach for populating date dimension table

I am looking to populate a date dimension table in a SQL Server 2008 database. The fields in the table are as follows: [DateId] INT IDENTITY(1,1) PRIMARY KEY [DateTime] ...
JohnLinux's user avatar
  • 255
0 votes
1 answer
926 views

How do I choose primary keys for star schema BI data warehouse?

I am extracting sales turnover data from a Microsoft Dynamics Database into another SQL database that will be used by the BI tool of choice. This data is coming from G/L (general ledger) entry items. ...
JohnLinux's user avatar
  • 255
2 votes
1 answer
2k views

date dimension OLAP cube design

I have a sales reporting system with incoming data like this date,editionID,outletId,Measure1,Measure2 2013-01-01,2,532,11,24 etc... I am new to designing data cubes (for the pentaho BI suite) and ...
Killerpixler's user avatar
13 votes
2 answers
10k views

Alternative to EAV for dynamic fields in a star schema data warehouse

I need to support dynamic fields and values in a big datawarehouse for storing API requests log, my user case is that I need to store all API requests query string and able to perform query against ...
Howard's user avatar
  • 249
1 vote
1 answer
2k views

~2 Billion row fact table in star schema on Oracle. Advisable? [closed]

Let me start with: I am not a DBA; I am a backend developer. It has been suggested that, for a report schema, I implement a star schema where-in the fact table can have ~2B rows before being archived....
Christian Bongiorno's user avatar