Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jchurch
Starting Member
2 Posts |
 Posted - 2008-04-17 : 10:54:18
|
| I am designing a BI solution for a large manufacturer. They process big ticket items. They track their inventory by each piece. I need to know where each item is located, where it came from, when it arrived, has it been processed, ect. Along with that I have to be able to run reports by day/week/month/year that shows current inventory levels at any point in time.What I arrived at for table design is each item will get a row which will include a column for date and another for Add_Remove. When the item is added to inventory it gets a date and a +1 for Add_Remove. When it is removed it gets a new row with a date and a -1 in the Add_Remove column. I should be able to add the Add_Remove column for the selected rows (by location, type, etc) from the beginning of time to any point in time and get a current total. If I'm reporting by month for the year I should be able to add from time(0) to lastitem in January to get January levels, then the same for each successive month.The problem I can't figure out the MDX code to do that. My basic data set is as follows:SELECT NON EMPTY { [Measures].[Total], [Measures].[Add Remove], [Measures].[Inventory Weight] } ON COLUMNS, NON EMPTY { ([Ship From].[Ship From Region].[Ship From Region].ALLMEMBERS * [Ship From].[Ship From].[Ship From].ALLMEMBERS * [Date Received].[Date].[Date].ALLMEMBERS * [Date Received].[Year].[Year].ALLMEMBERS * [Date Received].[Month].[Month].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@Region, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@FromDateReceivedMonth, CONSTRAINED) : STRTOMEMBER(@ToDateReceivedMonth, CONSTRAINED) ) ON COLUMNS FROM( SELECT ( STRTOSET(@InventoryType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@Division, CONSTRAINED) ) ON COLUMNS FROM [Heidtman DW])))) WHERE ( IIF( STRTOSET(@Division, CONSTRAINED).Count = 1, STRTOSET(@Division, CONSTRAINED), [Organization].[Division].currentmember ), IIF( STRTOSET(@InventoryType, CONSTRAINED).Count = 1, STRTOSET(@InventoryType, CONSTRAINED), [Inventory Type].[Inv Type].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGSAny ideas? I've tried adding ordered sets to the query with no success. I've tried calculate measures in the cube with less success. My time dimension includes every day from 1/1/1980 to 12/31/2050. The one calculated memeber i tried - AGGREGATE ([Date Shipped].[Date].[Date].Item(0):Tail(existing [Date Shipped].[Date].[Date]).Item(0)) took 20 minutes to run in VS Browser and produced an error for every date in the time dimension...... |
|
|
asqldeveloper
Starting Member
17 Posts |
Posted - 2008-04-17 : 23:20:59
|
| I am not much of MDX person, but if you have Pro clarity, I heard that you can design your calculated measures there and it will automatically generate MDX for you. Hope this helps. |
 |
|
|
jchurch
Starting Member
2 Posts |
Posted - 2008-04-18 : 08:24:51
|
| I have ProClarity but I don't use it very much and I wouldn't know how to design the time(0)-current query there either. |
|
|
|
|
|
|
|
|
Inventory table design