How to filter if data was not there

Question

How to filter if data was not there

dwilliscp

SSCrazy Eights

Points: 9270
More actions
October 31, 2012 at 1:40 pm

#264104

I have a query (shown below) that I have been asked to filter so that only stock activity that has record's 6 months or more back. I am trying to see if the cube keeps records for all parts in the master or if it only loads those with activity... but at anyrate, what is the best way to filter out those records for Plant + Part that only show up in the last 6 months? (My guess is a CTE that groups by Plant, Part) but I was wondering if there was a faster way. The below query takes about 30 min to run now.
; with base as
(
Select a.Dim_ID
, Day_DT_CD
, MRP_Area_CD
, Material_CD
, Material_Desc
, Material_Type
, Profit_Center
, SBU
, Stocking_Status
, Standard_Cost
, Total_Replenishment_Lead_Time
, Safety_Stock
, Total_Usable_Inventory
, min(Total_Usable_Inventory) Over(Partition by MRP_Area_CD, Material_CD) as Min_Stock_Lvl
FROM Inv_Cube_Fact A
JOIN Inv_Cube_Dimension B
ON A.Dim_ID = B.Dim_ID
where safety_stock > 0 and Stocking_Status like 'MTS'
)
Select a.Dim_ID
, Day_DT_CD
, MRP_Area_CD
, Material_CD
, Material_Desc
, Material_Type
, Profit_Center
, SBU
, Stocking_Status
, Standard_Cost
, Total_Replenishment_Lead_Time
, Safety_Stock
, Total_Usable_Inventory
, Min_Stock_Lvl
from base
where min_stock_lvl > 0
Thanks
dwcp

Viewing 4 posts - 1 through 3 (of 3 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Is this SSAS or a regular set of tables? It looks like T-SQL, not MDX, but the object names and explanation gave me a doubt about that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 2

There is a third party that handles these databases.. my history with cubes is in PICK, not SQL Server. Can you tell from the create statement if it realy is a cube or not?

CREATE TABLE [dbo].[Inv_Cube_Fact](

[Dim_ID] [int] NOT NULL,

[Day_DT_CD] [char](10) NOT NULL,

[Current_Inventory] [real] NULL,

[QualityInsp_Inventory] [real] NULL,

[Restricted_Inventory] [real] NULL,

[Blocked_Inventory] [real] NULL,

[InTransit_Inventory] [real] NULL,

[VCons_Current] [real] NULL,

[VCons_QualityInsp] [real] NULL,

[VCons_Restricted] [real] NULL,

[VCons_Blocked] [real] NULL,

[CCons_Current] [real] NULL,

[CCons_QualityInsp] [real] NULL,

[CCons_Restricted] [real] NULL,

[PctQuantity] [real] NULL,

[Storage_Cnt] [int] NULL,

[Shipments_Avg_1] [real] NULL,

[Shipments_Avg_3] [real] NULL,

[Consumption_Avg_1] [real] NULL,

[Consumption_Avg_3] [real] NULL,

[STO_Avg_1] [real] NULL,

[STO_Avg_3] [real] NULL,

[IntMvmt_Avg_1] [real] NULL,

[IntMvmt_Avg_3] [real] NULL,

[Ship_Fcst_Avg_1] [real] NULL,

[Ship_Fcst_Avg_3] [real] NULL,

[Consumption_Fcst_Avg_1] [real] NULL,

[Consumption_Fcst_Avg_3] [real] NULL,

[STO_Fcst_Avg_1] [real] NULL,

[STO_Fcst_Avg_3] [real] NULL,

[IntMvmt_Fcst_Avg_1] [real] NULL,

[IntMvmt_Fcst_Avg_3] [real] NULL,

[Standard_Cost] [real] NULL,

[Current_Inventory_Cost] [real] NULL,

[Planned_Delivery_Time] [real] NULL,

[Total_Replenishment_Lead_Time] [real] NULL,

[GR_Processing_Time] [real] NULL,

[Planning_Time_Fence] [real] NULL,

[Min_Lot_Size] [real] NULL,

[Max_Lot_Size] [real] NULL,

[Fixed_Lot_Size] [real] NULL,

[Round_Value] [real] NULL,

[Safety_Time] [real] NULL,

[Min_Safety_Stock] [real] NULL,

[Safety_Stock] [real] NULL,

[Max_Stock_Level] [real] NULL,

[Max_Target_Days] [real] NULL,

[Max_Target_Qty] [real] NULL,

[Total_Usable_Inventory] [real] NULL,

[Total_Usable_Inventory_Cost] [real] NULL,

[Total_Daily_Usage] [real] NULL,

[Total_Inventory_Cost] [real] NULL,

[Excess_Kgs] [real] NULL,

[Excess_$] [real] NULL,

[ISA_Safety_Stock] [real] NULL,

[NearTerm_Open_Orders] [real] NULL,

[NearTerm_STO] [real] NULL,

[Material_ID] [int] NULL,

[MRP_Area_ID] [int] NULL,

[Storage_Location_Group_ID] [int] NULL,

[Material_MRPArea_ID] [int] NULL,

[Region] [varchar](100) NULL,

CONSTRAINT [pk_Dim_Day_DT_pid] PRIMARY KEY CLUSTERED

(

[Dim_ID] ASC,

[Day_DT_CD] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

MMartin1 One Orange Chip Points: 27764 More actions · Answer 3

Since this creates a table you are looking at a relational database that happens holds dimensional data. These serve as the basis for the cube (itself a database-non relational) but is not the cube itself. Normally to speed up queries you need to understand what indexes exist and if statistics are being updated. In your case it might depend on the frequency of how the data is populated. Normally these have a one day latency so you can build a few indexes on the table or rebuild existing indexes to help with performance.

----------------------------------------------------