Can I and Should I use OLAP? - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Analysis Services (2000)

Can I and Should I use OLAP?

Author

Topic

RobWafle
Starting Member

38 Posts

Posted - 2002-02-08 : 22:33:18

First off, thank you to anyone who helps me with this!!

Can I and Should I use OLAP to calculate the results of the first select query below (not the select into query)?

vwPositions is a view that will have aprox 4.8 million rows of data. less than 1/16 of my data changes as new data comes in. The other rows are entirely static and do not change, and will always calculate to the same result.

I feel like the approach below will require my server to constantly recalculate my summary values. I don't want to put anything in the temp database because I don't want to cause unneeded disk I/O and locking in the temporary database. I've been reading a lot about derived queries instead of using the tempdb, but I am summarizing a lot of data here.. and why should I calculate something twice, or three hundred times if I can just calculate it once and store it in an OLAP cube, or maybe an auxillary table??

CREATE PROCEDURE spPivot13F_ByCIK_Summary (@CIK varchar(50), @currentQuarter int, @currentYear int, @lastQuarter int, @lastYear int) AS
begin

declare @currentQ varchar (50)
declare @lastQ varchar (50)

if 1 <= @currentQuarter and @currentQuarter <= 4 and 1 <= @lastQuarter and @lastQuarter <= 4 and 1980 <= @lastYear and @lastYear <= @currentYear
begin
set @currentQ = 'Q' + cast (@currentQuarter as char (1)) + '/' + cast (@currentYear as char (4))
set @lastQ = 'Q' + cast (@lastQuarter as char (1)) + '/' + cast (@lastYear as char (4))

select b.* , b.currentShares - b.lastShares as deltaShares, b.currentValue- b.lastValue as deltaValue,
case b.lastShares when 0 then null else (((b.currentShares - b.lastShares) / cast (b.lastShares as float)) * 100) end as pctChgShares,
case b.lastValue when 0 then null else (((b.currentValue - b.lastValue) / cast (b.lastValue as float)) * 100) end as pctChgValue
into #tmpSummary
from
( select positions_issuer, positions_class,positions_CUSIP,
isNull (sum( CASE dissem_period_fixed WHEN @currentQ THEN positions_shares END), sum( CASE dissem_period_fixed WHEN @lastQ THEN positions_shares END)) as fixedShares,
sum( CASE dissem_period_fixed WHEN @currentQ THEN positions_shares END) as currentShares,
sum( CASE dissem_period_fixed WHEN @lastQ THEN positions_shares END) as lastShares,
isNull (sum( CASE dissem_period_fixed WHEN @currentQ THEN positions_valuex1000 END), sum( CASE dissem_period_fixed WHEN @lastQ THEN positions_valuex1000 END)) as fixedValue,
sum( CASE dissem_period_fixed WHEN @currentQ THEN positions_valuex1000 END) as currentValue,
sum( CASE dissem_period_fixed WHEN @lastQ THEN positions_valuex1000 END) as lastValue,
5 as P1,
6 as p2
from edgar..vwPositions
where dissem_CIK = @CIK and positions_issuer is not null
group by positions_issuer, positions_class,positions_CUSIP) as b
select
(select count (*) from #tmpSummary) as total_shares_held_holders,
(select sum (currentshares) from #tmpSummary) as total_shares_held_shares,

(select count (*) from #tmpSummary where currentshares > 0 ) as new_positions_holders,
(select sum (currentshares) from #tmpSummary where currentshares > 0 ) as new_positions_shares,

(select count (*) from #tmpSummary where currentshares > lastshares ) as increased_positions_holders,
(select sum (currentshares) from #tmpSummary where currentshares > lastshares) as increased_positions_shares,

(select count (*) from #tmpSummary where currentshares < lastshares ) as decreased_positions_holders,
(select sum (currentshares) from #tmpSummary where currentshares > lastshares) as decreased_positions_shares,

(select count (*) from #tmpSummary where currentshares = 0 and lastshares > 0 ) as soldout_positions_holders,
(select sum (currentshares) from #tmpSummary where currentshares = 0 and lastshares > 0 ) as soldout_positions_shares

select * from #tmpSummary

end
end
GO

RobWafle
Starting Member

38 Posts

Posted - 2002-02-08 : 23:12:00

Please note I accidentally pasted and older version of my code here. In some cases, I may need to use the sum() .. but I don't think I have to do that in this particular case.

CREATE PROCEDURE spPivot13F_ByCIK (@CIK int, @currentQuarter int, @currentYear int, @lastQuarter int, @lastYear int, @minvalue bigint = 0, @maxvalue bigint = 0, @state varchar(2) = '') AS
begin

declare @currentQ varchar (50)
declare @lastQ varchar (50)

if 1 <= @currentQuarter and @currentQuarter <= 4 and 1 <= @lastQuarter and @lastQuarter <= 4 and 1980 <= @lastYear and @lastYear <= @currentYear
begin
set @currentQ = 'Q' + cast (@currentQuarter as char (1)) + '/' + cast (@currentYear as char (4))
set @lastQ = 'Q' + cast (@lastQuarter as char (1)) + '/' + cast (@lastYear as char (4))

select b.* , b.currentShares - b.lastShares as deltaShares, b.currentValue- b.lastValue as deltaValue,
case b.lastShares when 0 then null else (((b.currentShares - b.lastShares) / cast (b.lastShares as float)) * 100) end as pctChgShares,
case b.lastValue when 0 then null else (((b.currentValue - b.lastValue) / cast (b.lastValue as float)) * 100) end as pctChgValue
into #tmpSummary
from
( select positions_issuer, positions_class,positions_CUSIP,
isNull (CASE dissem_period_fixed WHEN @currentQ THEN positions_shares END, CASE dissem_period_fixed WHEN @lastQ THEN positions_shares END) as fixedShares,
CASE dissem_period_fixed WHEN @currentQ THEN positions_shares END as currentShares,
CASE dissem_period_fixed WHEN @lastQ THEN positions_shares END as lastShares,
isNull ( CASE dissem_period_fixed WHEN @currentQ THEN positions_valuex1000 END, CASE dissem_period_fixed WHEN @lastQ THEN positions_valuex1000 END) as fixedValue,
CASE dissem_period_fixed WHEN @currentQ THEN positions_valuex1000 END as currentValue,
CASE dissem_period_fixed WHEN @lastQ THEN positions_valuex1000 END as lastValue,
5 as P1,
6 as p2
from edgar..vwPositions
where dissem_CIK = @CIK and positions_issuer is not null
) as b

-- THIS SELECT IS THE ONE I AM THINKING ABOUT USING OLAP FOR
select
(select count (*) from #tmpSummary) as total_shares_held_holders,
(select sum (currentshares) from #tmpSummary) as total_shares_held_shares,

(select count (*) from #tmpSummary where currentshares > 0 ) as new_positions_holders,
(select sum (currentshares) from #tmpSummary where currentshares > 0 ) as new_positions_shares,

(select count (*) from #tmpSummary where currentshares > lastshares ) as increased_positions_holders,
(select sum (currentshares) from #tmpSummary where currentshares > lastshares) as increased_positions_shares,

(select count (*) from #tmpSummary where currentshares < lastshares ) as decreased_positions_holders,
(select sum (currentshares) from #tmpSummary where currentshares > lastshares) as decreased_positions_shares,

(select count (*) from #tmpSummary where currentshares = 0 and lastshares > 0 ) as soldout_positions_holders,
(select sum (currentshares) from #tmpSummary where currentshares = 0 and lastshares > 0 ) as soldout_positions_shares

end
end
GO

RobWafle
Starting Member

38 Posts

Posted - 2002-02-09 : 18:11:04

Alright..

Here's a new attempt at this thing..

this is created in a web page, dynamically... Lets say if you use the "Execution Plan" option in SQL query analyzer.. that query three seems to take ALOT of extra work!! (a bunch o table scans on my temp table..) I did it in a web page instead of the stored procedure to avoid having to create permutations of stored procedures..

Does anyone know of a good SQL code formatter? I can't paste this into VC++ because its tooo long.. go figure.

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = '#tmp' AND type = 'u') DROP TABLE #tmp; select b.*, b .currentShares - b.lastShares as deltaShares, b.currentValue- b.lastValue as deltaValue, case b.lastShares when null then 0 else (((b.currentShares - b.lastShares) / cast (b.lastShares as float)) * 100) end as pctChgShares, case b.lastValue when 0 then null else (((b.currentValue - b.lastValue) / cast (b.lastValue as float)) * 100) end as pctChgValue into #tmp from ( select Dissem_Conformed_Name,dissem_CIK,address_state,address_city, isNull (CASE dissem_period_fixed WHEN "Q3" THEN positions_shares END, CASE dissem_period_fixed WHEN "Q2/2001" THEN positions_shares END) as fixedShares, CASE dissem_period_fixed WHEN "Q3" THEN positions_shares END as currentShares, CASE dissem_period_fixed WHEN "Q2/2001" THEN positions_shares END as lastShares, isNull (CASE dissem_period_fixed WHEN "Q3" THEN positions_valuex1000 END, CASE dissem_period_fixed WHEN "Q2/2001" THEN positions_valuex1000 END) as fixedValue, CASE dissem_period_fixed WHEN "Q3" THEN positions_valuex1000 END as currentValue, CASE dissem_period_fixed WHEN "Q2/2001" THEN positions_valuex1000 END as lastValue, 5 as P1, 6 as P2 from edgar..vwPositions where dissem_CIK = "766524" and positions_manager is not null and dissem_Year = 2001 or dissem_Year = 2001 and dissem_Quarter = 3 or dissem_Quarter = 2) as b;select (select count (*) from #tmp) as total_shares_held_holders, (select sum (currentshares) from #tmp) as total_shares_held_shares, (select count (*) from #tmp where currentshares > 0 ) as new_positions_holders, (select sum (currentshares) from #tmp where currentshares > 0 ) as new_positions_shares, (select count (*) from #tmp where currentshares > lastshares ) as increased_positions_holders, (select sum (currentshares) from #tmp where currentshares > lastshares) as increased_positions_shares, (select count (*) from #tmp where currentshares < lastshares ) as decreased_positions_holders, (select sum (currentshares) from #tmp where currentshares > lastshares) as decreased_positions_shares, (select count (*) from #tmp where currentshares = 0 and lastshares > 0 ) as soldout_positions_holders, (select sum (currentshares) from #tmp where currentshares = 0 and lastshares > 0 ) as soldout_positions_shares

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources