Dimensional Model

I am in the process of designing a Booking, Billing and Backlog
datamart. My backend is going to be SQL Server 2K. Data source is
Baan ERP system. The transaction level I have to populate the
datamart is at sales order line level.
My problem is that the data model I have come up with is more
relational than dimensional. Here is what I have got so far.
Orders
Order Detail
Customer
Products
Countries
Regions
Employees
Other way of sourcing the data would be to write one big query from
ERP and then do the dimensional model but that would slow down the
data retrieval. I would be grateful if any you can share your
experience with this sort of a datamart.
Thanks
Ather
Well, you have to take the approach of dimensional modelling to make it
work. http://www.ralphkimball.com has some good articles to get you started,
also I would recommend a book "Data Wareshousing TookKit" by the same author
has some good examples that you can related to.
Pramod
"Ather Mian" <athermian@.yahoo.com> wrote in message
news:6ed9e09e.0403310515.4c0e0a79@.posting.google.c om...
> I am in the process of designing a Booking, Billing and Backlog
> datamart. My backend is going to be SQL Server 2K. Data source is
> Baan ERP system. The transaction level I have to populate the
> datamart is at sales order line level.
> My problem is that the data model I have come up with is more
> relational than dimensional. Here is what I have got so far.
> Orders
> Order Detail
> Customer
> Products
> Countries
> Regions
> Employees
> Other way of sourcing the data would be to write one big query from
> ERP and then do the dimensional model but that would slow down the
> data retrieval. I would be grateful if any you can share your
> experience with this sort of a datamart.
> Thanks
> Ather
sql

Dimensional Model

I am in the process of designing a Booking, Billing and Backlog
datamart. My backend is going to be SQL Server 2K. Data source is
Baan ERP system. The transaction level I have to populate the
datamart is at sales order line level.
My problem is that the data model I have come up with is more
relational than dimensional. Here is what I have got so far.
Orders
Order Detail
Customer
Products
Countries
Regions
Employees
Other way of sourcing the data would be to write one big query from
ERP and then do the dimensional model but that would slow down the
data retrieval. I would be grateful if any you can share your
experience with this sort of a datamart.
Thanks
AtherWell, you have to take the approach of dimensional modelling to make it
work. http://www.ralphkimball.com has some good articles to get you started,
also I would recommend a book "Data Wareshousing TookKit" by the same author
has some good examples that you can related to.
Pramod
"Ather Mian" <athermian@.yahoo.com> wrote in message
news:6ed9e09e.0403310515.4c0e0a79@.posting.google.com...
> I am in the process of designing a Booking, Billing and Backlog
> datamart. My backend is going to be SQL Server 2K. Data source is
> Baan ERP system. The transaction level I have to populate the
> datamart is at sales order line level.
> My problem is that the data model I have come up with is more
> relational than dimensional. Here is what I have got so far.
> Orders
> Order Detail
> Customer
> Products
> Countries
> Regions
> Employees
> Other way of sourcing the data would be to write one big query from
> ERP and then do the dimensional model but that would slow down the
> data retrieval. I would be grateful if any you can share your
> experience with this sort of a datamart.
> Thanks
> Ather

Dimension Displayed

Product Dimension Table

ProdID

Prod A

Prod B

Prod C

Fact Table

Key ProdID Measure

1 ProdA 100

2 ProdB 200

When I process the cube the and drag the measure and Product dim to view the result, the result show as below only ProdA and ProdB only.

The result sure correct and no error.

Just dont know why the ProdC not show as expected at SSAS2000.

I try the setting for the "Show Empty Cells" , It just for showing purpose only, not for permenant at that cube.

Anyone know why ProdC not show at the result as permenantly?

Do I need to do any setting to able ProdC show at the result.

Thanks .

This is not controlled by the cube, it is upto the client tool whether or not it includes empty cells or not. SSAS2000 was the same. By default a lot of browsers do not show empty cells and you have to explicitly turn this option on. There is nothing you can set at the database/cube level to control this.

Dimension creation manually issue

I have created a new dimension manually from a project.But when I rebuild,deploy and process and view browser for that cube, I couldnt see the new dimension from the list of dimensions. Under what condition should the new dimension 'relates' to the cube?

Thanks.

Regards

Alu

You have to add it to your cube and then define right relationship between dimension and measure group|||

You do this in the dimension usage tab in the cube editor in BIDS.

Right click on the dimensions and choose add cube dimension

HTH

Thomas Ivarsson

|||

Thanks.

Regards

Alu

Different behavior in 2000 and 2005 for same query

Hi there,

We are in the process of upgrading from SQL Server 2000 to 2005. During testing we came across the following situation.

To reproduce the issue you can do the following

Create this structure in a 2000 and 2005 server instances

CREATE TABLE test

(a int,

b varchar(30))

INSERT INTO test

(a, b)

VALUES (1, '2')

INSERT INTO test

(a, b)

VALUES (3, '3a')

INSERT INTO test

(a, b)

VALUES (4, '4')

Then run the following statement in both servers:

UPDATE test

SET a = ltrim(rtrim(b))

WHERE b NOT LIKE '%a%'

AND ltrim(rtrim(b)) <> a

In 2000 this last statement will execute with no problem and it will update one row, whereas in 2005 the following error message is given:

Msg 245, Level 16, State 1, Line 20

Conversion failed when converting the varchar value '3a' to data type int.

By looking at the execution plan it seems that 2005 first tries to evaluate ltrim(rtrim(b)) <> a and then excludes those rows containing a whereas 2000 first excludes those rows containing a and then evaluates the different than condition.

I know fixing this instance itself is easy but I’m more concerned about having to rewrite many more stored procedures where we find this same scenario; is there any setting that can be changed to avoid this?

Any guidance is much appreciated.

Thanks!

You could change the database Compatibility level to 80 (SQL 2000) and determine if that helps.

There is a significant difference between the versions query processing.

And SQL 2005 allows less implicit conversions (aka, careless programming that often contravenes security).

I suspect you will need to make the changes to avoid implicit conversions as soon as possible.