FAQ Database Discussion Community


Difference between sql query aggregation and aggegration and querying an OLAP cube

analytics,data-warehouse,olap,olap-cube,star-schema
I have a query with respect to the advantages of building a OLAP cube vs aggregating data in database table for querying ,data of say 6 months and then archiving the sql table later for analytics purpose. Which one is better, table or OLAP cube? and why since I can...

scaling a database on cloud and on local servers [closed]

mongodb,data-warehouse,sharding
I am considering using mongo db (it could be postgresql or any other ) as a data warehouse, my concern is that up to twenty or more users could be running queries at a time and this could have serious implications in terms of performance. My question is what is...

concurrent statistics gathering on Oracle 11g partiitioned table

oracle,oracle11g,etl,data-warehouse,table-statistics
I am developing a DWH on Oracle 11g. We have some big tables (250+ million rows), partitioned by value. Each partition is a assigned to a different feeding source, and every partition is independent from others, so they can be loaded and processed concurrently. Data distribution is very uneven, we...

Implementing SCD Type 2 using Pentaho Kettle (Pentaho Data Integeration 5.2)

pentaho,data-warehouse,dimensional-modeling,pentaho-cde
I am having a table, plan, with columns p_id,p_name,start_date,end_date,last_updated Problem Statement: when a customer changes from plan A to plan B, its end_date corresponding to plan A gets updated in the table and at the same time a new record for plan B inserted into the table. I am creating...

Handling change of grain for a snapshot fact table in a star-schema

data-warehouse,star-schema
The question How do you handle a change in grain (from weekly measurement to daily measurement) for a snapshot fact table. Background info For a star-schema design I want to incorporate the results of a survey as a fact (e.g. in week 2 of 2015 80% of the respondents have...

Aggregate Transformation vs Sort (remove Duplicate) in SSIS

ssis,data-warehouse,business-intelligence,bids
I'm trying to populate dimension tables on a regular basis and I've thought of two ways of getting distinct values for my dimension: Using an Aggregate transformation and then using the "Group by" operation. Using a Sort transformation while removing duplicates. I'm not sure which one is better (more efficient),...

SQL 2008 Change tracking and detecting Updated data

sql,versioning,data-warehouse
I plan to implement this in an SSIS project. Since I don't have enterprise version of SQL server 2008, I have to make use of other methods. Another way is to use triggers, but I am trying to avoid to many triggers. With change tracking I'm having difficulties detecting the...

Should the “count” measure be stored in the fact table?

data-warehouse,dimensions,fact-table,datamart
I have a fact table that includes "wait times in hours" for certain services. I have a lot of dimensions that could describe the wait-times based on different slices; however, I am also interested in knowing how many people (counts) came for services through the filters of the same dimensions....

Debugging SQL statement of SSIS SQL task to insert range of date

sql-server,ssis,data-warehouse,business-intelligence
I'm trying to insert range of date into a date dimensional table using SQL task, and passing through parameters of BeginDate / EndDate to it. However, if I try to execute the package, there are no data inserted in the dimensional table, but the package executes fine. How do I...

Natural Key and Fact tables

data-warehouse,business-intelligence,dimensional-modeling,fact-table,natural-key
I'm new on dimensional modelling I believe that you guys can help me in the following doubts. In the production system I have a transaction table, sales table for example.The unique identifier is a primary key called SaleId. Example: My doubt is when modelling the fact table should the SaleID...

Loading fact table with SCD type 2 dimension

pentaho,data-warehouse
I have got a dimension tables with 1 million records which is SCD type 2.I am using pentaho Dimension lookup step for populating this dimension table. I am getting a version number,start date and end date. Now I want to populate the fact table based on the scd type2. What...

Compare Data between 2 DW Tables

sql,database,oracle,compare,data-warehouse
I'm a little confused here. I'm testing some data quality issues in a DW, I need to know if the LOAN_SID in one table matches the other table. I was using this query but I'm not sure if I'm correct, if it matches there is an issue if it doesn't...

why operational database are not fulfilling business challenges as data warehouse?

database,data-warehouse
i have a question why operational database are not fulfilling business challenges as data warehouse? in operational database i can create reports in details about any product or any thing and i can issue statistical reports with charts and diagrams, so why the operational database can not use as data...

Errors in the OLAP storage engine: The attribute key cannot be found when processing

ssas,foreign-key-relationship,data-warehouse,olap-cube,dimensional-modeling
I know this is mainly a design problem. I 've read that there is a workaround for this issue by customising errors at processing time but I am not glad to have to ignore errors, also the cube process is scheduled so ignore errors is not a choice at least...

Select organizations that their income represent around 60% of the total income SQL Server2008

sql-server,sum,data-warehouse,percentage
In advance, I apologize for imperfect English In this data warehouse, we have organization which composed of multiple Organizations, I have [FactFinance] table which has information about the income of each organization. I have the following query in data warehouse which select the (Organization Name) from the [organization dimension table]...

Star Schema Design for User Utilization Reports

data-warehouse,star-schema,microstrategy,fact-table,snowflake-schema
Scenario: There are 3 kinds of utilization metrics that i have derive for the users. In my application, users activity are tracked using his login history, number of customer calls made by the user, number of status changes performed by user. All these information are maintained in 3 different tables...

Anchor Modeling - are data types part of the Model?

database-design,data-warehouse,temporal-database,6nf,anchor-modeling
A question about data types in the Anchor Model database design. The question assume separation of anchor model implementation from the anchor model itself. In the Anchor Model xml we have following kind information related to data types: dataRange="varchar(42)" identity="int" timeRange="datetime" They are stored in Anchor Model entities (anchor/attribute) xml...

Omniture Data Warehouse Segments Issue

bigdata,data-warehouse,adobe-analytics
Currently, I'm trying to create a segment filter called "Only Search Page" which filters out one particular server from a list of several thousand. Currently, I'm a little stuck and it might be easier to explain with screenshots. In the Segment Manager I set up a segment to check for...

OCDM combined with ODI

oracle,etl,data-warehouse,oracle-data-integrator
ODI = ELT tool OCDM = Data warehouse. Is my understanding of the above correct ? More information/explanation is welcome. Now my question is : Is it possible to load into OCDM's pre-existing tables via ODI, when the source of ODI are in flatfiles/XML format ? If possible, how ?...

INSERT INTO statement in MySQL

mysql,sql,database,data-warehouse
I'm trying to work with YEAR function on one column in the DB and then add the results to a different table in the DWH. What am I doing wrong? INSERT INTO example_dwh1.dim_time (date_year) SELECT YEAR(time_taken) FROM exampledb.photos; When removing the INSERT INTO line, I get the results I want,...