Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [data-warehouse]

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

3
votes
3answers
449 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 ...
0
votes
0answers
38 views

Slowly Changing Dimension table Index Optimization

What is the optimal way to created index on slowly changing dimension tables which have Begin and EndDates? If I want to select when Customer owned house or enrolled in school. Should EndDate be in ...
2
votes
0answers
79 views

Kimball Data Warehouse: “Denormalize” Hierarchy Attributes

I have many different flat files. We have Property Owners, who own Properties, which are under a Tax Assessment (Tag Code). Property Owners flat file does not have SSN/Id, so sometimes same property ...
0
votes
0answers
28 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 ...
0
votes
1answer
47 views

Should I place Kimball Data Warehouse in Microsoft Columnstore Index on Premise?

Are Columnstore tables now the vendor default method for Kimball Data warehouse tables with Dim and Facts for On Premise? I know in the cloud, Microsoft automaticaly recommends Azure Data warehouses ...
1
vote
1answer
48 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 ...
0
votes
0answers
14 views

Analytics on a Microservice system

Say we got a microservice system. The system is designed to do support a companys every need, say for example a store needs inventory, accounting/economy, a website, etc, all of which in turn is ...
1
vote
2answers
35 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, ...
-1
votes
1answer
50 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. ...
-1
votes
1answer
46 views

SQL Store all CDC changes and automatically ship to History Table [closed]

We are creating Data Warehouse. Does SQL server have technology to capture all changes in a table, and load into cumulative table which shows total history? Example: If having OLTP Customer Table, ...
1
vote
1answer
56 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 ...
0
votes
0answers
21 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) ...
0
votes
1answer
53 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 ...
2
votes
0answers
35 views

Data Warehouse Kimball Interest Rate and Interest Payment Amount

I am modelling a bank account. Should interest rate be in the DimCheckingAccountId (which shows ProductDimensions, or should it be in fact table, or both). Same with another LoanAccount, should ...
5
votes
2answers
949 views

How many rows can SQL Server process in a single INSERT statement into a table?

To illustrate my question, the following is a query detecting order's id which have not been inserted in a data warehouse and inserts them: With NewOrders As ( Select OrderID From Orders ...
1
vote
2answers
92 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. ...
1
vote
2answers
152 views

Data Warehouse: Auto Generate Slowly Changing Dimension Code For Any Table with Dynamic Sql

Can someone code review to take OLTP/Stage data and transform it into Data Warehouse Kimball Slowly Changing Dimension Type 2? This code should be dynamic, and should be able to utilize any table as ...
0
votes
0answers
60 views

Kimball Modelling One Column Linking to Many Dimensions

We are conducting Quality inspections, Appraisals, and Ratings on furniture. Many different types of furniture exist, chairs (1), shelves (2), couches (3), table (4). The respective DimProductType is ...
1
vote
1answer
45 views

MySQL data warehouse calculate difference over years in percentage

I'm trying to create a query to calculate the percentage difference based on a time range (e.g. WHERE dim_date.year BETWEEN 2009 AND 2011) spreaded on months. My question is how to add the new ...
0
votes
3answers
210 views

Create Slowly Changing Dimension table from Repeated Data

I am receiving transaction snapshot data from a file, it contains a history of repeated data. Currently trying to find Slowly Changing Dimensions on table with [ProductId] Business Key. Many ...
9
votes
1answer
110 views

What is the difference between Federated and Decentralized Data Warehouse?

I can not find any clear definitions or explanations of any of these. Both seem decentralized. It appears that in Federated DWH, the data is distributed and not integrated into a single repository and ...
0
votes
2answers
14 views

Mapping product codes in CRM and ERP

I'm loading data into a data warehouse. Customers/transactions are coming from the ERP and from Salesforce. Product codes do not match up between the systems. Is there a standard pattern for solving ...
1
vote
1answer
46 views

Mongodb as a data ware house?

Will mongodb a good choice as a data warehouse considering following situation? We are building analytics, report generation platform for our client and a typical table growth in terms of rows will ...
1
vote
0answers
26 views

Increase frequency of updates (ETL process) to Data Warehouse

Our data warehouse is updated only once every 24 hours (at night). The ETL process takes a little over an hour to complete. I am wondering how to increase the frequency of the ETL process - so running ...
0
votes
0answers
34 views

Auto Publish Deploy same table to different Schema in Database Project

We have a table in stage schema called: staging.Customer, containing all the new incremental customers added recently in the system. The data gets inserted into table called dbo.Customer which has ...
1
vote
0answers
31 views

Best Distkey and Sortkey for the tables in Redshift

I have the following tables: orders: id => primary key customer_id => store_id => order_date => datetime order_items: id => primary key order_id => FK The query I usually run ...
4
votes
1answer
160 views

SQL Server Data Warehouse stored procedure location

We are building a Kimball data warehouse. We have a Staging Database, and Final Datawarehouse. We have a stored procedure which creates the table CustomerTransaction. Where is the best location to ...
0
votes
1answer
236 views

SQL Server Stored Procedure Slowly Changing Dimension

Can someone code review this slowly changing dimension for our Kimball Data warehouse? This is Type 2 dimension. I reviewed a lot of code on the internet, however I want to use non-TSQL specific ...
0
votes
2answers
142 views

SSIS ETL or CDC - real time DWH reporting

I am currently working on SSIS package for continuous incremental data loads of the tables (40 tables now and expected to increase in near future) from OLTP system to a Reporting database residing on ...
1
vote
2answers
87 views

Difference between OLTP and Inmon Data Warehouse

What is the general difference between OLTP and Inmon-style Datawarehouse? It seems like they are the same. OLTP just copies all the tables into a Read-Only Datawarehouse DB. I know what a Kimball ...
1
vote
1answer
135 views

Data Warehouse Kimball Customer and Customer Address Dimensions

I have a Customer Table and Multiple Customer Addresses. A customer can have multiple addresses: home, work, vacation,etc. In Kimball data warehouse, would I utilize a bridge table to go between ...
0
votes
1answer
22 views

SSRS and DBA Scope

Just a quick questions regarding DBAs, recently I was asked about the scope of DBA in terms of SSRS and BI, in your experience who handles and maintain/configure your SSRS, is this part of the DBA or ...
2
votes
4answers
180 views

Best practices for transferring data across long, wide tables

As part of my data warehouse project, I'll be transferring data from our OLTPs to the Data Warehouse. Some of the tables are long and wide so obviously, I'll only be transferring required columns. ...
1
vote
1answer
35 views

SQL Temporal Tables Include Current State

We want to implement temporal tables in SQL Server 2016. We are creating a data warehouse and developing type 2 slowly changing dimension tables. We noticed that history tables only include history. ...
0
votes
1answer
140 views

SQL Temporal Tables Begin and End Date

We want to implement temporal tables in SQL Server 2016. We are creating a Datawarehouse and developing Type 2 Slowly changing Dimension Tables. For our BeginDate and EndDate, we want them to be date,...
1
vote
1answer
56 views

Performance Tuning ETL

I administer a datawarehouse running on SQL Server 2016. This datawarehouse has most of its ETL processing done during a nightly load, except for a few SSIS packages that run throughout the day, then ...
0
votes
1answer
28 views

How I develop Union Report Views for Multiple Single Tenant Databases?

We have a database for each customer, over 20+ databases. All database schemas are the same. Now we want to create a Consolidated Reporting View for All Databases, for total database analysis. How ...
3
votes
2answers
172 views

How to Develop Union Reports from Single Tenant Databases?

We have a database for each customer, for over 700 databases. All the schemas are the same. Now we want to conduct Reporting for : All Databases Customer Analysis. Many db professionals prefer single ...
0
votes
2answers
109 views

Setting isolation level at database level

We have a data warehouse that's being dropped and reproduced every night. We have lots of SSRS reports that use the warehouse. Sometimes an SSRS report goes wrong and will never execute, thus ...
0
votes
1answer
58 views

Is this a good database design or transition? Normalize to Denormalized for Data Warehousing [closed]

As of now in sql server , we have a data and a series normalized table structure that is connected to a C# web app (Human Resource / Recruiting System) (slightly medium (or small 12,000 rows.) size ...
3
votes
1answer
153 views

Bad row estimate following Compute Scalar operator in plan

I'm struggling to understand where a row estimate is coming from in an execution plan. Paste the plan link declare @BatchKey INT = 1, @ParentBatchKey INT = 1, @QuoteRef varchar(50) = 'Q00018249', @...
2
votes
1answer
382 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-...
1
vote
1answer
118 views

Index Maintenance for DW- DB ( ETL process )

I'm planning to set up a t-sql job to perform index maintenance in a DW DB (Stores data through an ETL process) currently overnight and eventually will need to be run more frequently as the business ...
2
votes
1answer
45 views

Is it bad practice to use a data-warehouse for providing reorganized non-BI data?

The Primary goal is the case-optimized data representation. The idea is to gather data from several databases, save the data in a data-warehouse and restructure/connect the data in data marts(similar ...
0
votes
0answers
16 views

Extract max of value in Fact table using date filter

In my Data warehouse, there are Dim date and Dim City and a Fact table which at the end of each month logs the population of each city, I have a problem to calculate max population of each city in a ...
2
votes
1answer
83 views

Using On-Premise SQL Data Warehouse Data for OLTP applications

Quick Information: Our EDW group has created On-Premise SQL Data Warehouse by collecting information from several systems. These SQL DW databases are made up of: SQL Version: SQL Server 2016 (SP2) ...
0
votes
1answer
56 views

Using views as a source for staging table? [closed]

I am in the process of trying to build a small datamart for my small business. I am planning on initially copying tables from the source DB to another DB for staging, where I can then do any data ...
0
votes
0answers
22 views

Experience of using BEAM in building data warehouses

We are going to build a data warehouse. It is completely a new experience for us and we found out it is better to use agile methodologies. So we came up with Lawrence Corr book "Agile Data Warehouse ...
0
votes
0answers
27 views

Oracle Cost-Based Optimizer: how to use statistics on foreign key column of fact table when criteria is on the dimension table

Good morning all, I am new to this community and I did search for this question. Apologies if I'm asking something that's been asked before. I am working on a dimensional data warehouse with fact ...
0
votes
2answers
78 views

How to find a data warehouse's design diagram? [closed]

I have a database with lots of tables. I need to quickly find out what data warehouse schema was used. Specifically, looking for the dimensions and fact tables. There does not seem to be a standard ...