Questions tagged [data-warehouse]

A database system optimised for reporting, particularly in aggregate. Often, but not always implemented using a star schema.

Filter by
Sorted by
Tagged with
1
vote
1answer
18 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 ...
0
votes
1answer
23 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 ...
0
votes
1answer
27 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 ...
0
votes
0answers
33 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
1answer
18 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
1answer
51 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 ...
0
votes
1answer
48 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
2answers
38 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 ...
1
vote
1answer
35 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?...
1
vote
0answers
19 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 ...
1
vote
0answers
38 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
1answer
38 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 ...
0
votes
0answers
22 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 ...
0
votes
0answers
24 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
2answers
254 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 ...
0
votes
0answers
12 views

Copy DWH for reports

I have a heavy load DWH server. I wish to create a copy of that server for report purposes (Cognos reports to be exact). In today's situation I have many reports and can cause locks on other processes,...
0
votes
1answer
247 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
27 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 ...
0
votes
0answers
40 views

Oracle - SCD Type 2

I am new to Oracle I have 2 tables called dwh_tbl_dim_supplier_type2_ar (target table) stg_tbl_dim_supplier_ar (source table) dwh_tbl_dim_supplier_type2_ar contains 6 column SUPPLIER_KEY (PK) ...
0
votes
0answers
53 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 ...
0
votes
1answer
27 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 ...
0
votes
1answer
24 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 ...
1
vote
0answers
24 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 ...
0
votes
0answers
17 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
0answers
116 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
2answers
94 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 ...
2
votes
0answers
51 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
0answers
22 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 ...
1
vote
2answers
145 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 ...
3
votes
1answer
729 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 #...
1
vote
2answers
50 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 ...
0
votes
0answers
30 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 ...
2
votes
2answers
1k 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 ...
7
votes
2answers
1k 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 ...
0
votes
0answers
42 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 ...
0
votes
1answer
67 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
vote
1answer
50 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, ...
0
votes
1answer
26 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 ...
0
votes
1answer
127 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
votes
1answer
78 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 ...
0
votes
0answers
21 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
1answer
376 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 ...
0
votes
0answers
304 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) ...
3
votes
1answer
533 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 ...
0
votes
1answer
44 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 ...
1
vote
1answer
337 views

Error on Datastage when I try to write on AS400

I'm having troubles to operate an ETL process on DataStage. I have the following error log: #TABLE#,0: ODBC function "SQLExecute" reported: SQLSTATE = HY000: Native Error Code = -332: Msg = [IBM(...
2
votes
0answers
41 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
0answers
54 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, ...
1
vote
0answers
622 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 ...
2
votes
1answer
676 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 (...

1
2 3 4 5
8