Problem:
A Business User wants to update/insert/delete the values in a table that sits in the metadata of the Datawarehouse on a regular basis. Based on these values numerous calculations will be done in the warehouse which generates a data driven Report on a weekly basis.
Initially I got the following Solutions
1. Create a frontend app for the user to populate the values (the most common Global solution)
2. Ask the user to populate the values in an excel file with the columns in specific format so that I can use my SSIS to do the lookup with the original table and update/delete/insert based on the values.( But there are lot of things to consider when using excel like Problems with incorrect format of excel files and maintaining them.)
So when I was reviewing the Merge feature in TSQL 2008, an idea stuck in mind
The solution I ended up doing was
Creating a Reporting services Report that takes the values as parameter and use the MERGE statement to insert/update/delete the values in the backend table
Here is the Example of what I mean:
create table CricketRuns
(
PlayerID int identity(1,1),
PlayerName nvarchar(max),
Runs int,
Average float
)
go
insert into CricketRuns
select 'Sachin Tendulkar',17000,45.23
union
select 'Ricky Ponting',12000,41.08
union
select 'Kevin Peiterson',4000,46.00
union
select 'Chris Gayle',7000,34.87
union
select 'Jacques Kallis',11000,42.09
union
select 'Ross Taylor',2500,41.00
go
So the table looks like
I created this procedure using the Merge Statement that will insert / delete / update based on the players name
create procedure uspInsertRuns
(
@PlayerName nvarchar(max)
,@Runs int
,@Average float
,@status nvarchar(100)
)
as
Merge into CricketRuns as Target
using
(select @PlayerName,@Runs,@Average,@status) as source(Playername,runs,Average,Status)
on
(Target.Playername = source.PlayerName)
when matched and Source.Status = 'D' then
Delete
when matched then
update set runs = source.runs , Average = source.Average
when not matched then
insert (PlayerName,runs,Average) values (source.PlayerName,source.runs,source.Average);
select PlayerName,Runs,Average from CricketRuns order by PlayerID
So you can insert,delete or update as below
exec uspInsertRuns ‘Andrew Strauss’,2000,35,” –> Insert
exec uspInsertRuns ‘Virendar Shewag’,5000,37,” –> Insert
exec uspInsertRuns ‘Ross Taylor’,0,0,’D’ –> Delete
exec uspInsertRuns ‘Sachin Tendulkar’,20000,45,” –> Update
Just add the proc in a reporting services and expose it to the business user and thats it..A frontend using Reporting Services
So without any excel management or creating a .net frontend reporting services has done the job.
