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 ...
