SQL Cursors

I see SQL cursor as a loop on the table. It is highly inefficient, and can result in infinite loops if not used properly. I highly recommend trying other alternatives (like Common table Expressions – CTE’s) if it is possible. Anyway, here is the syntax of a SQL cursor SELECT * INTO tmpTbl FROM (SELECT 1 AS SaleID) A[…]

Communicating Forecast results with the Management

Benefits of forecasting that can be communicated with the management are numerous: Areas: – Budgeting, planning, production, inventory control, marketing, advertising, sales projections, investment, etc. Benefits: – Improve profits – Eliminate Waste: Inventory shortages, missed due dates, lost sales, lost customers, expensive expediting, missed strategic opportunities. Forecasts to Management: Pay attention to: – Why is[…]

Time Series Decomposition

Time Series Decomposition: Y=TCSe. Fluctuation in trend & factors. Calculation Steps: 1. SI – Seasonal Index: Calculate SI, a.k.a S*e: 1.1. CMA (TC1): Centered Moving Average: TC1=Centered(2 shilfted years) all year moving Avg=avg of 2 MAs of 2 shifted years. 1.2. Single USI (Seasonal-Error): USI(t)=Y(t)/TC1. 1.3. USI=Average single USI’s of periods of same season. 1.4.[…]

Durbin Watson test

Durbin Watson is a test of autocorrelation: Where r is the auto-correlation coefficient (between Yt and Yt-1). And Since -1 £ r £ 1 • Conditions which Must be Fulfilled for DW to be a Valid Test • 1. Constant term in regression • 2. Regressors are non-stochastic • 3. No lags of dependent variable[…]

ETL with T-SQL Merge Statement

I sometimes use ETL through pure SQL (no SSIS). I use the following. We have two parts: 1. First connect to the OLTP/ODS server from the DW/ETL server through OPENROWSET (I use this instead of OPENQUERY, since OPENQUERY requires Linked Servers). To enable OPENROWSET just enable Ad Hoc Distributed Queries, as follows: EXEC sp_configure ‘show advanced options’, 1; GO reconfigure; GO EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1[…]

Qualitative Forecasting

Forecasting: The process of projecting values. "what do you think?" Methods: Surveys. Group meetings Delphi method. Risks/ Disadvantages: – Non-agreement. – Overdominance of one personality opinion. – The group-collective (sheep-following) possibility. – Under-forecsating.

SAS Exponential Smoothing

SAS Exponential Smoothing: Data Configuration – Simple & Double Exponential Smoothing. SAS Exponential Smoothing: Data Configuration – Seasonal/Tripple Exponential Smoothing Select the data set, the Dependent Variable, the Time ID (which should be unique), and the season length (how many seasons, in our case it is quarters, so 4): SAS does not provide the configuration[…]