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

learn more… | top users | synonyms

1
vote
2answers
55 views

Ordering of a dimension in sql server datawarehouse

Built and deployed a simple cube with a date dimension. The dimension has things like day of month. How do i sort it? it always shows in the reports as 1, 10, 11, 12, ... 19, 2, 20, 21 Design. ...
4
votes
1answer
201 views

Server roles in SQL Server 2016

There are 3 new server roles in SQL Server 2016: mediumrc largerc xlargerc Any idea what these are for? Google doesn't give any information about these.
6
votes
1answer
8k views

Best approach for populating date dimension table

I am looking to populate a date dimension table in a SQL Server 2008 database. The fields in the table are as follows: [DateId] INT IDENTITY(1,1) PRIMARY KEY [DateTime] ...
3
votes
2answers
275 views

Geographic Hierarchies in a OLAP Cube / Data Warehouse

Are the following geographic hierarchies correct? Do any US/Canadian area codes cross state/province lines? Do any US/Canadian counties cross state/province lines? Are there countries with counties ...
6
votes
1answer
107 views

How do you index alphanumeric foreign keys on large fact tables?

I have a field that is an alphanumeric field that ideally is a encrypted field for a non-unique identifier. It's used to relate other rather large fact tables in a many-to-many relationship. I do not ...
4
votes
1answer
51 views

SQL Server Edition for MS 70-463

I just started the MS 70-463 Implementation of warehouse, on the video course the tutor is directing towards SQL Server 2012 Evaluation Edition, but I have already have a SQL Server 2012 Express. Can ...
1
vote
2answers
233 views

Business Logic in Data Warehouse

my question is: Should business logic be stored in the data warehouse data store? Or should it only exist in the reporting applications? To give a more concrete example: In the CRM operational ...
14
votes
1answer
342 views

Query strategies using SQL Server 2016 system-versioned temporal tables for Slowly-Changing Dimensions

When using a system-versioned temporal table (new in SQL Server 2016), what are the query authoring and performance implications when this feature is used to handle Slowly Changing Dimensions in a ...
1
vote
1answer
77 views

SELECT Causes Performance Issue [closed]

I have a SQL SELECT statement that has joins on 27 of our warehouse tables. Original statement was taking more than an hour everyday so I decided to optimize it. I created some indexes but still it ...
1
vote
1answer
37 views

What options are there for rebuilds of indexes on large tables for a data warehouse?

I have a bulk loading process that loads millions of records into a few fact tables within a warehouse. Those tables are primarily clustered on time. The non-clustered indexes are in place for how the ...
0
votes
0answers
24 views

commonly used diagram for ETL

A the title indicates. Am i wondering what is the most commonly used diagram to explain an ETL process? In my case the process is rather linear, would a flowchart suffice? or is a Use-case also ...
1
vote
2answers
51 views

SQL Data warehouse and normalization forms

I don't know if it's a simple question but does a data warehouse have different rules when it comes to normalization forms? At the moment I am creating some tables in a data warehouse, and they ...
5
votes
0answers
119 views

Why should I “Avoid partitioning dimension tables” on SQL Server?

SQL Cat have a list of tips titled Top 10 Best Practices for Building a Large Scale Relational Data Warehouse. Under section 4 - Design dimension tables appropriately they state: Avoid ...
0
votes
0answers
24 views

Which Azure service for use as data warehouse/analysis store?

We currently have a database running in SQL Server 2012 Standard. We need to export data periodically (a "data dump") of a particular set of data to "a location to be decided" where the client's ...
0
votes
2answers
39 views

How can I handle delayed records a month or older on a table clustered on time?

I have a two part question really for anyone working with data warehousing where they have large fact tables. Question 1 Lets say you have a table that has 500 million or more records in it that is ...
0
votes
0answers
17 views

Database choice for structured big data to be queried with SQL

I have a system that harvests and registers a total of 500K (master table) and 100 million (detail table) into a two table master/detail database, per day. The detail table has around 50 fields, two ...
1
vote
0answers
24 views

Which Azure Storage / Database Technology to make use of for large scale logging

I am looking into making use of Azure hosting services in order to develop a logging service for our company. Azure has a number of storage options available (I have listed links below). However I am ...
0
votes
1answer
60 views

Azure TempDB and Log files

I'm setting up an Azure D11 SQL Server to be used for a data warehouse. I can only attach 4 data disks and have done so, now I need to determine how to configure them and split the DB files. I'm ...
0
votes
1answer
60 views

Database modelling : Creating a model for restaurant application which gives statistics

I am working on a server-side application in which users will be able to give ratings and comments to different factors of a 'Restaurant'. What the webapp does is takes all the ratings and creates ...
1
vote
2answers
23 views

Non numeric attributes in fact table (to track data source)?

I am creating a data warehouse using star schemas, following the Kimball methodology. One of the rules is only put numeric values in fact tables. I am considering putting an "id" in a fact table, in ...
1
vote
2answers
35 views

Should I convert timezones when moving data to a report server/data warehouse

I'm writing an SSIS Package to take data from a production DB to a reports server/data warehouse. The other databases on my reports server are all in Mountain Time but this DB is in UTC should I ...
2
votes
1answer
112 views

Update OLAP Cube data

We are using OLAP in our project and we are facing a scenario where we need to update the cube with real time data on daily basis. I need some suggestions about an optimal way for updating the data ...
1
vote
1answer
33 views

DSV - SSAS Project - New 2 Columns In Table Change More then I Planned

I encountered a very weird thing. I added in my DWH table 2 new columns but when I refreshed the DSV after adding this to my multidimensional project I saw that more (3) columns have been "changed". ...
0
votes
0answers
26 views

Fact Table with day grain that has many records that don't change

So I have a daily snapshot fact table that stores financial account balances. Normally, this would be pretty easy, just have a record for every day for every account. My problem is that table would ...
0
votes
1answer
203 views

apply cdc to existing data warehouse

I'm building an education data warehouse. I have various dimensions that i would like to track changes to i.e. DimStudent, DimClass, DimCollege, DimExamPaper etc. etc. My solution currently drops ...
0
votes
1answer
89 views

Time out while connecting to the database. Current time out value is 30 seconds

I am installing a repository creation utility on a windows 8 x64 bit system and getting the following error: Time out while connecting to the database. Current time out value is 30 seconds When ...
2
votes
3answers
24 views

Data Warehouse attribute that describes its timeframe?

I am writing a document about a Data Warehouse system, and would like to know if is there a single word (noun) that describes the DW attribute of how many time it contains? I mean, if a DW contains ...
0
votes
2answers
218 views

Daily snapshot fact table

I have a list of products per costumer. Costumers can delete or add products to their list. I want to know in each day what is the list of products (and count) by costumer. My first approach is to ...
2
votes
1answer
550 views

creating a report on the mysql jasperReport server

I have mysql local database, in it I have sample data mart. I have installed jasperReport server to my computer. Just using this tool, I want connect to the local mysql database and just create a pdf ...
1
vote
1answer
66 views

When to run ETL for dimensions

We have a suite of restaurant web applications that stores data in SQL Server 2012 on database server. We have now started to move data for reporting onto a separate data warehouse server. The data ...
0
votes
1answer
56 views

SSAS Calculated Field - rolling sum, for a year of days

I have a fact table storing employee occurrences incurred day over day. I need to have a running sum over the past year of an employee's life. I had, on first pass, done this on ETL, making this ...
1
vote
1answer
22 views

How to know if a particular DW project is using Kimball methodology

When I look at the data warehouse/BI/ETL processes in my group, how can I tell if we are using Kimball methodology or not? If I use Slowly Changing Dimensions i.e expire the old record and create a ...
2
votes
1answer
54 views

Mixed granularity weeks/months time series data in a data warehouse?

I'm not sure that "anti-aliasing of time series data" is the correct terminology to use, so let me explain: I have some sources of data that are aligned quarterly, mostly to do with quarterly running ...
4
votes
1answer
105 views

What known strategies, if any, can I use to validate a Slowly Changing Dimension?

I have a customer dimension in our data warehouse (SSAS 2014 Multidimensional). It is set up as a Type II SCD, with just Start and End Date fields to track changes. (null end date = current). As our ...
0
votes
0answers
9 views

Pentaho getfilename error

I am try to send 20 mail with attachments using pentahi DI, Jobs are like below Get File Names--->Add constants--->Mail But some mails are not send with below error, Thay say files are not ...
4
votes
1answer
119 views

Time dimension or timestamp in fact table?

Which would you use, and why? A separate time dimension or putting a timestamp in a fact table? Or perhaps both? I am building a data warehouse, and need to represent the time of day that events ...
2
votes
2answers
140 views

When to choose an attribute vs. a new dimension?

I am fully aware of what is a fact, attribute and dimension. However, I'm quite confused to which traits I should choose for dimensions vs attributes of that dimension. For instance, I'm building a ...
2
votes
1answer
259 views

How do you calculate how much hardware resources you need for a database?

We are in the process of scaling our database servers. I was wondering how should we calculate how much hardware resources we need for our databases? Here is a little bit information about our ...
8
votes
2answers
1k views

Data Warehouse design for reporting against data for many time zones

We are trying to optimize a data warehouse design that will support reporting against data for many time zones. For example, we might have a report for a month's worth of activity (millions of rows) ...
7
votes
2answers
5k views

Compare two similar Postgres databases for differences

I occasionally download publicly available data sets in the form of Postgres dBs. These datasets are updated/modified/expanded over time by the repository host. Is there a Postgres command or ...
3
votes
1answer
42 views

Scripting Microsoft Data Warehouse Wizard

I am trying to automate the MDW setup process in my environment. Currently I have everything complete except for enabling the data collectors (usually uses a wizard as seen ). Has anyone seen or ...
2
votes
0answers
217 views

Migrate Management Data Warehouse to a New Instance

Is there a defined procedure to follow for moving a SQL Server 2012 management data warehouse to a new server\instance? We intend to setup an MDW on a test sql server instance first, but will then ...
1
vote
1answer
50 views

What's the term that describes our data processes [closed]

We provide an online subscription product that allows our users to research and analyze financial data of their interested industries. As such, we have various tools for loading data from various ...
0
votes
0answers
37 views

SSAS bridge table with Slowly changing dimensions

I am designing a datamart using the basic principles of Kimball for use in SSAS. I have a many many to many attribute relationships (multi-valued dimension objects), however I have a few questions: ...
3
votes
1answer
98 views

Stored procedure deployment - push or pull into database? [closed]

Using stored procedures as part of an ETL process to populate tables in another database, which method would be considered a best practice (if any) in terms of deployment, maintenance, visibility and ...
0
votes
0answers
38 views

What options exist for replicating SQL Server over the public internet

Specifically, I'd like to set up a reporting copy of a SQL Server 2012 transactional database. This primary runs in a managed services provider, and I want a copy in an AWS EC2 SQL Server instance. ...
0
votes
0answers
56 views

Proxy for MDW data collection on remote servers - SQL Server 2012

I am trying to setup data collection (for disk usage) from remote servers and upload it to my MDW database. I understand I will need a proxy set up for the agent to do this but which subsystem to I ...
0
votes
0answers
30 views

What is the recommended RAID configuration for data Warehouse? [duplicate]

I am trying to understand if there is RAID best for data warehouse.( for version SQL Server 2012 or 2014). so What is the best RAID for data Warehouse performance wise? What is the recommended RAID ...
0
votes
0answers
23 views

Data Warehouse for reporting - 6 Dimension tables,or 1 table and 6 views?

Problem: I have been asked to add a Dimension table to our data warehouse and create the ETL tasks to populate it. Lets call the table "DimInternalFault". The source table for DimInternalFault is ...
0
votes
0answers
12 views

Datavaulting polymorphic association (conditional) columns

I'm trying to implement data vault into our business but I'm stuck with the data vault model. We are having some source tables (3NF) which contain some polymorphic association (conditional) columns: ...