Questions tagged [data-warehouse]
A database system optimised for reporting, particularly in aggregate. Often, but not always implemented using a star schema.
427
questions
1
vote
0
answers
34
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
181
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
124
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
50
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
2
votes
2
answers
1k
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 ...
- 509
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
81
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
0
votes
0
answers
245
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 ...
- 173
0
votes
1
answer
43
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
147
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
36
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
58
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
316
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
151
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
147
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
61
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
2
votes
2
answers
253
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 ...
- 663
3
votes
1
answer
1k
views
What's the best way to concatenate a multi-column foreign key?
We are expanding our data warehouse to include a new source system. In the new system, we want a single column for a primary key, but in the old system it's two columns.
For exmaple:
create table #...
- 2,612
1
vote
2
answers
56
views
How does Data Warehousing work in reverse? [closed]
I've made many researches about data warehousing to learn more about it and I think I've grasped the idea. But I've read in an article or two that data warehousing allows you to do analysis on data ...
- 13
0
votes
0
answers
199
views
How to handle multiple changing ID's for a single person
I have a company with a single party table containing Member ID(the primary key), Subscriber ID, Member Sequence ID, Legacy ID, and a myriad of other identifiers from random source systems. Because ...
- 1
2
votes
2
answers
7k
views
Which DB for 1 table with 100 Million rows per year? [closed]
I've a questionnaire with 70 questions and need to store the answers.
Problem: it's 100 Million records per year.
I've experience with different types of storages, but never had to deal with these ...
- 123
8
votes
2
answers
2k
views
What is the best option for High availability on a data warehouse?
I have a customer who have an existing 1.5Tb data warehouse and are currently planning for a complete refresh of the DW on a new environment. Their infrastructure manager has organised 2 servers with ...
- 81
0
votes
0
answers
62
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 ...
- 141
0
votes
1
answer
113
views
Aggregating Multiple COTS Databases into a Data Warehouse or similar for Reporting
I am sure this is a very basic question, so I apologize, but I've read lots of articles and done a lot of google searches and have not found any leads for this particular challenge I am looking to ...
- 1
1
vote
1
answer
832
views
How do you partition an events table by a time based off the datediff(Lag,time)?
I am not sure how really to ask this question as i have never come across such grouping/ partitioning before.
Basically i have an events table with 50 million rows that give me a timestamp, user, ...
- 21
0
votes
1
answer
136
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 ...
- 21
0
votes
1
answer
298
views
Can a factless fact table be a bridge table?
As for bridge tables, they can be a helper table both for facts and dimensions. But can the factless fact table be a bridge table?
- 2,132
2
votes
1
answer
151
views
Why student attendance is not considered a fact in data warehousing?
In a KimballGroup example student attendance is not considered a fact. Hence it considers this example as forming factless fact table. I don't really understand what is the difference between a fact ...
- 2,132
0
votes
0
answers
25
views
How can I filter first then show values in BI?
As my title, I'm in this trouble. When I create a BI publisher report, it's show all of value, I just can filter after that.
Can someone tell me know how can I filter values before show it in BI ...
0
votes
1
answer
658
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 ...
- 544
1
vote
0
answers
961
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) ...
- 41
3
votes
1
answer
1k
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
...
- 41
0
votes
1
answer
60
views
Appropriate situation to data warehouse and how to best manage the warehouse? [closed]
I have a query that is on the opening page of our application that takes about 8-10 seconds to load. I've tried all I can to optimize it and I simply cannot make it go any faster (indexed ...
- 225
2
votes
1
answer
134
views
How to implement updates from mySQL operational DB to Azure SQL DB for reporting
We have an operational mySQL DB running on AWS for a transactional system and an Azure SQL DB for reporting with PowerBI. Now I'd like to regularly (e.g. every night) do an update of certain tables ...
0
votes
0
answers
379
views
does a fact table always need to have a field for aggregate?
When I was studying data warehouse modeling, the first thing I came into contact with was the order fact table, and the product, user, date dimension tables.
The order table will have quantity, ...
- 176
1
vote
0
answers
902
views
Where to build the OLAP cubes? [closed]
I have built a data warehouse with multiple fact tables and dimension tables (with a MySQL server).
Now where could I build the OLAP cubes and write the MDX queries to produce results?
BTW, I have ...
- 176
2
votes
1
answer
1k
views
Run SQL Agent Job 3 times in a Row
How do I execute SQL Agent job 3 times in a row?
We are creating Data Warehouse Project. It ETLs data from Flatfile Legacy Folder, and creates Dim and Fact tables.
SSIS Project Environment Variable (...
user173948
0
votes
1
answer
250
views
Do Indexes in the T-SQL Data Warehouse automatically go over and optimize into the SSAS Cube?
Do Indexes in the T-SQL Data Warehouse automatically go over and optimize into the SSAS Cube?
I am reading that applying indexes, will transfer into SSAS. I thought SQL and SSAS are totally different ...
user175631
4
votes
3
answers
2k
views
How to handle schema changes in source systems when running an ETL?
How do people typically handle schema changes in source systems when running an ETL as part of an EDW? For example when a column you were operating on disappears and your ETL breaks.
- 99
0
votes
1
answer
96
views
Compare Validate TSQL Data Warehouse to SSAS
How do I compare Data in T-SQL DataWarehouse to SSAS Cube?
I would like to conduct this from SSMS (SQL Server Studio Management Studio).
Example
Select CustomerName from dbo.DimCustomer where ...
user172734
0
votes
1
answer
1k
views
Kimball Data: Modeling Data as Both Fact and Dimension with View
In Data warehousing, Kimball discusses modeling an insurance policy premium as Both Dimension and Fact.
I have the following table,
create table dbo.DimAutoInsurance
(
DimAutoInsuranceId int ...
user172334
4
votes
3
answers
6k
views
Difference Between a Staging and the Production DWH
I have a general question on a Data Warehouse Architecture. I have heard a lot of a Staging and a Production, so that data should be loaded via SSIS first into a staging dwh and then via SSIS to the ...
- 155
0
votes
0
answers
114
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 ...
- 1
0
votes
1
answer
413
views
Column store index for on premise Kimball Data Warehouses
Are column store tables now the vendor default method for Kimball Data Warehouse tables with Dim and Facts for On Premise?
I know in the cloud, Microsoft automatically recommends Azure Data warehouses ...
user162943
1
vote
1
answer
964
views
When to Disable and Recreate Indexes in Data Warehouse ETL?
We are moving data from Staging Database into Kimball Data Warehouse DB.
I am hearing different ideas regarding when to disable and rebuild indexes.
Stage tables are truncated every day for each new ...
user162943
1
vote
2
answers
337
views
All Data Transformations via SSIS
Some folks here at my current job have made the decision that all data transformations that go into our database must be done via SSIS. This means that any old code such as views, stored procedures, ...
- 11
-2
votes
1
answer
654
views
Datawarehouse Staging Tables: Null or Not Null
We are loading data from OLTP into Staging Tables with SSIS, then into Kimball data warehouse with ETL process.
We are inquiring about staging tables, if they should have null/not null constraints. ...
user168914
1
vote
1
answer
760
views
Data Warehouse: Can a Transaction Table also be a Dimension?
We have a transaction table. Not only does sales team want to analyze by CustomerId or Productid sold, but also by sales amount sold or quantities.
Kimball states, a fact table here can also be a ...
user162943
0
votes
0
answers
25
views
Oracle Best-Buyer-Customer is Wholesale or Retail buyer
I have data warehouse with Fact table Orders, and CustomerDimension, and TimeDimention, and BookDimension.
CREATE TABLE DIM_CUSTOMER(
DIMCUSTID NUMBER(6) PRIMARY KEY,
CustId NUMBER(4) ...
- 47
1
vote
1
answer
734
views
Best way to capture changes on OLTP for Data Warehouse: Create/Update column or CDC
What is a preferable way to move ETL data into Data Warehouse? Create/Update date on the OLTP application, or Change Data Capture (CDC)?
Given the two are allowed on a system and resourcing is not ...
user162943