star-schema

Star-Schema Design

Is a Star-Schema design essential to a data warehouse? Or can you do data warehousing with another design pattern? ...

Name Value pairs and fact tables

I'm working on a star schema for analysis of posted form data. The site that the form data will be posted to is actually external to the site hosting the form, so only the data in the form will be available. I'm going to give the option to include some extra useful information with hidden fields, original referrer, session id etc. I'll ...

Temporary Table Usage in SQL Server

This is a bit of an open question but I would really like to hear people opinions. I rarely make use of explicitly declared temporary tables (either table variables or regular #tmp tables) as I believe not doing so leads to more concise, readable and debuggable T-SQL. I also think that SQL can do a better job than I of making use of tem...

Complex processing in Stored procedures Vs .net application

We are building a new application in .net 3.5 with SQL server database. The database is fairly large having around 60 tables with loads on data. The .net application have functionality to bring data into this database from data entry and from third party systems. After all the data is available in database the system have to do lots of ...

Is there any benefit to including relationships in your star schema table design?

I'm designing the Fact and Dimension tables for a data warehouse currently using SQL Server, SSIS, and SSAS. Will I get any real benefit from programming the relationships between the dimensions and the fact tables into SQL? Or am I better off just defining the relationships manually when it comes time to create the cubes? It seems to b...

Key DataType for a high volume SQL Server 2008?

I in the process of designing a database for high volume data and I was wondering what datatype to use for the primary keys? There will be table partitioning and the database will ultimatley be clustered and will be hot failover to alternative datacentres. EDIT Tables - think chat system for multiple time periods and multiple things...

How to design a star schema

I am confusing where should I start to design a star schema. for example I have tables in database as follows: Branch(branchNo, bStreetAddress, bCity) LoanManager(empNo, empName, phone, branchNo) Customer(custNo, custName, profession, streetAddress, city, state) Account(accNo, accType, balance, accDate, custNo) LoanContract(contractNo,...

Star schema in SQLAlchemy

I have a star-schema architectured database that I want to represent in SQLAlchemy. Now I have the problem on how this can be done in the best possible way. Right now I have a lot of properties with custom join conditions, because the data is stored in different tables. It would be nice if it would be possible to re-use the dimensions fo...

Reporting tool for OLAP, *not* OLTP!

I'm looking for a control that I can put on top of an already existing OLAP star schema to allow the user to define their own "queries" and generate reports. Right now I have some predefined reports built on top of the cubes, but I'd like to allow the user to define their own criteria based on the cubes that I've created. I've found lo...

Star-schema naming conventions

Is it common practice in a star schema to prefix tables as a dimension or fact table? Is is also common practice to have column names prefixed with the table name? In my normal OLTP databases, I don't do this, but I'm seeing examples of this type of naming in star schemas. Does it make sense to have a different set of naming standards ...

Is Pentaho ETL and Data Analyzer good choice?

I was looking for ETL tool and on google found lot about Pentaho Kettle. I also need a Data Analyzer to run on Star Schema so that business user can play around and generate any kind of report or matrix. Again PentaHo Analyzer is looking good. Other part of the application will be developed in java and the application should be datab...

Star-schema: Separate dimensions for clients and non-clients or shared dimension for attendants?

I'm new to modeling star schemas, fresh from reading the Data Warehouse Toolkit. I have a business process that has clients and non-clients calling into conference calls with some of our employees. My fact table, call it "Audience", will contain a measure of how long an attending person was connected to the call, and the cost of this ...

Insert into a star-schema

I've read a lot about star-schema's, about fact/deminsion tables, select statements to quickly report data, however the matter of data entry into a star-schema seems aloof to me. How does one "theoretically" enter data into a star-schema db? while maintaining the fact table. Is a series of INSERT INTO statement within giant stored proc ...

select all values from a dimension for which there are facts in all other dimensions

I've tried to simplify for the purposes of asking this question. Hopefully, this will be comprehensible. Basically, I have a fact table with a time dimension, another dimension, and a hierarchical dimension. For the purposes of the question, let's assume the hierarchical dimension is zip code and state. The other dimension is just des...

Star schema [fact 1:n dimension]...how?

I am a newcomer to data warehouses and have what I hope is an easy question about building a star schema: If I have a fact table where a fact record naturally has a one-to-many relationship with a single dimension, how can a star schema be modeled to support this? For example: Fact Table: Point of Sale entry (the measurement is Dollar...

How do I avoid complex joins in star schema?

My fact table holds a user score in a course he took. Some of the details of the course, which I have to show on the report, comes from more then one table (in the actual OLTP db). Do I create a none normalized version of that course entry in a dimension table? Or do I just join the fact table directly to the course table join to the oth...

HBase and Star Schema

Hi All I am new to Hbase and exploring the ways how to manage dimensional modeling using Hbase for data warehousing. The dimensions can be Date, Time, Categories, Products. Can anybody give example of implementation of Star schema in HBase? Regards Shuja ...

star schema design - one column dimensions

Hi guys, I`m new to data warehousing, but I think my question can be relatively easy answered. I built a star schema, with a dimension table 'product'. This table has a column 'PropertyName' and a column 'PropertyValue'. The dimension therefore looks a little like this: surrogate_key | natural_key (productID) | PropertyName | Property...

Data Warehouse: Modelling Workload Allocations

Hi, We have a system that tracks the allocation of a unit of work, from receiving that unit of work till completing that unit of work. A unit of work has a number of attributes, it's source, it's type etc. These I'm fairly ok with modelling, likely to be either dimensions of the fact or just attributes depending on the nature of them a...

General understanding of star schema design

So, I think I understood what to put in dimensions, what in the fact table, and how to achieve this. Now I got the problem, that I have this dimension 'product' and a dimension 'productProperties'. I had to split this, cause otherwise my natural key in 'product' wouldn`t have been unique anymore. I asked this in this question. So my '...