How to filter cube data based on a status field in DB? 1

[MissouriTiger]

Hi,

Please help if you can. I am a total beginner at this. I am building my first cube in SQL Server Analysis Services, and am having some serious difficulties, to say the least. I have two questions:

1. In order to create calculated members, I have had to add dimensions to the cube which are otherwise useless. Since we are planning to deliver cubes directly to our customers, it's pretty sloppy having a whole pile of dimensions, half of which are of no value. They just clutter up the interface. Is there a cleaner way of going about this?

The fact table in my cube comes from the "lineitem" table in our DB. The measures are:

lineitem.order_qty
lineitem.item_cost
lineitem.extended_price

We only want to populate the fact table with records Where lineitem.status NOT = X. Reason is that X is a canceled or invalid order. Can someone please tell me how to accomplish this?

Any suggestions will be greatly appreciated.

-Greg

________________________________________________
Constructed from 100% recycled electrons.

 

[justins57]

Greg,

Create a view in your Warehouse DB that filters the fact data to exclude cancelled or invalid line items. Use the view as the data source for your cube.

hth,

Justin

 

[MissouriTiger]

Hi Justin,

Actually, I just finished doing that, and it worked like a charm (thanks for your help, though). However, I still have one problem:

I haven't figured out a way to create a measure for 'Number of Orders'. Let me explain...

In our DB we have an ORDERS table, and a LINEITEM TABLE. One order can have many lineitems.

In our OLAP cube we want to have a measure for 'Number of Orders'. I've tried for 2 days to do this with a Calculated Member, but it never works right.

I might be able to build a column into the view that stores a 1 only for the first lineitem per order number and otherwise stores a 0, but I have noidea how to do that in a Create View SQL Query.

Any suggestions would be gratefully appreciated.

-Greg

________________________________________________
Constructed from 100% recycled electrons.

 

[blom0344]

Suggestion:

First create a view that holds the number of lineitems for each order:

 SELECT ORDER,COUNT(LINEITEMS) FROM LINEITEM GROUP BY ORDER

Now join this view with the respective tables and park this in a second view to base your cube upon...

Ties Blom
Information analyst