Questions tagged [data-warehouse]
A database system optimised for reporting, particularly in aggregate. Often, but not always implemented using a star schema.
0
votes
1answer
23 views
Data Warehouse Modeling: Sales Order Charges
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
14 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
98 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
28 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
40 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
22 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
20 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
17 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
97 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 (...
0
votes
1answer
77 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 ...
3
votes
3answers
104 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.
0
votes
1answer
39 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 ...
-3
votes
1answer
49 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 ...
0
votes
0answers
36 views
SSAS Find Distinct Business Key Count for Fact Table
We have Dimension Table and Fact Table in SSAS Cube.
The DimCustomer table is Slowly changing Dimension, it tells when Business Key eg: 2, Joe Smith, changes his address, phone number. Hence, a ...
3
votes
3answers
510 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
31 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
votes
1answer
67 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
62 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
16 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
44 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, ...
-2
votes
1answer
86 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
vote
1answer
60 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
116 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
40 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
988 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
247 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.
...
0
votes
2answers
234 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
65 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
92 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
335 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
303 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
18 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
54 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
55 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 ...
1
vote
0answers
48 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 ...
3
votes
1answer
241 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 ...
-1
votes
1answer
466 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
277 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
121 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
306 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
24 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
241 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
45 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
251 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
74 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
176 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 ...
1
vote
2answers
217 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
77 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 ...
