A database system optimised for reporting, particularly in aggregate. Often, but not always implemented using a star schema.
1
vote
0answers
27 views
Optimize Data Warehouse Design
I am working on a project for school (lab, not homework) and we are asked to fix any design problems that we found in the data warehouse implementation. It seems like we could probably get rid of a ...
0
votes
2answers
31 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 ...
0
votes
2answers
32 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
40 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
18 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 ...
0
votes
1answer
21 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. To ...
1
vote
1answer
26 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
31 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
45 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 ...
0
votes
0answers
20 views
How to Source Control Reporting 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 ...
3
votes
2answers
168 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
60 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
42 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
93 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
117 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-...
0
votes
0answers
24 views
PLSQL procedure from OWB mapping?
Is it possible to get the PLSQL procedure from an OWB mapping?
Could OMB Plus help?
1
vote
1answer
105 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
55 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
47 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
19 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
24 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
73 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
58 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
81 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
115 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
44 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
159 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
687 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
39 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
77 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
1answer
35 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
85 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
49 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 ...
3
votes
2answers
311 views
Always On Availability Groups as a Secondary Read Only Database [closed]
I am working on setting up a secondary copy of our prod DB for Data Warehousing and even something we can possibly use to offload other processes and reporting on. Basically just a read only copy of ...
4
votes
1answer
80 views
Copy to Data Warehouse
I have been tasked with copying select databases from the production SQL server to the Data Warehouse (at least nightly). However, no matter how I try to copy the data over, I run into an issue. Below ...
2
votes
1answer
238 views
When are dimension tables still needed using clustered columnstore indexing?
I am using MS SQL Server 2016 Clustered Columnstore Indexing (let's call it CCI) in my reporting database.
In initial designs I was thinking star schema but then I started playing with CCI. Now I ...
0
votes
1answer
75 views
Fetch data from SQL Server table of the ERP DB
I have pretty old ERP system running on SQL Server 2005 (about 20GB database. Largest able has 2 million rows). Unfortunately, the ERP can't run on SQL Server 2008 or newer. I have a huge amount of ...
3
votes
2answers
1k views
What are the difference between a “dimension” table in a star-schema and a “lookup” table in a relational database?
I'm trying to design a star-schema fact table along with some dimension tables that will surround it. If I reuse the natural key called customer_key in both the fact_table and dim_customer then I don'...
1
vote
1answer
449 views
'Refreshing' SSRS subscriptions (parameter values not valid due to lack of data in the tables)
In a place where I work we have lots of subscriptions set for SSRS reports. Some reports have as many as 100 subscriptions set up.
Our data warehouse is refreshed every night, with all the data ...
1
vote
0answers
56 views
Where to start for DW/BI project?
I work for a small technology MSP. Having just completed my Bachelor's in Information Science, with a focus in data science, my boss has asked me to build a data warehouse (I think he really means a ...
0
votes
1answer
220 views
Oracle - Query to join fact table to bridge table and show bridge group rows in one row?
I have a data warehouse with a One-To-Many relationship:
"Fact" table contains: Product_Group_ID
"Product_Bridge" table contains: Product_Group_ID, Product_ID
"Product" table contains: Product_ID, ...
1
vote
1answer
218 views
How to handle null value number DataWarehouse [duplicate]
I have a question.
I'm developing a Data Warehouse, I retrieve the data from my reconciled level, this in order to keep updated my Data Warehouse, but i am not sure how to handle some numeric fields ...
0
votes
1answer
215 views
Data Warehouse: Staging Area, DWH databases and ETL tool on one server? [closed]
I am thinking about a DWH server layout and I'd like to ask you if you can spot any downsides to this layout. The basic idea is to have Staging Area & Data Warehouse DBs and the ETL Tool for the ...
1
vote
2answers
68 views
Approaches for handling nvarchar sources to data warehouse
Situation is this...our data warehouse doesn't need nvarchar and is built using all non-unicode datatypes. But all new source systems are all coming in with unicode (utf8 Oracle or nvarchar in ...
0
votes
0answers
121 views
How to modify table in order to store historical information using SCD2 in ORACLE SYNTAX?
I have to modify my table PRODUCT_DIM so that I can be able to store historical information according to SCD2
A Type 2 SCD retains the full history of values. When the value of a
chosen attribute ...
