Is it always necessary to create Dim tables?
I have a fact table that will have a column which will hold one of two values (occupied/vacant). Is it necessary to create a Dim table for this? ...
data-warehouse
Database choice for large data volume?
I'm about to start a new project which should have a rather large database. The number of tables will not be large (<15), majority of data (99%) will be contained in one big table, which is almost insert/read only (no updates). The estimated amount of data in that one table is going to grow at 500.000 records a day, and we should keep...
Free database for small datawarehouse
Which free database would you use for a relatively small datawarehouse system? Are there any 'special' databases e.g. multidimensional databases freely available? Which of the free relational databases is best suited for the job? By datawarehouse system I mean a system that will receive some inserts, few updates, next to no deletes an...
Error while refreshing a MS datawarehousing cube
We have a cube that we are populating the data from the source tables. To get the data to reflect we are calling the "Analysis services processing task" component. While the cube is being refreshed we are getting the following error. Description: OLE DB error: OLE DB or ODBC error: Communication link failure; 08S01. End Error Error: 2...
Efficiently storing 7.300.000.000 rows
How would you tackle the following storage and retrieval problem? Roughly 2.000.000 rows will be added each day (365 days/year) with the following information per row: id (unique row identifier) entity_id (takes on values between 1 and 2.000.000 inclusive) date_id (incremented with one each day - will take on values between 1 and 3.65...
Jitterbit as an ETL tool
Hi, anybody have used Jitterbit as an ETL tool? any reviews/tutorials? ...
Datawarehouse Question
I have a structure like this in a DB | RoleID | RoleTypeID | PersonID | OrganizationID | FromDate | ToDate | Each role is only valid for dates between FromDate and ToDate. Each person has a name, gender and birth date. I want to put this date into a data warehouse to use with SSAS. There I want to be able to build cubes with the nu...
PDF Data source in Informatica
How does Informatica handle unstructured data sources like PDF. If a tabular report is stored as a PDF, can we read it out from PDF as a tabular data (like a data table in .net)? ...
How do you remove fields with Activewarehouse-etl?
I'm parsing a csv file and trying to send it to a table with fewer fields than the original csv file. How do I remove those extraneous commas that are left over if I set the extra fields to nothing? Here's the original csv format: columns => id,first_name,last_name,phone,fax sample row => 1,ben,tomas,5555555,6666666 Here's the final ...
Converting Views into tables using SSIS
Is it a good idea to convert complex views in "db1" into tables in "db2" using SSIS. the purpose of converting views to tables is to make the reports faster. Is there any disadvantages or risks? ...
Odd Update Behavior
In a part of my data warehousing stored procedures, i have a procedure that compares old project data to new project data (old data is in a table, new in temp table) and updates the old data. The weird part is that if the old data is null, then the update statement doesn't work. If i add a is null statement the update works fine. My q...
Structure within staging area of data warehouse
Hi. We are working on a datawarehouse for a bank and have pretty much followed the standard Kimball model of staging tables, a star schema and an ETL to pull the data through the process. Kimball talks about using the staging area for import, cleaning, processing and everything until you are ready to put the data into the star schema. ...
Transforming OLTP Relational Database to Data Warehousing Model
What are the common design approaches taken in loading data from a typical Entity-Relationship OLTP database model into a Kimball star schema Data Warehouse/Marts model? Do you use a staging area to perform the transformation and then load into the warehouse? How do you link data between the warehouse and the OLTP database? Where/How d...
Where can I find a real dataset anywhere online that I could try doing a data warehouse cube with?
I am studying data warehouses and I have to do one final project for my studies. I am thinking about doing a cube for a data warehouse. Where can I find a real dataset anywhere online that I could try doing a cube with? ...
Attribute in multiple hierarchies in Analysis Services 2008
Hi! I have designed a relatively simple data warehouse that uses the star schema. I have a fact table with just a primary key along with CompanyID and Amount (the actual measurement) columns. Of course I also have a dimension table to represent the companies which the fact table references. Now I'm required to create a single level hie...
Datawarehouse - How to Link dimensions
Hi Guys, Just got into datawarehousing and need your help to clarify a confusion. Lets say I have Employee dimension and Department Dimension. If I have a report that requires me to list fields from dimEmployee (Name, Salary, Position) and fields from department (DeptNo, Desc, Manager), how do I do that. Do I create a fact table(factless...
Handling nulls in Datawarehouse
Hi all, I'd like to ask your input on what the best practice is for handling null or empty data values when it pertains to data warehousing and SSIS/SSAS. I have several fact and dimension tables that contain null values in different rows. Specifics: 1) What is the best way to handle null date/times values? Should I make a 'default' ...
Calling a Scalar-valued Function in SSIS
Is there any way to execute a scalar-valued function from within a Derived Column transformation in SSIS? -Scenario- I have a function in my source DB that converts weights based on a UOM value in the record's UOM column. I want to utilize this function in the ETL process to ensure that my weight measures are always pounds. Can I call ...
Programmatically add and connect Data Flow components in SSIS in C#
Hi. Is there a high quality sample code for Data Flow Components in SSIS? I specifically need the following: OLE DB Source Derive Column Transformation Flat File Destination. Lets say that the table schema comprises of two columns, ID of varchar(2) and Description of varchar(20). The output is a flat file ID of 1 char (just get th...
Data warehouse reporting questions
Hi all. I've just begun diving into data warehousing and I have one question that I just can't seem to figure out. I have a business which has ten stores, each with a certain employees. In my data warehouse I have a dimension representing the store. The employee dimension is a SCD, with a column for start/end, and the store at which t...