Questions tagged [data-warehouse]
A database system optimised for reporting, particularly in aggregate. Often, but not always implemented using a star schema.
414
questions
0
votes
0
answers
22
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)...
- 101
0
votes
0
answers
23
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-...
- 1
2
votes
1
answer
31
views
Data lake or data warehouse first?
I have been confused whether to create a data lake or a data warehouse and hope some experienced real-world professional can give me some enlightenment.
I will like to store, visualise and perform ...
- 123
0
votes
1
answer
19
views
Relations between fact tables in a data warehouse
I'm designing a data warehouse and I keep reading that there should be no relations between fact tables.
I'm sure everyone here is well aware that businesses are full of facts which are inherently ...
- 621
0
votes
1
answer
18
views
Fact table linking to natual key rather than row key in a slowly changing dimension table?
I’m designing a data warehouse and have a Slowly Changing Dimension Type 2 table something like this:
UserKey
UserID
Group
EffectiveDate
ExpiryDate
IsCurrent
1
1001
GR1
2021-01-01
9999-12-31
Y
2
...
- 621
0
votes
0
answers
94
views
OBT (One Big Table) vs Star Schema for Data Warehousing
I am trying to prepare myself for some interviews at a FAANG, while coming from a much smaller company. Part of the interview process involves data modeling and ETL design. Kimball's "The Data ...
- 101
0
votes
1
answer
53
views
Table design to store Refund Transaction information
I have a transaction table that contains purchases and refunds. I'd like to create a linkage between purchase and refunds in the same table (I decided not to use a separate table). I'm not sure which ...
- 1,222
0
votes
0
answers
15
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,...
- 141
0
votes
1
answer
51
views
How to handle incoming time-series data in a data warehouse?
I am new to data warehousing (having learnt this in my school days) and is looking to do a data warehouse as a side project. Below is a simple data warehouse design I came up with:
The data warehouse ...
- 103
0
votes
2
answers
55
views
From what volumes of data do data ingestion tools like apache nifi, flume, storm or tools like logstash become relevant?
I'm working in a company that has two legacy data warehouses, which have evolved to unmaintainable monoliths throughout the time. Therefore, they are in dire need of re-form.
I'm investigating a ...
- 103
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. ...
- 2,223
1
vote
0
answers
95
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
41
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 ...
- 266
0
votes
2
answers
49
views
how do modern data warehouses tackle frequent small writes? esp. when streaming data is one of the sources? [closed]
So for many days, I had a question in mind.
How do modern data warehouses tackle frequent small writes? esp. when streaming data is one of the sources?
e.g. Kafka/Kinesis => DW(Snowflake, Teradata, ...
0
votes
0
answers
57
views
What are the table naming conventions in tabular models?
With SSAS multi-dimensional models we use star schemas and cubes as our data source. With tabular models we use relational databases. Therefore, with tabular models naming our tables Dim_* and Fact_* ...
- 455
0
votes
1
answer
72
views
Setting up database for date dimension table [closed]
I was working on creating database for warehouse purpose. I have many dimension table and one fact table. I have created separate create_date dimension table but It is not working as I want it to. ...
- 101
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 :
...
- 1,294
0
votes
0
answers
86
views
Would Star Schema be useful for a Lakehouse table accessed from PowerBI?
In a Lakehouse, the data is stored in a datalake (deltalake in to be precise in my case) instead of a data warehouse. The data there is stored in parquet format, which is a columnar format. I am ...
MetallicPriest
0
votes
1
answer
330
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 ...
- 103
1
vote
0
answers
24
views
History data in CUBE
Not sure if this is the right place to ask.
I am working on an ETL procedure that gathers the data about medication consumption and I have something like this:
Dim_Patient, the dimension for all the ...
- 111
0
votes
2
answers
128
views
Storing Live Timeseries Data in SQL Server Azure Database
I have an SQL Server Azure Database and am trying to store time-series data that I get from multiple sensors.
Data Source:- 5-minute data is obtained via an API.
Current table structure: -
Timestamp |...
- 15
1
vote
1
answer
170
views
Is "ETL vs ELT" just "tables vs views"?
TL;DR; Is the difference between ETL and ELT just whether or not the transformed data is a concretion vs abstraction before being loaded into the warehouse?
So forgive me the broadness of the subject ...
- 207
1
vote
1
answer
281
views
MariaDB Column Store Suitability for Data Warehouse
We want to build a data warehouse to distribute valid information across the organization (reporting) and get some insights into our data (analytics). After some research we found MariaDB using Column ...
- 13
1
vote
1
answer
128
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
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 ...
- 1
0
votes
1
answer
689
views
SSAS error deploying project: Ensure that 'SQL Browser' service is running
After successfully building my project, I got this error when deploying it:
The project could not be deployed to the '\SQLEXPRESS' server because of the following connectivity problems :
A connection ...
- 3
0
votes
0
answers
91
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
36
views
Leaf Joins in Data Warehouse Design
Upon modeling my very first data warehouse model (for historical stock market data), I stumbled across a data relationship that I don't know how to best account for. The fact tables include multiple ...
0
votes
1
answer
244
views
Best Practice for Handling Table with Large Amount of Transaction Data
I have a transaction table storing large amount of transaction data. Only the most recent data of one month are required to be available for transactional use in real-time. Less recent data may only ...
- 3
0
votes
2
answers
321
views
Replicating PostgreSQL data into Citus/Greenplum?
I need to integrate data from 3 different PostgreSQL databases (OLTP application backends) in a data warehouse. For the data warehouse itself I consider using Citus or Greenplum. There is a ...
1
vote
2
answers
53
views
best approach to model relation between customers - dimension dim_customer (SCD2) which can be a HEAD/SUB customer
Hi there we have the concept of having department. each department can either be its own head or it can have a head department. (similar to each person in a organisation is a employee but some ...
- 11
1
vote
1
answer
72
views
How can Amazon Redshift be built based on PostgreSQL if Postgres is row-based?
According to Amazon, Redshift is based on PostgreSQL, and is a column-oriented database management system. This looks to me as a very self-conflicting statement, isn't Postgres a row-oriented database?...
- 63
1
vote
0
answers
26
views
How are the relations from Relational Database mapped to DataWarehouse?
this year one of our collage courses was related to creating and managing data warehouses. There is one thing that I really struggle to understand and I would appreciate any kind of advice that you ...
- 11
1
vote
0
answers
97
views
Flags Cloud SQL for Postgresql
Context:
I have a process that runs daily, with different amounts of data, if the process is on the weekday (process last 30 days), for weekends (process last year). The input is of 10 tables of ...
2
votes
1
answer
53
views
What are the disadadvantages of a datawarehouse with no datawarehouse tables, but only datamarts?
The ETL design pattern prescribes transformation from staging tables to a central data warehouse data model, followed by extracting data from the data warehouse data model to data marts.
Are there any ...
- 21
0
votes
0
answers
25
views
Distinguishing between fact and dimension tables in data warehouse
Is it true that facts in a fact table of a data warehouse always have a relation to time? I.e. is it true that a fact must always be assignable to a specific point in time?
If this is correct, can we ...
- 101
0
votes
0
answers
31
views
db choice for activity log
I'm working on a geo-replicated web platform, composed of an ecosystem of microservices.
We need to improve and rework the user activity tracing pipeline and I'm looking for "the best" ...
3
votes
2
answers
474
views
Consistent data in dimension and fact tables from multiple incrementally loaded staging tables
To create a data model for our data warehouse we use tooling supplied by the ERP vendor. This does probably matter due to the fact that is has it's limitations. We inherited this environment with a ...
- 445
0
votes
1
answer
858
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
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 ...
- 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 ...
- 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 ...
- 103
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 ...
- 101
1
vote
0
answers
30
views
Implementing data warehouse on the cloud [closed]
I need to implement a data warehouse on the cloud for a mid-sized company. The database size will be around 200 Gb.
My options are (so far):
AWS with its database Redshift
Azure with its database ...
- 51
0
votes
0
answers
33
views
Trying to do a fact and dim table in Sql
so far this is what i have code wise, but im getting an error 1239 . Is it not referencing something correctly .
CREATE TABLE CustomerDim
(
C_CustomerID_Cu INT(10) NOT NULL,
...
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 ...
2
votes
2
answers
117
views
Handling Big Data
I have a MySQL database that will have 2000 new rows inserted / second. These rows indicate the value of a sensor at that particular moment.
The plan is to get this data into graphical ...
- 125
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 ...
- 131
0
votes
0
answers
27
views
implementing one to many relation between 2 dimensions in dataware house
I am designing a Dataware house for the Stackoverflow job section. I have two dimensions: job opportunity and company. every record of job opportunity is referenced to only one record of company ...
- 103
1
vote
2
answers
184
views
Strategies/Resources for implementing data lineage in an ETL process?
I'm struggling to articulate my need here.
I've been tasked with developing a significant ETL process dealing with warehouse item costs in a major grocery chain using the full Microsoft Stack.
I'd ...
- 653