Questions tagged [data-warehouse]
A database system optimised for reporting, particularly in aggregate. Often, but not always implemented using a star schema.
0
votes
0answers
3 views
migrating data pipeline to AWS Glue
I was trying AWS Glue to migrate our current data pipeline from python scripts to AWS Glue . I was able to setup a crawler to pull the schema for the different Postgres databases . However, I am ...
0
votes
0answers
14 views
Tool for ETL in oracle 11g [on hold]
I'm new to data warehousing
I have now a relational db and data on it
I want to move this origin data to warehouse using ETL tool
I'm using oracle 11g & sql developper
So i need a simple free tool ...
2
votes
2answers
55 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
votes
2answers
82 views
Data Warehouse: Auto Generate Slowly Changing Dimension Code For Any Table with Dynamic Sql
Is there any code to take OLTP/Stage data and transform it into Data Warehouse Kimball Slowly Changing Dimension Type 2? The code should be dynamic, and should be able to utilize any table as input.
...
0
votes
0answers
51 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
23 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 ...
-1
votes
3answers
162 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
74 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
11 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
42 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
19 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
25 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
21 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
130 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
94 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
64 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
49 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
62 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
19 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
125 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
31 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
56 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
48 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
169 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
76 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
48 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
108 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
242 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
106 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
40 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
13 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
68 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
50 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
77 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 ...
0
votes
1answer
61 views
SQL Indexed View for Datamart Reporting with Transactional Replication
Should Indexed Views be utilized/recommended as method to auto-generate DataMart Reporting tables?
We have a Customer timeline of tables which can be simply joined on CustomerId in each tables.
...
0
votes
0answers
100 views
Cycle time calculation in TFS with a data warehouse query
Moved from here.
This is a simple code to extract cycle time per project team from Team Foundation Server data warehouse.
Here is the relationships of tables in data warehouse:
We use Sql Server ...
5
votes
0answers
124 views
Using the Schema Generation Wizard to regenerate a previously-generated schema
Lately, I've been trying to clean up a group of projects that my company received from an outside contractor. Among them is an SSAS cube, with an associated data warehouse.
The contractors generated ...
-2
votes
1answer
49 views
Microsoft exam Implementing a data warehouse [closed]
I am going to give exam of microsoft 70-767 implementing a dwh
Can somebody advise me anything important related to exam or if anybody can provide me some practice test that will be really helpful
...
1
vote
0answers
202 views
Kimball Cross Reference Table/Bridge Table
I'm currently struggling to understand how to apply Kimball methodology to a certain scenario and talk of Outriggers/Cross Reference/Bridge tables are starting to get confusing.
Consider the scenario:...
5
votes
4answers
794 views
Best way to store physical stock data in a SQL db
I work for a retail company and we are in the process of building a new data warehouse. One element we need to store is physical stock data as to how many units of any sku are available at a certain ...
5
votes
1answer
42 views
If a DW fact table is not uniquely identified by all dimensions, what problems?
This is a bit of a thought problem I've been wrestling with. I have a visceral antipathy to the notion of duplicate combinations of dimension values in a fact table. I've read a lot about the ...
1
vote
0answers
87 views
Are there differences between a Fact Constellation Schema and a Galaxy Schema?
I am currently working on the two schemes and I'm just not sure if they are the same or if there are differences. In the literature both are often synonymous. Now I've read that there are differences (...
0
votes
2answers
46 views
Is there a recommended order to create a DV model and star schema?
I want to create and implement a Data Vault Model. In the presentation layer, I want to create a View based on a star schema. Now I would like to know if there is a certain order in creating the ...
3
votes
2answers
92 views
Should I use separate address dimensions?
I have a Star Schema model with two Dimensions (User and Store) each of them have addresses, which I store.
Each User and Store can just have one Address. Is it recommended to split the Dimension ...
0
votes
0answers
31 views
Dimensional modeling and doing Sentiment analysis
I would like to try a simple Sentiment analysis with a R Script.
I created a dimensional model but I'm not sure how to handle the user comments. I hope someone can give me some advices.
I thought ...
2
votes
1answer
52 views
EDW index question
I have been assigned the task of designing a dimension or fact table for an EDW.
I know that a SQL Server table supports only one rowstore clustered index (conventional clustered index) and that you ...
-1
votes
2answers
51 views
How to implement GROUP BY aggregation with holistic aggregation if partitions do not fit into main memory?
Let's say I want to compute the median of some data given some grouping attributes G, e.g. the median price of all product sold daily (assuming a star-schema)
SELECT T.day_id, MEDIAN(price)
FROM ...
