Inventory Cube Problem

Question

Post reply

Inventory Cube Problem

erdem1973

SSCommitted

Points: 1843
More actions
September 27, 2010 at 5:26 pm

#142094

Hello Experts,
In SSAS , I have designed a fact table named InventoryLookup. I have inserted all item transactions that holds inventory changes to this fact table. So, I can get the correct results from the beginning date and the specified date range in SQL queries.
I run these two SQL queries:
select SUM(Quantity) from InventoryFact
where dateID<'16/08/2010' and warehouseID='1000'
-- returns 20885
select SUM(Quantity) from InventoryFact
where dateID>'08/08/2010' and
dateID<'16/08/2010' and warehouseID='1000'
-- returns -1796
The first query returns the correct inventory value. But SSAS shows the second query's result.
I have attached a screenshot and I am sure that you will understand my problem by looking this screenshot
Any idea?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

naveendas Mr or Mrs. 500 Points: 590 More actions · Answer 1

What is the hierarchy structure defined on DimDate?

What does '33' refer to in the Hierarchy slice?

erdem1973 SSCommitted Points: 1843 More actions · Answer 2

Year --> Month --> WeekOfYear -->DateID

33 is the 33th week of the year.

naveendas Mr or Mrs. 500 Points: 590 More actions · Answer 3

Maybe I am missing something, but you are asking for the week 33 SUM from the cube, and that result is the same value as reported by the SQL query (#2).

So, what is the issue?

erdem1973 SSCommitted Points: 1843 More actions · Answer 4

The problem is SSAS returns the Equal operator for the datefilter. But it should be <= operator for this date filter.

I can get the correct result by using <= operator.

Is it possible to use <= operator for date filters?

naveendas Mr or Mrs. 500 Points: 590 More actions · Answer 5

in SSAS, you are looking at members of a dimension and if you want to use <= operator as in SQL, you have to add up all values from the beginning to the member being pointed to.

See this article for details:

http://www.ssas-info.com/analysis-services-articles/62-design/367-inventory-management-calculations-in-sql-server-analysis-services-2005-by-richard-tkachuk

erdem1973 SSCommitted Points: 1843 More actions · Answer 6

Thank you very much for your comments.

But, I think in this case I cannot filter my fact table. So, how can show a specific week's inventory values ( I mean the last date for specified week of year) ?

It will always show the latest inventory levels. Right?

naveendas Mr or Mrs. 500 Points: 590 More actions · Answer 7

You don't need to filter your fact table - create a calculated measure to add up values from the beginning of the year to the current week and that should give you the inventory level for the week.

erdem1973 SSCommitted Points: 1843 More actions · Answer 8

erdem1973

SSCommitted

Points: 1843

September 28, 2010 at 12:36 pm

#1228605

could you please give me a sample?

naveendas Mr or Mrs. 500 Points: 590 More actions · Answer 9

You have to SUM from the first member to the currentmember at the date level in Time dimension.

Try this:

WITH MEMBER Measures.[Stock Level] AS

SUM([Date].[Hierarchy].[Date].members(0):ClosingPeriod([Date].[Hierarchy].[Date]),

Measures.[Quantity] )

SELECT Measures.[Stock Level] on 0,

[Dim Warehouses MAX].[Store ID] on 1

FROM [Inventory]

WHERE [Date].[Hierarchy].[33]

Do note that this is a simplistic approach and can be refined for performance enhancements.

CB4 Newbie Points: 7 More actions · Answer 10

I have implemented Richard Tkachuk's inventory solution and it works quite well. Though, I an having an issue that I'm hoping someone can provide an answer. How do I get it too work with a slowly changing dimensions? Richard's solution sum's inventory positions from the first point in time it was entered into the data warehouse. In my situation, the product enters into the warehouse priced at full retail. Invemtory transactions then go in as deltas; +1, -1, +2, etc. As these delta records are entered, I'm fine if the product remains at full retail. When certain products change to a promotional price (promo) , I have inventory records that are deltas associated with a promo indicator 'y' as of the date the product is changed to promo. The previous delta records (and original inventory positon) were a promo indicator of 'n'.

Now of I want to see inventory at any point in time based upon full retail vs. promo, I filter based upon promo indicator. In this case, all my full priced items are fine, but my promo numbers are incorrect. This is because my beginning inventory, which is needed as my starting position, was originally entered as full retail (promo indicator 'n') therefore it does no SUM with my promo items. Essentially in Richard's solution, I end up fltering out my starting inventory positions when I sum up my promo items. Anyone have any ideas how to handle this?

Thanks in advance....