March 19, 2009 at 5:27 pm
Hi all,
I'm fairly new to BI, I've done some cubes in SQL 2000 and 2005 but I never dealt with calculated members more complicated than basic mathematical operators. I could use help with this.
This cube needs to show inventory transactions by fiscal period. I started building a data warehouse with a few dimensions, in this cube I'll be using my dimWarehouse, dimStyle and dimCalendar dimensions.
The cube will contain three measures, OpeningBalance, Quantity and ClosingBalance. The Quantity comes from the relational database inventory transactions table which I summarized by warehouse and period, it represents the sum of all the transactions during the period.
At this point I think I should probably mention that the company I work for uses the 4-4-5 method of accounting periods starting January 1st so I can never work with normal time dimensions based on calendar dates which is why my FiscalPeriod field in the dimCalendar dimension is char(6) (200901, 200902 etc)
I wrote a stored procedure to create my fact table and my dimensions, here are the structures.
FactInvTransactions
WarehouseKey int
StyleKey int
CalendarKey int
Quantity int
dimWarehouse
WarehouseKey int
WarehouseCode char(2)
WarehouseName char(40)
DimStyle
StyleKey int
StyleCode char(10)
StyleName char(20)
DimCalendar
CalendarKey
FiscalPeriod char(6)
I created my cube and I can now see my total by warehouse/style/period.
Now comes the Opening/Closing balance part. I need to add calculated measures for those two. Obviously the opening is the closing of the previous period and the closing is the Opening + Quantity for the period.
How can I do this, can it be done with the OpeningPeriod and ClosingPeriod functions ?
Thanks !
March 23, 2009 at 6:49 am
It looks like I'm on my own on this one. I'll post the code if I can figure it out.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply