sql dwh problem: 2012

Thursday, March 29, 2012

Dynamic Server Path

I have images shown on a report that are stored on a server.
Problem is when developing the server path is different from my live server.
So whenever we deploy the report on the server i have to make a change
manually to the path of the images...i need to make this dynamic.
How can i do this...can i do this in config file. If so how can i set value
in config file(which one specific) and how can i access it in the report.
Any help appreciated...thanks
Sunny.Hi,
I would suggest you include all your images in your "Report project" so that
whenever you deploy all these images will get deployed.
Amarnath
"Sunny" wrote:
> I have images shown on a report that are stored on a server.
> Problem is when developing the server path is different from my live server.
> So whenever we deploy the report on the server i have to make a change
> manually to the path of the images...i need to make this dynamic.
> How can i do this...can i do this in config file. If so how can i set value
> in config file(which one specific) and how can i access it in the report.
> Any help appreciated...thanks
> Sunny.
>|||You can use an expression in the url and the variable
"Globals!ReportServerUrl" to make it dynamically.
Maran
"Sunny" wrote:
> I have images shown on a report that are stored on a server.
> Problem is when developing the server path is different from my live server.
> So whenever we deploy the report on the server i have to make a change
> manually to the path of the images...i need to make this dynamic.
> How can i do this...can i do this in config file. If so how can i set value
> in config file(which one specific) and how can i access it in the report.
> Any help appreciated...thanks
> Sunny.
>

Dynamic selection of flat file

I am writing a package where the user uploads a flat file to a web folder. I need to automate this package to run everytime it sees a new file.

How can I implement this?

Can I make a call to a package or a sql server job to run from .net 2.0?

Do I need to use a service broker to look for a new file and run the package or a stored proc....I am looking for an async process where user doesnt have to wait for the package to run as it involves data validation of flat file and its huge...

Please help!!

How about a scheduled package that runs every minute, and if it finds a file it runs the load task, otherwise it just ends.

You could do something event driven with the WMI event task, but I dislike that since it does not tell you what file has been found, or try the File Watcher Task (http://www.sqlis.com/default.aspx?23)

|||

Appreciate your response...

I was wondering about creating a config file in the database and update the Connection string value everytime a new file is uploaded with the new file name

And then call the package to run by adding dts assembly to visual studio and doing package.load....

Any suggestion or comments on this as I really dont want to run this every min....

Dynamic selecting a table

I want to write a dynamic Query to select a Column called sFirstName from
different table based on Name column which exists in all the table
The table name which I get from My process is with spaces So I need to
replace " "(Space) with ("_") underscore. I know this can be done using
execute sp_executesql @.sql... but Please advice if this can be done with a
Sql Statement
Declare @.sTemp_Name varchar(100)
Set @.sTemp_Name = replace('Complaint table' ,' ','_')
Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
The above Code gives me an Error as " Incorrect syntax near '+'. "
Please advice
Thanks
kritiHi,
The only solution for this is using Dynamic SQL using either sp_executesql
or EXEC
Thanks
Hari
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:41FF6DE6-DE4F-4C32-B959-3D268626E46F@.microsoft.com...
>I want to write a dynamic Query to select a Column called sFirstName from
> different table based on Name column which exists in all the table
> The table name which I get from My process is with spaces So I need to
> replace " "(Space) with ("_") underscore. I know this can be done using
> execute sp_executesql @.sql... but Please advice if this can be done with a
> Sql Statement
> Declare @.sTemp_Name varchar(100)
> Set @.sTemp_Name = replace('Complaint table' ,' ','_')
> Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
> The above Code gives me an Error as " Incorrect syntax near '+'. "
> Please advice
> Thanks
> kritisql

Dynamic selecting a table

I want to write a dynamic Query to select a Column called sFirstName from
different table based on Name column which exists in all the table
The table name which I get from My process is with spaces So I need to
replace " "(Space) with ("_") underscore. I know this can be done using
execute sp_executesql @.sql... but Please advice if this can be done with a
Sql Statement
Declare @.sTemp_Name varchar(100)
Set @.sTemp_Name = replace('Complaint table' ,' ','_')
Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
The above Code gives me an Error as " Incorrect syntax near '+'. "
Please advice
Thanks
kriti
Hi,
The only solution for this is using Dynamic SQL using either sp_executesql
or EXEC
Thanks
Hari
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:41FF6DE6-DE4F-4C32-B959-3D268626E46F@.microsoft.com...
>I want to write a dynamic Query to select a Column called sFirstName from
> different table based on Name column which exists in all the table
> The table name which I get from My process is with spaces So I need to
> replace " "(Space) with ("_") underscore. I know this can be done using
> execute sp_executesql @.sql... but Please advice if this can be done with a
> Sql Statement
> Declare @.sTemp_Name varchar(100)
> Set @.sTemp_Name = replace('Complaint table' ,' ','_')
> Select sFirstName from dbo. + @.sTemp_Name + where Name = 'Samay'
> The above Code gives me an Error as " Incorrect syntax near '+'. "
> Please advice
> Thanks
> kriti

Dynamic selecting a table

Dynamic Select/Update Statement Possible?

Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter?

For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following...

ALTER PROCEDURE u_sp_x
@.semester int
AS
Select Semester@.semester
From ThisTable

Just curious.

Thanks,
Steve HanzelmanThis might work..

alter procedure u_sp_x
@.semester int
as
select * from semester
where @.semester = 'semester 1'|||You CAN do just about anything. Dynamic SQL statements would be required here, or a UNION query or complicated WHERE clause. But whether you SHOULD do it is another think entirely. Dynamic SQL statements are a pain in the butt, and should be avoided, and thus are definitely more for masochistic DBAs than lazy DBAs.

Your problem, as is often the case, is that you are having to code around a deficiency in the design of your tables. You should have a table that stores each Semester's value as a separate record. Then your application will also be easily adaptable to situations where three or five semesters are allowed, or half-semesters, or quarters, or whatever.|||Blindman,
I agree re: the design of the tables/database. Unfortunately, it is one that was inherited and belongs to an application that was purchased by my employer. Therein lies the rub...can't modify so I'm try to save a few steps.

Oh well, I'm guessing four procedures.

Thanks for the help.|||OK...

First, I have seen WAY too many slick apps that pretend to be cute..they are MAJOR pain to debug.

The smaller you make your sprocs, the better. And the less dynamic sql the better.

So with that said...the keys to the kingdom

USE Northwind
GO

CREATE PROC mySproc99 @.COLUMN_NAME sysname, @.TABLE_NAME sysname
AS
DECLARE @.sql varchar(8000)

SELECT @.sql = 'SELECT ' + @.COLUMN_NAME + ' FROM ' + @.TABLE_NAME

EXEC(@.sql)
GO

EXEC mySproc99 'ShipName','Orders'
GO

DROP PROC mySproc99
GO|||Brett proposing dynamic SQL?! :eek:

What's the weather forecast in Hell, today? ;)|||I was thinking this, but forgot...

Becareful out there...

And

Abandon all hope for ye who enter here...

Only dynamic sql I use is for admin purposes...never in an application

(Some would say some of my admin procedures amount to a mini mainframe application...but that a story for another margarita...COME ON 5:00!)

Dynamic Select Statement

please advice me with the below SQL Script
say,I have a cursor to Insert a records in the table if the below condition matches
samay
I have different tables
1. EMPLOYEE
ID Salary TableName
================================== EMP1 25000 Nametable1
EMP3 15000 Nametable2
EMP19 15000 Nametable3
EMP5 10000 Nametable4
2.
Nametable1
ID Name
============================ EMP1 Samay
EMP2 Rahul
3.
Nametable2
ID Name
============================ EMP19 Samay
EMP29 Rahul
Now,
1. I want to get the TableName(@.TableName) from EMPLOYEE where salary = 15000
2. Select a Name from (@.TableName) table
where EMPLOYEE.ID = @.TableName.ID
Declare @.Name
Declare @.TableName
Declare @.ID
Set @.ID = (Select ID from EMPLOYEE where salary = 15000)
set @.TableName = (Select TableName from EMPLOYEE where salary = 15000 and ID = @.ID)
Set @.Name = (Select Name from @.TableName where ID = @.ID)
Now if I execute my script which has a cursor it give me an Error
Must declare the variable '@.TableName '. for Last line script
PLease advice me with this1) you need to declare your vbls
2) Dynamic SQL means that you build a string (E.g., @.SQL below) which when all put together is a valid SQL Statement. Then you execute it via exec (@.SQL).
Declare @.SQL varchar(2000)
Declare @.TableName varchar(100)
Declare @.ID int
Set @.ID = (Select ID from EMPLOYEE where salary = 15000)
set @.TableName = (Select TableName from EMPLOYEE where salary = 15000 and ID = @.ID)
Set @.SQL = 'Select Name from ' + @.TableName + ' where ID =' + Str(@.ID)
exec (@.SQL)
--
Tea C.
"KritiVerma@.hotmail.com" wrote:
> please advice me with the below SQL Script
> say,I have a cursor to Insert a records in the table if the below condition matches
> samay
> I have different tables
> 1. EMPLOYEE
> ID Salary TableName
> ==================================> EMP1 25000 Nametable1
> EMP3 15000 Nametable2
> EMP19 15000 Nametable3
> EMP5 10000 Nametable4
> 2.
> Nametable1
> ID Name
> ============================> EMP1 Samay
> EMP2 Rahul
> 3.
> Nametable2
> ID Name
> ============================> EMP19 Samay
> EMP29 Rahul
> Now,
> 1. I want to get the TableName(@.TableName) from EMPLOYEE where salary = 15000
> 2. Select a Name from (@.TableName) table
> where EMPLOYEE.ID = @.TableName.ID
>
> Declare @.Name
> Declare @.TableName
> Declare @.ID
> Set @.ID = (Select ID from EMPLOYEE where salary = 15000)
> set @.TableName = (Select TableName from EMPLOYEE where salary = 15000 and ID = @.ID)
> Set @.Name = (Select Name from @.TableName where ID = @.ID)
> Now if I execute my script which has a cursor it give me an Error
> Must declare the variable '@.TableName '. for Last line script
> PLease advice me with this
>|||Thanks for your Query
I am having a SQl satement as
If(Object_ID('DBName.Dbo.##TEMPDelete')) is not null
Drop table ##TEMPDelete
SET @.Name = (Select replace(@.Name,' ','_'))
Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+ @.Name + ' where eid = '+ @.eid
Exec (@.SQL)
Set @. Name = (select @.Name from ##TEMPDelete)
Print AddressTemp
this results back as an error
Invalid Column name EMP123 (the Value of @.eid instead of the column eId)
please advice
"Tea C" wrote:
> Sorry - that was not clear from your original inquiry.
> I can think of 3 way to accomplish this:
> 1) Use a temporary tbl
> if Object_ID('tempdb.dbo.##tmp') is not null
> drop table ##tmp
> Set @.SQL = 'Select top 1 Name as test into ##tmp from ' + @.TableName + ' where ID =' + Str(@.ID)
> exec (@.SQL)
> set @.Name = (select test from ##tmp)
> print @.Name
> 2) Combine your SELECT and INSERT into a single "dynamic" SQL, by doing INSERT after your SELECT.
> Set @.SQL = ' declare @.name varchar(100) Select top 1 Name from ' + @.TableName + ' where ID =' + Str(@.ID) ' + ' -- here goes your insert statement or a call to SP which does the insert'
> 3) Or just combine INSERT and SELECT into a single query
> Set @.SQL = 'insert into NameTable(Name) from Select top 1 Name from ' + @.TableName + ' where ID =' + Str(@.ID)
> exec (@.sql)
> Hopefully, one of these 3 ideas meets your needs...
> Tea C.
>
> "KritiVerma@.hotmail.com" wrote:
> > Thanks for ur reply But I want to store the value into a Variable which i am using to insert a record into an another table
> >
> > i.e for
> > Set @.SQL = 'Select Name from ' + @.TableName + ' where ID =' + Str(@.ID)
> > exec (@.SQL)
> >
> > Set @.SQL = 'Select Name from ' + @.TableName + ' where ID =' + Str(@.ID)
> > @.Name = exec (@.SQL) -- or somthing like this
> >
> > Please advice
> >
> > Thanks again
> >
> >
> >
> > "Tea C" wrote:
> >
> > > 1) you need to declare your vbls
> > > 2) Dynamic SQL means that you build a string (E.g., @.SQL below) which when all put together is a valid SQL Statement. Then you execute it via exec (@.SQL).
> > >
> > > Declare @.SQL varchar(2000)
> > > Declare @.TableName varchar(100)
> > > Declare @.ID int
> > >
> > > Set @.ID = (Select ID from EMPLOYEE where salary = 15000)
> > > set @.TableName = (Select TableName from EMPLOYEE where salary = 15000 and ID = @.ID)
> > > Set @.SQL = 'Select Name from ' + @.TableName + ' where ID =' + Str(@.ID)
> > > exec (@.SQL)
> > >
> > > --
> > > Tea C.
> > >
> > >
> > > "KritiVerma@.hotmail.com" wrote:
> > >
> > > > please advice me with the below SQL Script
> > > >
> > > > say,I have a cursor to Insert a records in the table if the below condition matches
> > > > samay
> > > > I have different tables
> > > > 1. EMPLOYEE
> > > > ID Salary TableName
> > > > ==================================> > > > EMP1 25000 Nametable1
> > > > EMP3 15000 Nametable2
> > > > EMP19 15000 Nametable3
> > > > EMP5 10000 Nametable4
> > > >
> > > > 2.
> > > > Nametable1
> > > > ID Name
> > > > ============================> > > > EMP1 Samay
> > > > EMP2 Rahul
> > > > 3.
> > > > Nametable2
> > > > ID Name
> > > > ============================> > > > EMP19 Samay
> > > > EMP29 Rahul
> > > >
> > > > Now,
> > > > 1. I want to get the TableName(@.TableName) from EMPLOYEE where salary = 15000
> > > > 2. Select a Name from (@.TableName) table
> > > > where EMPLOYEE.ID = @.TableName.ID
> > > >
> > > >
> > > > Declare @.Name
> > > > Declare @.TableName
> > > > Declare @.ID
> > > >
> > > > Set @.ID = (Select ID from EMPLOYEE where salary = 15000)
> > > >
> > > > set @.TableName = (Select TableName from EMPLOYEE where salary = 15000 and ID = @.ID)
> > > >
> > > > Set @.Name = (Select Name from @.TableName where ID = @.ID)
> > > >
> > > > Now if I execute my script which has a cursor it give me an Error
> > > >
> > > > Must declare the variable '@.TableName '. for Last line script
> > > >
> > > > PLease advice me with this
> > > >|||If your eID exists as a column in the Address table, then you would change your @.SQL to:
SET @.SQL = 'Select top 1 eID into ##TEMPDelete ......'
After executing the above @.SQL, you will end up with a tmp table ##TempDelete, which will have a single column, named eID. From here you treat your temp table, just like any other table.
e.g.,
Select * from ##tempDelete
then change your last SET statement to:
Set @. Name = (select eID from ##TEMPDelete)
Thanks,
--
Tea C.
"Tea C" wrote:
> Change your last SET statement to:
> Set @. Name = (select AddressTemp from ##TEMPDelete)
> and make sure AddressTemp returns whatever you need to retrun when you insert it into the ##tempDelete. If you need a value from a different field (or fields), you will change your @.SQL to include these additional fields (E.g., select top 1 Address, City, AddressID into ##tempDelete ...etc.). You can then retrieve each of these fields into your vbls after you execute your dynamic SQL (@.SQL).
> declare @.addressID int
> declare @.address varchar(50)
> declare @.city varchar(50)
> Select @.addressID = AddressID, @.Address=Address, @.city=City from ##tempDelete
> -- Just for fun, print
> Print @.AddressID
> -- or you can do (as you are already doing)
> SET @.addressID = (Select AddressID from ##tempDelete)
> SET @.Address = (Select Address from ##TempDelete)
> SET @.City = (Select City from ##tempDelete)
> -- Just for fun print
> print @.address
> Tea C.
>
> "KritiVerma@.hotmail.com" wrote:
> > Thanks for your Query
> >
> > I am having a SQl satement as
> > If(Object_ID('DBName.Dbo.##TEMPDelete')) is not null
> > Drop table ##TEMPDelete
> >
> > SET @.Name = (Select replace(@.Name,' ','_'))
> >
> > Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+ @.Name + ' where eid = '+ @.eid
> > Exec (@.SQL)
> >
> > Set @. Name = (select @.Name from ##TEMPDelete)
> > Print AddressTemp
> >
> >
> > this results back as an error
> > Invalid Column name EMP123 (the Value of @.eid instead of the column eId)
> >
> >
> > please advice
> >
> > "Tea C" wrote:
> >
> > > Sorry - that was not clear from your original inquiry.
> > >
> > > I can think of 3 way to accomplish this:
> > >
> > > 1) Use a temporary tbl
> > >
> > > if Object_ID('tempdb.dbo.##tmp') is not null
> > > drop table ##tmp
> > >
> > > Set @.SQL = 'Select top 1 Name as test into ##tmp from ' + @.TableName + ' where ID =' + Str(@.ID)
> > >
> > > exec (@.SQL)
> > > set @.Name = (select test from ##tmp)
> > > print @.Name
> > >
> > > 2) Combine your SELECT and INSERT into a single "dynamic" SQL, by doing INSERT after your SELECT.
> > >
> > > Set @.SQL = ' declare @.name varchar(100) Select top 1 Name from ' + @.TableName + ' where ID =' + Str(@.ID) ' + ' -- here goes your insert statement or a call to SP which does the insert'
> > >
> > > 3) Or just combine INSERT and SELECT into a single query
> > > Set @.SQL = 'insert into NameTable(Name) from Select top 1 Name from ' + @.TableName + ' where ID =' + Str(@.ID)
> > > exec (@.sql)
> > >
> > > Hopefully, one of these 3 ideas meets your needs...
> > >
> > > Tea C.
> > >
> > >
> > > "KritiVerma@.hotmail.com" wrote:
> > >
> > > > Thanks for ur reply But I want to store the value into a Variable which i am using to insert a record into an another table
> > > >
> > > > i.e for
> > > > Set @.SQL = 'Select Name from ' + @.TableName + ' where ID =' + Str(@.ID)
> > > > exec (@.SQL)
> > > >
> > > > Set @.SQL = 'Select Name from ' + @.TableName + ' where ID =' + Str(@.ID)
> > > > @.Name = exec (@.SQL) -- or somthing like this
> > > >
> > > > Please advice
> > > >
> > > > Thanks again
> > > >
> > > >
> > > >
> > > > "Tea C" wrote:
> > > >
> > > > > 1) you need to declare your vbls
> > > > > 2) Dynamic SQL means that you build a string (E.g., @.SQL below) which when all put together is a valid SQL Statement. Then you execute it via exec (@.SQL).
> > > > >
> > > > > Declare @.SQL varchar(2000)
> > > > > Declare @.TableName varchar(100)
> > > > > Declare @.ID int
> > > > >
> > > > > Set @.ID = (Select ID from EMPLOYEE where salary = 15000)
> > > > > set @.TableName = (Select TableName from EMPLOYEE where salary = 15000 and ID = @.ID)
> > > > > Set @.SQL = 'Select Name from ' + @.TableName + ' where ID =' + Str(@.ID)
> > > > > exec (@.SQL)
> > > > >
> > > > > --
> > > > > Tea C.
> > > > >
> > > > >
> > > > > "KritiVerma@.hotmail.com" wrote:
> > > > >
> > > > > > please advice me with the below SQL Script
> > > > > >
> > > > > > say,I have a cursor to Insert a records in the table if the below condition matches
> > > > > > samay
> > > > > > I have different tables
> > > > > > 1. EMPLOYEE
> > > > > > ID Salary TableName
> > > > > > ==================================> > > > > > EMP1 25000 Nametable1
> > > > > > EMP3 15000 Nametable2
> > > > > > EMP19 15000 Nametable3
> > > > > > EMP5 10000 Nametable4
> > > > > >
> > > > > > 2.
> > > > > > Nametable1
> > > > > > ID Name
> > > > > > ============================> > > > > > EMP1 Samay
> > > > > > EMP2 Rahul
> > > > > > 3.
> > > > > > Nametable2
> > > > > > ID Name
> > > > > > ============================> > > > > > EMP19 Samay
> > > > > > EMP29 Rahul
> > > > > >
> > > > > > Now,
> > > > > > 1. I want to get the TableName(@.TableName) from EMPLOYEE where salary = 15000
> > > > > > 2. Select a Name from (@.TableName) table
> > > > > > where EMPLOYEE.ID = @.TableName.ID
> > > > > >
> > > > > >
> > > > > > Declare @.Name
> > > > > > Declare @.TableName
> > > > > > Declare @.ID
> > > > > >
> > > > > > Set @.ID = (Select ID from EMPLOYEE where salary = 15000)
> > > > > >
> > > > > > set @.TableName = (Select TableName from EMPLOYEE where salary = 15000 and ID = @.ID)
> > > > > >
> > > > > > Set @.Name = (Select Name from @.TableName where ID = @.ID)
> > > > > >
> > > > > > Now if I execute my script which has a cursor it give me an Error
> > > > > >
> > > > > > Must declare the variable '@.TableName '. for Last line script
> > > > > >
> > > > > > PLease advice me with this
> > > > > >

Dynamic Select Statement

Dynamic Select list in Stored Proc

I have the following script where the select parameters is dependent on the "component" parameter list being send via the stored proc. Therefore the number of parameters returned by the proc is dynamic. These fields therefore do not show in the .Net design to be added to the report. Need some advise please.
CREATE PROCEDURE dbo.prcPerfAnalysisComponentTest
( @.Category varchar(50)
, @.Item varchar(50)
, @.Component1 varchar(250)
, @.Component2 varchar(250) = NULL
, @.Component3 varchar(250) = NULL
, @.Component4 varchar(250) = NULL
, @.Component5 varchar(250) = NULL
, @.Component6 varchar(250) = NULL
, @.Component7 varchar(250) = NULL
, @.Component8 varchar(250) = NULL
, @.Component9 varchar(250) = NULL
, @.Component10 varchar(250) = NULL
, @.DOW char(13) = NULL
, @.StartTime char(8) = NULL -- Eg. '00:00:00'
, @.EndTime char(8) = NULL -- Eg. '23:59:59'
, @.GroupInd char(7) = 'DAY' ) -- DAY, WEEK, MONTH, YEAR,
QUARTER
AS
DECLARE @.Command varchar(2000)
SELECT @.Command = 'SELECT ' +
CASE
WHEN @.GroupInd = 'WEEK' THEN 'LTRIM(STR(TimeYear)) + ''-'' + LTRIM(STR(TimeWeek))'
WHEN @.GroupInd = 'MONTH' THEN 'LTRIM(STR(TimeYear)) + ''-'' + LTRIM(STR(TimeMonth))'
WHEN @.GroupInd = 'QUARTER' THEN 'LTRIM(STR(TimeYear)) + ''-'' + LTRIM(STR(TimeQuarter))'
WHEN @.GroupInd = 'YEAR' THEN 'LTRIM(STR(TimeYear))'
ELSE 'LEFT(t.TimePortion,5)' --Default to daily
END
SELECT @.Command = @.Command +
', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component1 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component1 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component1 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component1 + '"'
IF @.Component2 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component2 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component2 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component2 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component2 + '"'
IF @.Component3 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component3 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component3 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component3 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component3 + '"'
IF @.Component4 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component4 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component4 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component4 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component4 + '"'
IF @.Component5 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component5 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component5 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component5 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component5 + '"'
IF @.Component6 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component6 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component6 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component6 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component6 + '"'
IF @.Component7 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component7 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component7 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component7 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component7 + '"'
IF @.Component8 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component8 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component8 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component8 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component8 + '"'
IF @.Component9 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component9 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component9 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component9 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component9 + '"'
IF @.Component10 IS NOT NULL
SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component10 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' + @.Component10 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN sc.ComponentName = ''' + @.Component10 + ''' THEN 1 ELSE 0 END) END AS "' + @.Component10 + '"'
SELECT @.Command = @.Command + ' FROM DataSN3 p ' +
'JOIN vwServerComponent sc ON sc.ServerComponentID = p.ServerComponentID AND sc.MonitorItemID = p.MonitorItemID ' +
'JOIN TimeLineSN3 t ON p.MonitorDate = t.TimeLineID WHERE sc.CategoryDescription = ''' + @.Category + ''' ' +
'AND sc.ItemDescription = ''' + @.Item + ''' '
IF @.DOW IS NOT NULL
SELECT @.Command = @.Command + ' AND t.TimeDOW IN (' + @.DOW + ')'
IF @.StartTime IS NOT NULL
SELECT @.Command = @.Command + ' AND t.TimePortion >= (''' + @.StartTime + ''')'
IF @.EndTime IS NOT NULL
SELECT @.Command = @.Command + ' AND t.TimePortion <= (''' + @.EndTime + ''')'
IF @.Start IS NOT NULL
SELECT @.Command = @.Command + ' AND t.TimeDate >= CONVERT(datetime, ''' + CONVERT(char(19), @.Start, 121) + ''', 121)'
IF @.End IS NOT NULL
SELECT @.Command = @.Command + ' AND t.TimeDate <= CONVERT(datetime, ''' + CONVERT(char(19), @.End, 121) + ''', 121)'
SELECT @.Command = @.Command + ' GROUP BY ' +
CASE
WHEN @.GroupInd = 'WEEK' THEN 'LTRIM(STR(TimeYear)) + ''-'' + LTRIM(STR(TimeWeek)) '
WHEN @.GroupInd = 'MONTH' THEN 'LTRIM(STR(TimeYear)) + ''-'' + LTRIM(STR(TimeMonth)) '
WHEN @.GroupInd = 'QUARTER' THEN 'LTRIM(STR(TimeYear)) + ''-'' + LTRIM(STR(TimeQuarter)) '
WHEN @.GroupInd = 'YEAR' THEN 'LTRIM(STR(TimeYear)) '
ELSE 'LEFT(t.TimePortion,5) ' --Default to daily
END +
' ORDER BY 1'
EXECUTE (@.Command)You don't have to have the fields showing in the Designer to use them. You
can set the source of the textboxes to =Fields!<your field name>.Value. You
can also use the IsMissing property of the Field object to determine if the
stored procedure has returned the field or not. Finally, please not that
multiple select statements are not supported in RS; only the first is used.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Frans-sa" <Frans-sa@.discussions.microsoft.com> wrote in message
news:B7AB19DC-FB72-43C9-9EF6-665BA46F4CA2@.microsoft.com...
> I have the following script where the select parameters is dependent on
the "component" parameter list being send via the stored proc. Therefore
the number of parameters returned by the proc is dynamic. These fields
therefore do not show in the .Net design to be added to the report. Need
some advise please.
>
> CREATE PROCEDURE dbo.prcPerfAnalysisComponentTest
> ( @.Category varchar(50)
> , @.Item varchar(50)
> , @.Component1 varchar(250)
> , @.Component2 varchar(250) = NULL
> , @.Component3 varchar(250) = NULL
> , @.Component4 varchar(250) = NULL
> , @.Component5 varchar(250) = NULL
> , @.Component6 varchar(250) = NULL
> , @.Component7 varchar(250) = NULL
> , @.Component8 varchar(250) = NULL
> , @.Component9 varchar(250) = NULL
> , @.Component10 varchar(250) = NULL
> , @.DOW char(13) = NULL
> , @.StartTime char(8) = NULL -- Eg. '00:00:00'
> , @.EndTime char(8) = NULL -- Eg. '23:59:59'
> , @.GroupInd char(7) = 'DAY' ) -- DAY, WEEK, MONTH, YEAR,
> QUARTER
> AS
> DECLARE @.Command varchar(2000)
> SELECT @.Command = 'SELECT ' +
> CASE
> WHEN @.GroupInd = 'WEEK' THEN 'LTRIM(STR(TimeYear)) + ''-'' +
LTRIM(STR(TimeWeek))'
> WHEN @.GroupInd = 'MONTH' THEN 'LTRIM(STR(TimeYear)) + ''-'' +
LTRIM(STR(TimeMonth))'
> WHEN @.GroupInd = 'QUARTER' THEN 'LTRIM(STR(TimeYear)) + ''-'' +
LTRIM(STR(TimeQuarter))'
> WHEN @.GroupInd = 'YEAR' THEN 'LTRIM(STR(TimeYear))'
> ELSE 'LEFT(t.TimePortion,5)' --Default to daily
> END
> SELECT @.Command = @.Command +
> ', CASE SUM(CASE WHEN sc.ComponentName = ''' + @.Component1 + ''' THEN 1
ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN sc.ComponentName = ''' +
@.Component1 + ''' THEN p.MonitorValue ELSE 0 END) / SUM(CASE WHEN
sc.ComponentName = ''' + @.Component1 + ''' THEN 1 ELSE 0 END) END AS "' +
@.Component1 + '"'
> IF @.Component2 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component2 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component2 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component2 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component2 + '"'
> IF @.Component3 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component3 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component3 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component3 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component3 + '"'
> IF @.Component4 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component4 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component4 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component4 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component4 + '"'
> IF @.Component5 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component5 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component5 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component5 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component5 + '"'
> IF @.Component6 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component6 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component6 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component6 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component6 + '"'
> IF @.Component7 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component7 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component7 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component7 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component7 + '"'
> IF @.Component8 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component8 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component8 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component8 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component8 + '"'
> IF @.Component9 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component9 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component9 + ''' THEN p.MonitorValue ELSE 0 END) /
SUM(CASE WHEN sc.ComponentName = ''' + @.Component9 + ''' THEN 1 ELSE 0 END)
END AS "' + @.Component9 + '"'
> IF @.Component10 IS NOT NULL
> SELECT @.Command = @.Command + ', CASE SUM(CASE WHEN sc.ComponentName = '''
+ @.Component10 + ''' THEN 1 ELSE 0 END) WHEN 0 THEN 0 ELSE SUM(CASE WHEN
sc.ComponentName = ''' + @.Component10 + ''' THEN p.MonitorValue ELSE 0 END)
/ SUM(CASE WHEN sc.ComponentName = ''' + @.Component10 + ''' THEN 1 ELSE 0
END) END AS "' + @.Component10 + '"'
> SELECT @.Command = @.Command + ' FROM DataSN3 p ' +
> 'JOIN vwServerComponent sc ON sc.ServerComponentID = p.ServerComponentID
AND sc.MonitorItemID = p.MonitorItemID ' +
> 'JOIN TimeLineSN3 t ON p.MonitorDate = t.TimeLineID WHERE
sc.CategoryDescription = ''' + @.Category + ''' ' +
> 'AND sc.ItemDescription = ''' + @.Item + ''' '
> IF @.DOW IS NOT NULL
> SELECT @.Command = @.Command + ' AND t.TimeDOW IN (' + @.DOW + ')'
> IF @.StartTime IS NOT NULL
> SELECT @.Command = @.Command + ' AND t.TimePortion >= (''' + @.StartTime +
''')'
> IF @.EndTime IS NOT NULL
> SELECT @.Command = @.Command + ' AND t.TimePortion <= (''' + @.EndTime +
''')'
> IF @.Start IS NOT NULL
> SELECT @.Command = @.Command + ' AND t.TimeDate >= CONVERT(datetime, ''' +
CONVERT(char(19), @.Start, 121) + ''', 121)'
> IF @.End IS NOT NULL
> SELECT @.Command = @.Command + ' AND t.TimeDate <= CONVERT(datetime, ''' +
CONVERT(char(19), @.End, 121) + ''', 121)'
> SELECT @.Command = @.Command + ' GROUP BY ' +
> CASE
> WHEN @.GroupInd = 'WEEK' THEN 'LTRIM(STR(TimeYear)) + ''-'' +
LTRIM(STR(TimeWeek)) '
> WHEN @.GroupInd = 'MONTH' THEN 'LTRIM(STR(TimeYear)) + ''-'' +
LTRIM(STR(TimeMonth)) '
> WHEN @.GroupInd = 'QUARTER' THEN 'LTRIM(STR(TimeYear)) + ''-'' +
LTRIM(STR(TimeQuarter)) '
> WHEN @.GroupInd = 'YEAR' THEN 'LTRIM(STR(TimeYear)) '
> ELSE 'LEFT(t.TimePortion,5) ' --Default to daily
> END +
> ' ORDER BY 1'
> EXECUTE (@.Command)
>

Dynamic Select in MSSQL

Hi folks,
I have a problem migrating from Oracle to MSSQL the statement:
stmt2 := 'Select substr('||tabc||',1,50) from '||tabn||'
OPEN cur2 FOR stmt2;

How can I to perform this dynamic select into MSQL ??

Thanks 4 your helpI partially solved with:

Exec ('select substring('+@.chvCol+',1,50) from ' + @.chvTable + .....)

with

DECLARE @.chvTable sysname, @.chvCol sysname

Dynamic SELECT Command in SqlDataSource

I have a GridView (that uses SqlDataSource1) and a Dropdownlist. Depending upon the value selected on the DropDownList I need to select different stored procedures for the gridview. The problem is that I can do it without taking SqlDataSource1 by using DataSet or DataTable. But, I need to Use SQLDataSource1 for easy way of Header SORTING. So, is there any way to change the SQLDatasource1.SELECT Command dynamically. So that, I can use different queries for the Single DataGrid.

I have attached the sample code of the SqlDataSource1 I'm using. I need to change the Command i.e.SelectCommand="usp_reports_shortages" to"usp_reports_shortagesbyID" and"usp_reports_shortagesbyDate"

depending on the value selected in the dropdownlist. So, is there any way to do this??
<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ConnectionStrings:TESTDrivercommunication%>"

SelectCommand="usp_reports_shortages"SelectCommandType="StoredProcedure">

<asp:ControlParameterControlID="lblDriver"Name="date1"PropertyName="Text"Type="DateTime"/>

<asp:ControlParameterControlID="lblTODate"Name="date2"PropertyName="Text"Type="DateTime"/>

<asp:ControlParameterControlID="DDlDriver"Name="driver"PropertyName="SelectedValue"

Type="Int32"/>

<asp:SessionParameterName="week"SessionField="s_week"Type="DateTime"/>

</SelectParameters>

</asp:SqlDataSource>

Numerous ways.

Change the value in the dropdown's selectedindex changed event.

Change it in SqlDatasource's selecting event.

Change the select command to an if. "IF @.DDlDriver=1 THEN EXECusp_reports_shortages @.date1,@.date2,@.weekIF @.DDlDriver=2 THEN EXEC usp_reports_shortagesbyID @.date1,@.date2,@.week IF @.DDlDriver=3 THEN EXECusp_reports_shortagesbyDate @.date1,@.date2,@.week".

Make a stored procedure that takes all 4 paramters and encapsulates the IF's.

sql

Dynamic Security to Cube

I have the requiremrnt that when a User loggs in He would be able to see only his and his Ancestores Details(means he ll be able to see the users deatils those are reporting him)

The MDX Query i wrote in Roles--> Dimension Data-->Advanced-->Allowed Memberset is.

FILTER( [DIMUSERS].[DIMUSERS].Members,

'POLARIS\'+ [DIMUSERS].[DIMUSERS].Currentmember.Name=username)

When After execuing the above MDX Its doing hardcode as the login of the Current User

which is not affecting for other Users.

So that Who ever user login to the system everybody getting all the User deatils

This is Urgent plz help me in this regards

Thank U
Check that your users are not setup in the Administrators group, or are not part of a windows group that is in the admin role. This is definitely true for the server admins and possibly also for database admins, but I have not double checked that.|||

Thanks Daren

what you gav solution I did the same still its comming the same

|||

I'm not entirely sure I understand your issue. Are you possibly saying that the members are restricted at the lower level, but the parent levels still have the total for all members? If this is your problem, go into the advanced tab and turn on the "Visual Totals" option. This will make the value for all the parent members only show a total which is the sum of the values to which the user has access.

If this is not your issue maybe an example of what you are seeing and what you would like to see would help.

|||

I want the cube data to be filtered on the basis of the current domain user. Is there any way to get that filter implemented on analysis cube or through MDX query as said above.....please its urgent...

|||You can either do what the first poster was doing and setup a "users" dimension and setup the allowed set in your role to filter using the username function. or you can setup a series of different roles with different allowed sets and allocate users to the different roles - it really depends on your specific security needs as to which is the better approach.

Dynamic Security to Cube

I have the requiremrnt that when a User loggs in He would be able to see only his and his Ancestores Details(means he ll be able to see the users deatils those are reporting him)

The MDX Query i wrote in Roles--> Dimension Data-->Advanced-->Allowed Memberset is.

Thanks Daren

what you gav solution I did the same still its comming the same

|||

If this is not your issue maybe an example of what you are seeing and what you would like to see would help.

|||

Dynamic Security Stored Procedure Repeatedly Called

I have implemented an SSAS stored procedure for dynamic security and I call this stored procedure to obtain the allowed set filter. To my supprise, the stored procedure is being called repeatedly many times (more than 10) upon establishing the user session. Why is this happening?Do you have more than 10 attributes in this dimension ? (i.e. number of calls to sproc should be the same as number of attributes in dimension).|||

Thank you. I don't quite follow. The dimension is wide (it has some 30 attributes). However, only one attribute hierarchy has an allowed set filter defined. Why does this need to be evaluated many times?

In addition, the dimension is large (some 2 mil plus members). Even with allowed set caching inside the stored procedure, it takes an enormous amount of time (some 10 min) for the filter to be applied, e.g. if the filter returns some 40,000 allowed members. Any optimization tips?

|||10 minutes to filter 40,000 members out of 2 million is way too much. I've written sprocs which were able to do comparable filtering in a matter of seconds. Of course, it depends on the logic inside sproc. Would you share more details please - what is the criteria for the filtering ?|||

Thank you for helping out.

As I mentioned, the dimension has some 2 mil members. I has a Security Filter attribute with some 50,000 members on which the allowed set is applied. The allowed set expression is StrToSet(<call to stored procedure here>). The stored procedure returns a comma-seperated list of the allowed members in the format [DimensionName].[Security Filter].&[key].

The stored procedure queries a database to get the set but this is very fast. Then, it caches the set to avoid repetative calls.

|||

> The allowed set expression is StrToSet(<call to stored procedure here>). The stored procedure returns a comma-seperated list of the allowed members in the format [DimensionName].[Security Filter].&[key].

This is not a good practice. Much better approach is to use Server Adomd.NET object model and return AdomdServer.Set from sproc instead of giant string. But since you query database table, I wonder whether you can make this table part of UDM and build joins inside UDM and avoid sproc altogether...

|||

OK, I will test both approaches (StrToSet and server-side Set) and post the results here.

-update

Thanks for the tip. Server Set resulted in much better performance. The first column shows the number of members in the allowed set. Time is in seconds.

Filer #	StrToSet	Set
5000	52	35
10000	90	58
20000	171	108
40000	272	144

end update

Would you mind eleborating more about the table idea? Do you mean a new dimension table that will slice the fact table or do you refer to a many-to-many dimension?

|||

> Would you mind eleborating more about the table idea? Do you mean a new dimension table that will slice the fact table or do you refer to a many-to-many dimension?

Since it seems that the information about which users can see which members is already stored in the table, you could build an aux measure group with User dimension and your dimension included, and then use something like

Exists(MyDimension.MyAttribute.MyAttribute.MEMBERS, StrToMember("[User].[" + Username + "]", "securitymeasuregroup")

as expression for the allowed set.

|||Thank you. But this will result in a very large fact table which will hold the allowed members for each user, corrrect? So, if I have 100 users and each user is allowed to see 100,000 members on average, the table will have 10,000,000 rows. I wonder how both approaches compare from a performance standpoint. Do you have any performance test results to share?|||Before we can compare the approaches - can you explain how the database table used by sproc looks like ? Doesn't it have the same structure (i.e. for user you need to be able to get list of allowed members), and therefore same size ?|||

To make the things simpler, let's assume that that in both cases the security policy will be materialized into the same table (dimension surrogate key, employee surrogate key). In the case of dynamic security, the stored procedure will query this table by user and apply the allowed set but the allowed set will not exceed the maximum number of members that the user can access. In the case of the security measure group, EXISTS can be potentially applied over millions of records. As the securiy table gets larger, I'd expect the benefits of EXISTS to level off. Or, will it?

|||Please note, that the call to Exists is always filtered by the current User, therefore there will never be a full scan of this measure group. I.e. if this measuregroup was implemented as pure ROLAP - it would generate exactly same SQL queries as you are likely doing from your sproc already. And, of course, in case of MOLAP it will be much more efficient. Therefore I beleive that the approach with EXISTS will always outperform approach with sproc no matter what sizes of tables will be.|||Thank you so much for you help, Mosha. I will post to this thread if I find otherwise.|||I think it will be very interesting to everybody to see results of your testing in either scenario. While I have seen approach that I recommended working well in number of installations, your data volumes (100 users each one having access to different set of 100,000 members out of 2 million) are interesting enough to see how well it will perform in the real world. My prediction is that it should perform well, but it is only a feeling without hard data behind it.|||

Got results. The materialized results are impressive! For the sake of testing, I generated all permutations of 250 employees with 52,000 securable items resulting in a fact table with some 13 mil rows. I compared this against the dynamic security approach where 52,000 securable items are converted to a Set. I tested the connect time from Excel and noted the query log events in the Profiler.

Dynamic Security Materialized Security (non-partitioned)

30-35 sec 8-12 sec

Not to mention that the dynamic security test currently excludes the application latency (records are read directly from a table). In real life, it would take additional time for stored procedure/service to prepare the security filter.

Thank you so much for the tip, Mosha! We will go probably with materialized security.

Do you think that partitioning the Security Filter measure group (if there is a way to logically group users and minimize the number of partitions) would decrease the query time even further?

Dynamic Security Stored Procedure Repeatedly Called

Thank you for helping out.

The stored procedure queries a database to get the set but this is very fast. Then, it caches the set to avoid repetative calls.

|||

OK, I will test both approaches (StrToSet and server-side Set) and post the results here.

-update

Thanks for the tip. Server Set resulted in much better performance. The first column shows the number of members in the allowed set. Time is in seconds.

Filer #	StrToSet	Set
5000	52	35
10000	90	58
20000	171	108
40000	272	144

end update

Would you mind eleborating more about the table idea? Do you mean a new dimension table that will slice the fact table or do you refer to a many-to-many dimension?

|||

> Would you mind eleborating more about the table idea? Do you mean a new dimension table that will slice the fact table or do you refer to a many-to-many dimension?

Exists(MyDimension.MyAttribute.MyAttribute.MEMBERS, StrToMember("[User].[" + Username + "]", "securitymeasuregroup")

as expression for the allowed set.

Dynamic Security Materialized Security (non-partitioned)

30-35 sec 8-12 sec

Thank you so much for the tip, Mosha! We will go probably with materialized security.

Do you think that partitioning the Security Filter measure group (if there is a way to logically group users and minimize the number of partitions) would decrease the query time even further?

Dynamic Security Stored Procedure Repeatedly Called

Thank you for helping out.

The stored procedure queries a database to get the set but this is very fast. Then, it caches the set to avoid repetative calls.

|||

OK, I will test both approaches (StrToSet and server-side Set) and post the results here.

-update

Thanks for the tip. Server Set resulted in much better performance. The first column shows the number of members in the allowed set. Time is in seconds.

Filer #	StrToSet	Set
5000	52	35
10000	90	58
20000	171	108
40000	272	144

end update

Would you mind eleborating more about the table idea? Do you mean a new dimension table that will slice the fact table or do you refer to a many-to-many dimension?

|||

> Would you mind eleborating more about the table idea? Do you mean a new dimension table that will slice the fact table or do you refer to a many-to-many dimension?

Exists(MyDimension.MyAttribute.MyAttribute.MEMBERS, StrToMember("[User].[" + Username + "]", "securitymeasuregroup")

as expression for the allowed set.

Dynamic Security Materialized Security (non-partitioned)

30-35 sec 8-12 sec

Thank you so much for the tip, Mosha! We will go probably with materialized security.

Do you think that partitioning the Security Filter measure group (if there is a way to logically group users and minimize the number of partitions) would decrease the query time even further?

sql

Thursday, March 29, 2012

Dynamic Server Path

Dynamic selection of flat file

Dynamic selecting a table

Dynamic selecting a table

Dynamic selecting a table

Dynamic Select/Update Statement Possible?

Dynamic Select Statement

Dynamic Select Statement

Dynamic Select Statement

Dynamic Select list in Stored Proc

Dynamic Select in MSSQL

Dynamic SELECT Command in SqlDataSource

Dynamic Security to Cube

Dynamic Security to Cube

Dynamic Security Stored Procedure Repeatedly Called

Dynamic Security Stored Procedure Repeatedly Called

Dynamic Security Stored Procedure Repeatedly Called

sql dwh problem

Blog Archive

About Me