Questions tagged [star-schema]
The star-schema tag has no usage guidance.
43
questions
0
votes
0answers
26 views
Storing raw results in a relational database
I have used a star-schema to build a manufacturing database. I'm receiving results of tests (eg. Temperature: 273 K) and I'm storing them in a fact table (transactions).
The problem is, some machines ...
2
votes
3answers
33 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
1answer
12 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 ...
0
votes
0answers
16 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 ...
1
vote
0answers
45 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 ...
1
vote
0answers
28 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
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 - ...
1
vote
2answers
958 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.
...
3
votes
1answer
2k 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-...
0
votes
2answers
941 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 ...
0
votes
2answers
92 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 ...
3
votes
2answers
200 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 ...
4
votes
1answer
757 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 ...
4
votes
2answers
4k 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'...
2
votes
2answers
806 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 ...
6
votes
2answers
2k 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 ...
2
votes
1answer
302 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
...
5
votes
1answer
2k 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 ...
1
vote
0answers
61 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 ...
1
vote
2answers
96 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 ...
2
votes
1answer
676 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 ...
1
vote
0answers
149 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
vote
1answer
494 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. ...
3
votes
1answer
2k 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.
...
0
votes
1answer
118 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
vote
2answers
371 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 ...
2
votes
2answers
1k 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
vote
0answers
713 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 | ...
0
votes
1answer
3k 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 ...
4
votes
1answer
874 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 ...
3
votes
0answers
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 | ...
8
votes
1answer
16k 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] ...
0
votes
1answer
627 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.
...
2
votes
1answer
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 ...
13
votes
2answers
9k 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 ...
1
vote
1answer
1k 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....
5
votes
1answer
3k views
Schema for analytics table in Postgres
We use Postgres for analytics (star schema).
Every few seconds we get reports on ~500 metrics types.
The simplest schema would be:
timestamp metric_type value
78930890 FOO ...
4
votes
1answer
536 views
How to handle “many columns” in OLAP RDBMS
I have a fact that has around 1K different numerical attributes (i.e. columns). I would like to store this in to a column-oriented DB and perform cube analysis on it.
I tried to design a star ...
4
votes
1answer
720 views
Should I snowflake or duplicate it across my facts?
I'm building up a data warehouse to be used by SSAS to create cubes on, and I'm debating between two possible schemas.
In my warehouse, I've got two different fact tables that tracking daily changes ...
2
votes
1answer
944 views
IP Address lookup star schema design
I am working on a project in which we need to do IP Address lookups. So for this we have got the datasets and now the next steps is to populate this datasets in to the table. And then have some ...
4
votes
2answers
2k views
Open source and commercial tools to build dynamic queries of fact tables in star schema [closed]
What user interfaces or tools (commercial and/or open source) can query a star schema model (Ralph Kimball dimensional model)? So for example, if we have X fact tables, and Y dimension tables, and Z ...
2
votes
2answers
2k views
Data warehouse support tickets
I'm starting to design a data warehouse for a company. The first questions we're trying to resolve are regarding their support ticketing system. My initial schema is as follows
Now one of the ...
5
votes
2answers
4k views
Cardinality rule for bitmap indexes
The Oracle documentation includes the following advice:
A bitmap index should be built on each of the foreign key columns of
the fact table or tables
In that reference, there is even a bitmap ...
