Questions tagged [star-schema]
The star-schema tag has no usage guidance.
58 questions
0
votes
0
answers
12
views
Have I converted this DB table to the correct number of dimension tables for a star schema correctly?
I'm trying to replace a pretty complicated query for a dashboard. We currently use this system where we track practices, phone numbers and start/end dates partially in an excel spreadsheet and also in ...
- 101
0
votes
0
answers
52
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 ...
- 101
1
vote
0
answers
180
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 ...
- 11
0
votes
1
answer
75
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 ...
- 513
0
votes
1
answer
158
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 ...
- 1
1
vote
0
answers
190
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 ...
- 73
0
votes
0
answers
1k
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 ...
-1
votes
1
answer
447
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
...
- 265
2
votes
1
answer
998
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
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
171
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 ...
- 185
1
vote
2
answers
853
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 ...
- 13
0
votes
0
answers
198
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 ...
0
votes
1
answer
222
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"...
- 1,629
1
vote
1
answer
413
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 ...
- 119
1
vote
3
answers
754
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 ...
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 ...
- 1
0
votes
0
answers
89
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
1
vote
0
answers
327
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 ...
3
votes
0
answers
184
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
157
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 - ...
- 101
1
vote
2
answers
3k
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.
...
- 47
4
votes
1
answer
8k
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-...
- 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 ...
- 101
0
votes
2
answers
162
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
7
votes
1
answer
2k
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 ...
- 685
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'...
- 153
2
votes
2
answers
2k
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 ...
- 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 ...
- 73
20
votes
2
answers
22k
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 ...
- 2,300
2
votes
1
answer
454
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
...
- 153
5
votes
1
answer
4k
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 ...
- 165
1
vote
0
answers
111
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 ...
- 153
2
votes
2
answers
295
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 ...
- 31
2
votes
1
answer
982
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 ...
- 51
1
vote
0
answers
252
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 ...
- 1,414
1
vote
1
answer
942
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. ...
- 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.
...
- 93
0
votes
1
answer
151
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 ...
- 1,414
1
vote
2
answers
640
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 ...
- 243
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'...
- 1,043
1
vote
0
answers
831
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 | ...
- 215
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 ...
- 215
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 ...
- 663
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 | ...
- 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] ...
- 255
0
votes
1
answer
984
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.
...
- 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 ...
- 121
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 ...
- 249