Hi Gurus
I am trying to develop an application around SQL server. The app allows
the user to input a where clause at runtime, which the application runs
against. The only way the app can use the input where clause is by
building an SQL statement including the where clause, and then calling
sp_executeSQL against it. This is inefficient; ideally I would like to
compile the SQL statement (as it will be used multiple times) - but how
can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
from inside another statement (it must be done explicitly with a
hardcoded where statement).
Is there any wat I can improve on building the statement and calling
sp_executeSQL every time I need to run it?"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169494423.824216.35130@.38g2000cwa.googlegroups.com...
> Hi Gurus
> I am trying to develop an application around SQL server. The app allows
> the user to input a where clause at runtime, which the application runs
> against. The only way the app can use the input where clause is by
> building an SQL statement including the where clause, and then calling
> sp_executeSQL against it. This is inefficient;
Why do you think that? SQL Server will cache and reuse SQL queries sent
using sp_executesql, especially if you use parameter markers in the query.
See
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/pr...005/recomp.mspx
Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/aa175244(SQL.80).aspx
> ideally I would like to
> compile the SQL statement (as it will be used multiple times) - but how
> can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
> from inside another statement (it must be done explicitly with a
> hardcoded where statement).
> Is there any wat I can improve on building the statement and calling
> sp_executeSQL every time I need to run it?
>
No. That is really a perfectly fine thing to do. Consider using parameter
markers in sp_executesql to get query plan reuse and reduce compilations.
However if the volume of these dynamic queries is not great, or the cost of
each query to execute is large, then plan reuse may not be important or even
desirable.
David|||Thanks very much for that David.
The queries don't have parameters - they are always exactly the same
every time they are run (the user may change the where clause, but this
will be a very infrequent event). However, there could be up to 255 of
these queries (and where clauses), which could potentially all get
called (in series) every time a row is inserted into a certain key
table. A row is inserted in the key table possibly several times a
second (worst case).|||"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169495992.357049.50780@.m58g2000cwm.googlegroups.com...
> Thanks very much for that David.
> The queries don't have parameters - they are always exactly the same
> every time they are run (the user may change the where clause, but this
> will be a very infrequent event). However, there could be up to 255 of
> these queries (and where clauses), which could potentially all get
> called (in series) every time a row is inserted into a certain key
> table. A row is inserted in the key table possibly several times a
> second (worst case).
>
Ok, 255 different queries isn't much at all. I would just let SQL Server
cache them.
David
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Monday, March 26, 2012
dynamic procedure creation - possible?
Hi Gurus
I am trying to develop an application around SQL server. The app allows
the user to input a where clause at runtime, which the application runs
against. The only way the app can use the input where clause is by
building an SQL statement including the where clause, and then calling
sp_executeSQL against it. This is inefficient; ideally I would like to
compile the SQL statement (as it will be used multiple times) - but how
can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
from inside another statement (it must be done explicitly with a
hardcoded where statement).
Is there any wat I can improve on building the statement and calling
sp_executeSQL every time I need to run it?
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169494423.824216.35130@.38g2000cwa.googlegrou ps.com...
> Hi Gurus
> I am trying to develop an application around SQL server. The app allows
> the user to input a where clause at runtime, which the application runs
> against. The only way the app can use the input where clause is by
> building an SQL statement including the where clause, and then calling
> sp_executeSQL against it. This is inefficient;
Why do you think that? SQL Server will cache and reuse SQL queries sent
using sp_executesql, especially if you use parameter markers in the query.
See
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/aa175244(SQL.80).aspx
> ideally I would like to
> compile the SQL statement (as it will be used multiple times) - but how
> can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
> from inside another statement (it must be done explicitly with a
> hardcoded where statement).
> Is there any wat I can improve on building the statement and calling
> sp_executeSQL every time I need to run it?
>
No. That is really a perfectly fine thing to do. Consider using parameter
markers in sp_executesql to get query plan reuse and reduce compilations.
However if the volume of these dynamic queries is not great, or the cost of
each query to execute is large, then plan reuse may not be important or even
desirable.
David
|||Thanks very much for that David.
The queries don't have parameters - they are always exactly the same
every time they are run (the user may change the where clause, but this
will be a very infrequent event). However, there could be up to 255 of
these queries (and where clauses), which could potentially all get
called (in series) every time a row is inserted into a certain key
table. A row is inserted in the key table possibly several times a
second (worst case).
|||"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169495992.357049.50780@.m58g2000cwm.googlegro ups.com...
> Thanks very much for that David.
> The queries don't have parameters - they are always exactly the same
> every time they are run (the user may change the where clause, but this
> will be a very infrequent event). However, there could be up to 255 of
> these queries (and where clauses), which could potentially all get
> called (in series) every time a row is inserted into a certain key
> table. A row is inserted in the key table possibly several times a
> second (worst case).
>
Ok, 255 different queries isn't much at all. I would just let SQL Server
cache them.
David
sql
I am trying to develop an application around SQL server. The app allows
the user to input a where clause at runtime, which the application runs
against. The only way the app can use the input where clause is by
building an SQL statement including the where clause, and then calling
sp_executeSQL against it. This is inefficient; ideally I would like to
compile the SQL statement (as it will be used multiple times) - but how
can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
from inside another statement (it must be done explicitly with a
hardcoded where statement).
Is there any wat I can improve on building the statement and calling
sp_executeSQL every time I need to run it?
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169494423.824216.35130@.38g2000cwa.googlegrou ps.com...
> Hi Gurus
> I am trying to develop an application around SQL server. The app allows
> the user to input a where clause at runtime, which the application runs
> against. The only way the app can use the input where clause is by
> building an SQL statement including the where clause, and then calling
> sp_executeSQL against it. This is inefficient;
Why do you think that? SQL Server will cache and reuse SQL queries sent
using sp_executesql, especially if you use parameter markers in the query.
See
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/aa175244(SQL.80).aspx
> ideally I would like to
> compile the SQL statement (as it will be used multiple times) - but how
> can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
> from inside another statement (it must be done explicitly with a
> hardcoded where statement).
> Is there any wat I can improve on building the statement and calling
> sp_executeSQL every time I need to run it?
>
No. That is really a perfectly fine thing to do. Consider using parameter
markers in sp_executesql to get query plan reuse and reduce compilations.
However if the volume of these dynamic queries is not great, or the cost of
each query to execute is large, then plan reuse may not be important or even
desirable.
David
|||Thanks very much for that David.
The queries don't have parameters - they are always exactly the same
every time they are run (the user may change the where clause, but this
will be a very infrequent event). However, there could be up to 255 of
these queries (and where clauses), which could potentially all get
called (in series) every time a row is inserted into a certain key
table. A row is inserted in the key table possibly several times a
second (worst case).
|||"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169495992.357049.50780@.m58g2000cwm.googlegro ups.com...
> Thanks very much for that David.
> The queries don't have parameters - they are always exactly the same
> every time they are run (the user may change the where clause, but this
> will be a very infrequent event). However, there could be up to 255 of
> these queries (and where clauses), which could potentially all get
> called (in series) every time a row is inserted into a certain key
> table. A row is inserted in the key table possibly several times a
> second (worst case).
>
Ok, 255 different queries isn't much at all. I would just let SQL Server
cache them.
David
sql
dynamic procedure creation - possible?
Hi Gurus
I am trying to develop an application around SQL server. The app allows
the user to input a where clause at runtime, which the application runs
against. The only way the app can use the input where clause is by
building an SQL statement including the where clause, and then calling
sp_executeSQL against it. This is inefficient; ideally I would like to
compile the SQL statement (as it will be used multiple times) - but how
can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
from inside another statement (it must be done explicitly with a
hardcoded where statement).
Is there any wat I can improve on building the statement and calling
sp_executeSQL every time I need to run it?"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169494423.824216.35130@.38g2000cwa.googlegroups.com...
> Hi Gurus
> I am trying to develop an application around SQL server. The app allows
> the user to input a where clause at runtime, which the application runs
> against. The only way the app can use the input where clause is by
> building an SQL statement including the where clause, and then calling
> sp_executeSQL against it. This is inefficient;
Why do you think that? SQL Server will cache and reuse SQL queries sent
using sp_executesql, especially if you use parameter markers in the query.
See
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/aa175244(SQL.80).aspx
> ideally I would like to
> compile the SQL statement (as it will be used multiple times) - but how
> can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
> from inside another statement (it must be done explicitly with a
> hardcoded where statement).
> Is there any wat I can improve on building the statement and calling
> sp_executeSQL every time I need to run it?
>
No. That is really a perfectly fine thing to do. Consider using parameter
markers in sp_executesql to get query plan reuse and reduce compilations.
However if the volume of these dynamic queries is not great, or the cost of
each query to execute is large, then plan reuse may not be important or even
desirable.
David|||Thanks very much for that David.
The queries don't have parameters - they are always exactly the same
every time they are run (the user may change the where clause, but this
will be a very infrequent event). However, there could be up to 255 of
these queries (and where clauses), which could potentially all get
called (in series) every time a row is inserted into a certain key
table. A row is inserted in the key table possibly several times a
second (worst case).|||"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169495992.357049.50780@.m58g2000cwm.googlegroups.com...
> Thanks very much for that David.
> The queries don't have parameters - they are always exactly the same
> every time they are run (the user may change the where clause, but this
> will be a very infrequent event). However, there could be up to 255 of
> these queries (and where clauses), which could potentially all get
> called (in series) every time a row is inserted into a certain key
> table. A row is inserted in the key table possibly several times a
> second (worst case).
>
Ok, 255 different queries isn't much at all. I would just let SQL Server
cache them.
David
I am trying to develop an application around SQL server. The app allows
the user to input a where clause at runtime, which the application runs
against. The only way the app can use the input where clause is by
building an SQL statement including the where clause, and then calling
sp_executeSQL against it. This is inefficient; ideally I would like to
compile the SQL statement (as it will be used multiple times) - but how
can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
from inside another statement (it must be done explicitly with a
hardcoded where statement).
Is there any wat I can improve on building the statement and calling
sp_executeSQL every time I need to run it?"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169494423.824216.35130@.38g2000cwa.googlegroups.com...
> Hi Gurus
> I am trying to develop an application around SQL server. The app allows
> the user to input a where clause at runtime, which the application runs
> against. The only way the app can use the input where clause is by
> building an SQL statement including the where clause, and then calling
> sp_executeSQL against it. This is inefficient;
Why do you think that? SQL Server will cache and reuse SQL queries sent
using sp_executesql, especially if you use parameter markers in the query.
See
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/aa175244(SQL.80).aspx
> ideally I would like to
> compile the SQL statement (as it will be used multiple times) - but how
> can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
> from inside another statement (it must be done explicitly with a
> hardcoded where statement).
> Is there any wat I can improve on building the statement and calling
> sp_executeSQL every time I need to run it?
>
No. That is really a perfectly fine thing to do. Consider using parameter
markers in sp_executesql to get query plan reuse and reduce compilations.
However if the volume of these dynamic queries is not great, or the cost of
each query to execute is large, then plan reuse may not be important or even
desirable.
David|||Thanks very much for that David.
The queries don't have parameters - they are always exactly the same
every time they are run (the user may change the where clause, but this
will be a very infrequent event). However, there could be up to 255 of
these queries (and where clauses), which could potentially all get
called (in series) every time a row is inserted into a certain key
table. A row is inserted in the key table possibly several times a
second (worst case).|||"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169495992.357049.50780@.m58g2000cwm.googlegroups.com...
> Thanks very much for that David.
> The queries don't have parameters - they are always exactly the same
> every time they are run (the user may change the where clause, but this
> will be a very infrequent event). However, there could be up to 255 of
> these queries (and where clauses), which could potentially all get
> called (in series) every time a row is inserted into a certain key
> table. A row is inserted in the key table possibly several times a
> second (worst case).
>
Ok, 255 different queries isn't much at all. I would just let SQL Server
cache them.
David
Wednesday, March 21, 2012
Dynamic lookup with periodical (Yearly) data using JOIN
Hi,
I am generating a dynamic SQL query to perform a lookup based on criterias entered by the user. The quey is generated by a C# application and the target database is Sql Server 2005.
Basically I have 2 tables:
--Cie-- (Over 40k companies, 1 rows per companies, total 40k rows)
(
CieId int not null,
Symbol varchar,
CieName varchar
) Primary key(CieId)
--YearlyData-- (Over 40k companies, 20 years of data per companies, total 800k rows)
(
CieId int not null,
Year int not null,
Revenue currency,
NetIncome currency,
[...over 170 columns]
)Primary key(CieId, Year)
//Sample Case:
The user want to get the list of all the compagnies (CieId) where
YearlyData.NetIncome > 25k for YearlyData.Year 2005
and YearlyData.Revenue > 100k for YearlyData.Year 2005
and YearlyData.Revenue > 95k for YearlyData.Year 2004
and YearlyData.Revenue > 90k for YearlyData.Year 2003
and YearlyData.Revenue of YearlyData.Year 2005 > YearlyData.Revenue of YearlyData.Year 2004
Currently, the Sql query that would be generated looks like this:
SELECT Cie.CieId
FROM Cie
JOIN (select CieId, Revenue, NetIncome from YearlyData where Year = 2005) Y2005 ON Y2005.CieId = Cie.CieId
JOIN (select CieId, Revenue, NetIncome from YearlyData where Year = 2004) Y2004 ON Y2004.CieId = Cie.CieId
JOIN (select CieId, Revenue, NetIncome from YearlyData where Year = 2003) Y2003 ON Y2003.CieId = Cie.CieId
WHERE 1=1
AND Y2005.NetIncome > 25000
AND Y2005.Revenue > 100000
AND Y2004.Revenue > 95000
AND Y2003.Revenue > 90000
AND Y2005.Revenue > Y2004.Revenue
I dont feel good about all those JOINs especially since there is up to 20 years of data and the user could use all of them in his query. The request become slower with each new join.
Does anyone see a better way to do this, maybe a PIVOT or something?
Thanks
Math:
I mocked this up establishing 40k cie entries and 800k yearly records. When I ran your query I got the expected execution plan and the expected performance. When the data is not cached it ran in about 15 seconds and with the data cached in ran in zero seconds. I got, of course, 3 table (clustered index) scans as it read in the yearly data. I got the merge join (which I expected).
My view is that (1) this is a report and (2) it doesn't figure to be run often and therefore performance for this is not critical. Now, I did show about 10,000 logical IOs as it scanned the tables. This will roughly amount to a grab of about 80Meg of buffer cache whenever this query runs. For most modern servers used for SQL Server 2005 this should not be a problem. Since (1) this is a report and doesn't figure to run often and (2) it seems to provide very adequate performance for a report I would not take any time to try to upgrade the performance of this; I don't figure you will get a good return on time invested in this report unless you are having some specific problems.
One thing that can be done to provide a marginal performance boost for this particular is to build a "cover index" for the specific columns you are using for this report; however, I don't feel like this is a good investment either. The modest boost received from the cover index does not figure to be a good tradeoff for the cost of the necessary cover index.
Are you having some specific problems? If so, what are they?
|||
Dave
Dave, thank you for the reply.
I dont have any specific problems other than perfomances, I am just trying to see if there is a better way to construct this kind of query (i.e. without 1 join per year). This is not really a report, it is more like a screener, a tools used when looking for companies to invest in. The screener is used frequently so performance is an issue. The data is readonly with only 1 update at the end of each day so we indexed every columns (grouped logicaly).
The query in the OP was only a sample case, the user can really compare:
"columnX of yearX" with "columnX of yearY"
"columnX of yearX" with "columnY of yearY"
"columnX of yearX" with "columnY of yearX"
"columnX of yearX" with a static value (like 250.95)
and more
Do you think the query can be resructured so it perform better (like removing the "join per year")?
Thanks
|||Would partitioning by year be helpful? It seems to be a common filter.|||You can do below query instead and there is no reason to join with Cie table if you want just CieId values and there is FK relationship between YearlyData & Cie on CieId column.
select y.CieId
from YearlyData as y
where y.Year in (2003, 2004, 2005)
group by y.CieId
having max(case y.Year when 2005 then NetIncome end) > 25000
and max(case y.Year when 2005 then Revenue end) > 100000
and max(case y.Year when 2004 then Reveue end) > 95000
and max(case y.Year when 2003 then Reveue end) > 90000
and max(case y.Year when 2005 then Revenue end) > max(case y.Year when 2004 then Reveue end)
This query will provide best performance if there is an index on Year or (Year, CieId). You seem to have primary key on (CieId, Year) which will not help for this particular query resulting in a table/index scan.
|||Math:
Both ideas help. When I ran Umachandar's query it reduced the logical IOs from about 10000 to about 3200, reduced alpha read time to about 4600 ms and reduced beta read time to around 420 ms. When I then partitioned the data on year logical IO was further reduced from 3200 to about 500, alpha read time from 4600ms to about 3200ms and beta read time from 420ms to about 380 ms.
|||
Dave
Both idea looks good, I will see if this query structure is compatible with all my test case and keep you inform.
Thank you for your help.
Monday, March 19, 2012
Dynamic Group Sorting in Crystal Report
i am using crystal report application in visual studio .net. Now, I make one crystal report, has 5 groups. Now, I want to change their order from VB .net application. What to do for that?? I'm new in crystal report.... I guess you need to organize your dynamic grouping in your report. You can use a parameter(s) for that, it will let you/user to select/change a group. Then create a formula based on the parameter response.
For example:
I would create a parameter to allow you/user to select 'SDate' Shipping Date or 'CustID' - Customer ID.
Then I would write a formula for my 1st group @.Group1:
If {?Group1}='SDate' then {table.shipping_date} else {table.CustomerID}
For my 2nd group, I would write a formula @.Group2:
If {?Group1}='SDate' {table.CustomerID} else {table.shipping_date}
After that, I would grouped my records on @.Group1 and on @.Grop2.
----
Or you can create one parameter per group and then a formula based on that parameter (1 per group as well):
For your 1st group I would create a parameter {?Group1} which allows me to select 'SDate' for shipping date, 'CustID' for CustomerID, 'Country' for country code etc.
Then create a formula @.Group1:
If {?Group1}='SDate' then {table.shipping_gdate} else
If {?Group1}='CustID' then {table.CustomerID} else
If {?Group1}='Country' then {table.CountryCode} else
For my 2nd group I would create a parameter {?Group2} and then a formula @.Group2:
If {?Group2}='Source' then {table.origination_number} else
If {?Group2}='DestCity' then {table.destination_city} else
If {?Grop2}='Carrier' then {tabme.CarrierID} else
.
.
.
You can create as many group options as you need.
I hope this will be helpful
For example:
I would create a parameter to allow you/user to select 'SDate' Shipping Date or 'CustID' - Customer ID.
Then I would write a formula for my 1st group @.Group1:
If {?Group1}='SDate' then {table.shipping_date} else {table.CustomerID}
For my 2nd group, I would write a formula @.Group2:
If {?Group1}='SDate' {table.CustomerID} else {table.shipping_date}
After that, I would grouped my records on @.Group1 and on @.Grop2.
----
Or you can create one parameter per group and then a formula based on that parameter (1 per group as well):
For your 1st group I would create a parameter {?Group1} which allows me to select 'SDate' for shipping date, 'CustID' for CustomerID, 'Country' for country code etc.
Then create a formula @.Group1:
If {?Group1}='SDate' then {table.shipping_gdate} else
If {?Group1}='CustID' then {table.CustomerID} else
If {?Group1}='Country' then {table.CountryCode} else
For my 2nd group I would create a parameter {?Group2} and then a formula @.Group2:
If {?Group2}='Source' then {table.origination_number} else
If {?Group2}='DestCity' then {table.destination_city} else
If {?Grop2}='Carrier' then {tabme.CarrierID} else
.
.
.
You can create as many group options as you need.
I hope this will be helpful
Friday, March 9, 2012
Dynamic Field DB Schema Brainstorming
Hello -
Have a project where we are going to build a form creation application. (ASP.NET). This will allow an administrator to build a form on the fly - this form will appear on the front end of the site.
This is a fairly common thing. Are there any resources out there as to where to start designing the DB schema? I'm not looking to reinvent the wheel. Here's the basic objects I'm seeing:
Tables
Forms
FormFieldNames
FormFieldTypes
FormFieldJS
Any tips on the right direction to go?
Thanks
Rob
Try the link below and right click to download the PPT slides for datamodeling. The key to data modeling is files and associations, that means you may have fifty files but you may only have three tables based on files association. The table relationship is determined by upper and lower bound cardinality and it is not complicated. There are six complete database catalogs in the book. Hope this helps.
http://wings.buffalo.edu/mgmt/courses/mgtsand/data.html|||
You need to consider other solutions, technologies, tools. XML may be suited formatted for this -- the administrator would define an XML schema (possibly through your tool) and then build a form to input data to create XML documents that conform to the schema.
No less, any time I see anyone try to do this in a RDBMS, it falls apart in less than a year -- if it ever makes it out of development.|||
Have a project where we are going to build a form creation application. (ASP.NET). This will allow an administrator to build a form on the fly - this form will appear on the front end of the site.
This is a fairly common thing. Are there any resources out there as to where to start designing the DB schema? I'm not looking to reinvent the wheel. Here's the basic objects I'm seeing:
Tables
Forms
FormFieldNames
FormFieldTypes
FormFieldJS
Any tips on the right direction to go?
Thanks
Rob
Try the link below and right click to download the PPT slides for datamodeling. The key to data modeling is files and associations, that means you may have fifty files but you may only have three tables based on files association. The table relationship is determined by upper and lower bound cardinality and it is not complicated. There are six complete database catalogs in the book. Hope this helps.
http://wings.buffalo.edu/mgmt/courses/mgtsand/data.html|||
Thanks for the resource, good stuff - organized nicely. I was looking specifically to my example, so I didn't plow through an ERD of what's already out there, thankx.
|||A relational database sounds like a completely inapropiate tool to build this application you're looking to build. When you are using a RDBMS, you need to know what data you are modeling. You can't just try to make it up on the fly.You need to consider other solutions, technologies, tools. XML may be suited formatted for this -- the administrator would define an XML schema (possibly through your tool) and then build a form to input data to create XML documents that conform to the schema.
No less, any time I see anyone try to do this in a RDBMS, it falls apart in less than a year -- if it ever makes it out of development.|||
Sounds like you're both directing me the same way - a relatively small DB schema that holds the forms, the fields, and descriptive text for the fields, etc. The actual field types, values, javascript, etc... should be in a bunch of files that describe each fieldID, most likely in XML.
Thank you for that. We were thinking about compiling a bunch of .JS files for each field, and then one large.js file for the form aggregate fields. But XML would make more sense for the fields' descriptive data I believe, no?
Dynamic Dimension security options
Hi
I want to filter members in the dimension based on the the access an application user has access to. Which will be stored in the applicaion database table.
Questions
1. Can somebody post the example of how to read this database table to retreive members so that I could set the allowed member section of the dimension security for the application user (not on windows domain)
2. Is this the good way off doing it or there is a better way to provide dimension member security for the application.
3. In my case none of the cubes data, tables or data sources has any application user inforamation stored aginast them. What I building is separate user repository and want to provide facility where administrator will be able to configure dimesion securty in the ASP.NET web application (using forms authentication). and then when user logs into the system he wshould be able to see only those dimesion members to which he has access to. Please let me know how the diffenten ways to acheive this.
Thanks
Milind
Bryan
Have you experienced performance issues with this solution? I implemented a solution similar to this but for some reason my performance has slowed significantly on MDX queries for users in my dynamic security role.
Thanks
|||What are you using to define your allowed set?
B.
|||
A simple way of approaching this problem is to create a measure group with the user-dimension key combination. The measure group would have a single measure, a count, which you would make not visible to users in order to hide that measure group.
In the cube dimension you want to restrict, you defined an allowed set something like this:
NONEMPTYCROSSJOIN(
[My Dimension].[My Attribute].Members,
STRTOMEMBER("[User].[User Name].[" + USERNAME + "]"),
[Measure].[MySecurityMeasureGroupCount],
1)
It's important to note the USERNAME function returns the a string in the format of domain\user. You can use many of the common VB-type string parsing functions to alter the format if you need it. (Also, the MySecurityMeasureGroupCount is the count you hid above.
Bryan
|||Hi Bryan,
In the scenario, I am trying. no user information is stored in the cube data. what I have is a screen where administrators will configure access to dimension member for the application users and this information will be stored into application database. the table structure will be like this
User Id, Dimension, Members
User123 Location [Location].[Some level member].(member].value
This table will be real time meaning administrator will be updating this table as and when required for any application user. I want to have this changes in effect imeddiately whenever administrators makes this changes.
Can you please let me know how would you acheive this with performance taking into consideration.
Cheeeers
Milind
|||Hello!I have similar scenario with such table and real-time change propagation. I'm processing the cube to force re-read of user's permission. This is only necessary when user with changed permission set was already connected before that change. You can use "Process script cache" type, or Default, which wouldn't do the real process if cube is in processed state. However, it will cut off all the other users, so they will be also forced to connect again.
Radim
|||
I would still recommend the approach outlined above. This is a standard way of implementing user-specific allowed sets. If you need real-time updates, try building that one measure group as Rolap or as Holap with ProActive Caching.
You potentially could do a realtime lookup with a custom assembly. (See Books Online page titled "Working with Stored Procedures (Analysis Services)".) Still, this is not a commonly used technique so I wouldn't recommend this at this time.
Bryan
|||
Bryan C. Smith wrote:
I would still recommend the approach outlined above. This is a standard way of implementing user-specific allowed sets. If you need real-time updates, try building that one measure group as Rolap or as Holap with ProActive Caching.
You potentially could do a realtime lookup with a custom assembly. (See Books Online page titled "Working with Stored Procedures (Analysis Services)".) Still, this is not a commonly used technique so I wouldn't recommend this at this time.
Bryan
Bryan,
1. Can you please post the example of acheiving this...i mean some link to some help or some code.
2. Are you storing the uniquename of members in this user dimension member security table? Basically I will be building a screen wher application administrator will be able to browse dimesion members for any dimension and then they will select the members a particular user has access to. Could yuo please let me know is a good idear to store unique name of members and then is it possible to uses some what similat MDX expresssion to filter the dimesion mber in the allowed members set section of the dimension. ( it would be great if you could provide the example of the expresion in this case)
I appreciate your suggestion on this, this will give me comprehensive idea to go with the design.
Thanks in advance.
Waiting for your response.
Milind
|||Let's say you have a dimension called MyDimension that you want to secure on a user-by-user basis. Connect to the relational database and create the following:
Code Snippet
create table MyUser (
UserID int not null,
ADAccount varchar(256) not null
)
alter table MyUser add
constraint PK_MyUser primary key (userid),
constraint AK_MyUser unique (adaccount)
create table MyDimensionSecurity (
MyDimensionID int not null,
MyUserID int not null
)
alter table MyDimensionSecurity add
constraint PK_MyDimensionSecurity primary key (mydimensionid, myuserid),
constraint FK_MyDimensionSecurity_MyDimensionID foriegn key (mydimensionid) references MyDimension (MyDimensionID),
constraint FK_MyDimensionSecurity_MyUserID foriegn key (myuserid) references MyUser (MyUserID)
Add your list of users to the MyUser table. For this example, be sure you store the ADAccount value in the DOMAIN\user format. Use your interface to control which users get to see which MyDimensionRecords. This data is of course stored in the MyDimensionSecurity table.
Now, connect to SSAS. Add the MyUser and MyDimensionSecurity tables to the DSV.
Create a new dimension for MyUser. It will likely have a single attribute, MyUser, with UserID as the key and ADAccount as the name.
Open the cube designer window. Create a new measure group for the MyDimensionSecurity table. It will have a single measure, MyDimensionSecurity Count. Select the measure in this measure group, right-click, select properties, and set visible = false.
Go to your dimension usage tab. Make sure the MyUser dimension was added to the cube. If not, add it. Make sure the MyDimensionSecurity measure group has a relationship with the MyDimension and MyUser dimensions.
Now we're ready to set up security.
Create a role (or alter an existing role). Select the Dimension Data tab and select the MyDimension cube dimension. We will be exploiting a relationship that exists within the cube so be certain to NOT select the database dimension version.
Once you've selected the MyDimension CUBE dimension, set the attribute to the leaf-level (key) attribute of that dimension and go to the Advanced tab. In the Allowed Set text box, enter the following code:
Code Snippet
{
NONEMPTYCROSSJOIN(
[My Dimension].[My Dimension Key].Children,
STRTOMEMBER( "[My User].[My User]."+USERNAME+"]"),
[Measures].[My Dimension Security Count],
1
)
}
This will give the affect you want. It does leave a security hole in that the My User dimension is visible. Even if you make it not visible, users who know it's there can write MDX to access the list of users within it. It is adviseable you set an Allowed Set on the User dimension based on the USERNAME function as well. (It's syntax will not look like the code example above.)
Now, you have to figure out how to get updates in the SQL Server tables reflected in real-time. Start by reading the Books Online sections on HOLAP, ROLAP, and proactive caching.
Hope that helps get you started.
Bryan
|||Hi Bryan,
Thanks for your reply, it does helps. it has given me an ideaon how measure can be used to provide dimension security.
But solution you have provided still doesn't statisfy the requirement I have.
As per your example, I have understood following. please let me know if I have misunderstood something, may be because of little exp. on olap cubes.
1. This way I can protect members of only one diimension. it does require to have relation ship setup on the two tables (MyDimensionSecurity and MyDimension) making it the constraint for having same data type for the key columns. This means to secure members of other dimension in the cue MyOtherDimension i will have to create another table MyOtherDimensionSecurity and create one more measure based on it and setup a relationship between key columns of this new table and source table of MyOtherDimension dimension. Please confirm.
2. how can you extend this to show/hide members of any dimension attribute to one particular user using allowed members set expression. Please have a look at the table structure in my previos post for configuring dimension security for user using my asp.net application screen.
3. You ave mentioned ...."Go to your dimension usage tab. Make sure the MyUser dimension was added to the cube. If not, add it. Make sure the MyDimensionSecurity measure group has a relationship with the MyDimension and MyUser dimensions." can you please provide information on what kind of relation needs to be created and how to create. (This will be a great help)
I am after more generic way of configuring and securing dimension members of any dimension in the cube through asp.net application screen with help of "Allowed member set" section of advance tab of dimension data in role configuration.
Thanks,
Milind
|||Sorry for the delay in the response. Long weekend here.
Regarding item #1, you are correct. Every dimensions you wish to secure with user-specific allowed sets, you will need to set up this way. In most real-world situations where we use this, there are 1 or 2 sensitive dimensions that are user-specific. The rest we secure at a group level. In other words, just because you secure one dimension with this technique doesn't mean every dimension must not employee this technique. Use it only where required to minimize complexity and maintenance burden).
Regarding item #2, I'm not seeing the table structure you reference. Still, you can apply this technique wherever you have table that lists dimension key and user key combos.
Regarding item #3, the relationship-type you should employ between the security fact table and the user and the MyDimension dimensions would be "Regular". You use this because your fact table has the key of both the User and the MyDimension dimensions. (You could technically build a many-to-many relationship between your other fact tables and the User dimension using the security fact table (measure group) as the intermediate measure group. Still, I'd stick with just the simple relationships and the security approach outlined in the previous posts.)
Regarding an overall security strategy, keep your initial approach very simple. SSAS security is different from more traditional security. Try implementing something basic, verify it works, and then add one more element to the mix.
Good luck,
Bryan
Dynamic Dimension security options
Hi
I want to filter members in the dimension based on the the access an application user has access to. Which will be stored in the applicaion database table.
Questions
1. Can somebody post the example of how to read this database table to retreive members so that I could set the allowed member section of the dimension security for the application user (not on windows domain)
2. Is this the good way off doing it or there is a better way to provide dimension member security for the application.
3. In my case none of the cubes data, tables or data sources has any application user inforamation stored aginast them. What I building is separate user repository and want to provide facility where administrator will be able to configure dimesion securty in the ASP.NET web application (using forms authentication). and then when user logs into the system he wshould be able to see only those dimesion members to which he has access to. Please let me know how the diffenten ways to acheive this.
Thanks
Milind
A simple way of approaching this problem is to create a measure group with the user-dimension key combination. The measure group would have a single measure, a count, which you would make not visible to users in order to hide that measure group.
In the cube dimension you want to restrict, you defined an allowed set something like this:
NONEMPTYCROSSJOIN(
[My Dimension].[My Attribute].Members,
STRTOMEMBER("[User].[User Name].[" + USERNAME + "]"),
[Measure].[MySecurityMeasureGroupCount],
1)
It's important to note the USERNAME function returns the a string in the format of domain\user. You can use many of the common VB-type string parsing functions to alter the format if you need it. (Also, the MySecurityMeasureGroupCount is the count you hid above.
Bryan
|||Hi Bryan,
In the scenario, I am trying. no user information is stored in the cube data. what I have is a screen where administrators will configure access to dimension member for the application users and this information will be stored into application database. the table structure will be like this
User Id, Dimension, Members
User123 Location [Location].[Some level member].(member].value
This table will be real time meaning administrator will be updating this table as and when required for any application user. I want to have this changes in effect imeddiately whenever administrators makes this changes.
Can you please let me know how would you acheive this with performance taking into consideration.
Cheeeers
Milind
|||Hello!I have similar scenario with such table and real-time change propagation. I'm processing the cube to force re-read of user's permission. This is only necessary when user with changed permission set was already connected before that change. You can use "Process script cache" type, or Default, which wouldn't do the real process if cube is in processed state. However, it will cut off all the other users, so they will be also forced to connect again.
Radim
|||
I would still recommend the approach outlined above. This is a standard way of implementing user-specific allowed sets. If you need real-time updates, try building that one measure group as Rolap or as Holap with ProActive Caching.
You potentially could do a realtime lookup with a custom assembly. (See Books Online page titled "Working with Stored Procedures (Analysis Services)".) Still, this is not a commonly used technique so I wouldn't recommend this at this time.
Bryan
|||
I would still recommend the approach outlined above. This is a standard way of implementing user-specific allowed sets. If you need real-time updates, try building that one measure group as Rolap or as Holap with ProActive Caching.
You potentially could do a realtime lookup with a custom assembly. (See Books Online page titled "Working with Stored Procedures (Analysis Services)".) Still, this is not a commonly used technique so I wouldn't recommend this at this time.
Bryan
Bryan,
1. Can you please post the example of acheiving this...i mean some link to some help or some code.
2. Are you storing the uniquename of members in this user dimension member security table? Basically I will be building a screen wher application administrator will be able to browse dimesion members for any dimension and then they will select the members a particular user has access to. Could yuo please let me know is a good idear to store unique name of members and then is it possible to uses some what similat MDX expresssion to filter the dimesion mber in the allowed members set section of the dimension. ( it would be great if you could provide the example of the expresion in this case)
I appreciate your suggestion on this, this will give me comprehensive idea to go with the design.
Thanks in advance.
Waiting for your response.
Milind
|||Let's say you have a dimension called MyDimension that you want to secure on a user-by-user basis. Connect to the relational database and create the following:
Code Snippet
create table MyUser (
UserID int not null,
ADAccount varchar(256) not null
)
alter table MyUser add
constraint PK_MyUser primary key (userid),
constraint AK_MyUser unique (adaccount)
create table MyDimensionSecurity (
MyDimensionID int not null,
MyUserID int not null
)
alter table MyDimensionSecurity add
constraint PK_MyDimensionSecurity primary key (mydimensionid, myuserid),
constraint FK_MyDimensionSecurity_MyDimensionID foriegn key (mydimensionid) references MyDimension (MyDimensionID),
constraint FK_MyDimensionSecurity_MyUserID foriegn key (myuserid) references MyUser (MyUserID)
Add your list of users to the MyUser table. For this example, be sure you store the ADAccount value in the DOMAIN\user format. Use your interface to control which users get to see which MyDimensionRecords. This data is of course stored in the MyDimensionSecurity table.
Now, connect to SSAS. Add the MyUser and MyDimensionSecurity tables to the DSV.
Create a new dimension for MyUser. It will likely have a single attribute, MyUser, with UserID as the key and ADAccount as the name.
Open the cube designer window. Create a new measure group for the MyDimensionSecurity table. It will have a single measure, MyDimensionSecurity Count. Select the measure in this measure group, right-click, select properties, and set visible = false.
Go to your dimension usage tab. Make sure the MyUser dimension was added to the cube. If not, add it. Make sure the MyDimensionSecurity measure group has a relationship with the MyDimension and MyUser dimensions.
Now we're ready to set up security.
Create a role (or alter an existing role). Select the Dimension Data tab and select the MyDimension cube dimension. We will be exploiting a relationship that exists within the cube so be certain to NOT select the database dimension version.
Once you've selected the MyDimension CUBE dimension, set the attribute to the leaf-level (key) attribute of that dimension and go to the Advanced tab. In the Allowed Set text box, enter the following code:
Code Snippet
{
NONEMPTYCROSSJOIN(
[My Dimension].[My Dimension Key].Children,
STRTOMEMBER( "[My User].[My User]."+USERNAME+"]"),
[Measures].[My Dimension Security Count],
1
)
}
This will give the affect you want. It does leave a security hole in that the My User dimension is visible. Even if you make it not visible, users who know it's there can write MDX to access the list of users within it. It is adviseable you set an Allowed Set on the User dimension based on the USERNAME function as well. (It's syntax will not look like the code example above.)
Now, you have to figure out how to get updates in the SQL Server tables reflected in real-time. Start by reading the Books Online sections on HOLAP, ROLAP, and proactive caching.
Hope that helps get you started.
Bryan
|||Hi Bryan,
Thanks for your reply, it does helps. it has given me an ideaon how measure can be used to provide dimension security.
But solution you have provided still doesn't statisfy the requirement I have.
As per your example, I have understood following. please let me know if I have misunderstood something, may be because of little exp. on olap cubes.
1. This way I can protect members of only one diimension. it does require to have relation ship setup on the two tables (MyDimensionSecurity and MyDimension) making it the constraint for having same data type for the key columns. This means to secure members of other dimension in the cue MyOtherDimension i will have to create another table MyOtherDimensionSecurity and create one more measure based on it and setup a relationship between key columns of this new table and source table of MyOtherDimension dimension. Please confirm.
2. how can you extend this to show/hide members of any dimension attribute to one particular user using allowed members set expression. Please have a look at the table structure in my previos post for configuring dimension security for user using my asp.net application screen.
3. You ave mentioned ...."Go to your dimension usage tab. Make sure the MyUser dimension was added to the cube. If not, add it. Make sure the MyDimensionSecurity measure group has a relationship with the MyDimension and MyUser dimensions." can you please provide information on what kind of relation needs to be created and how to create. (This will be a great help)
I am after more generic way of configuring and securing dimension members of any dimension in the cube through asp.net application screen with help of "Allowed member set" section of advance tab of dimension data in role configuration.
Thanks,
Milind
|||Sorry for the delay in the response. Long weekend here.
Regarding item #1, you are correct. Every dimensions you wish to secure with user-specific allowed sets, you will need to set up this way. In most real-world situations where we use this, there are 1 or 2 sensitive dimensions that are user-specific. The rest we secure at a group level. In other words, just because you secure one dimension with this technique doesn't mean every dimension must not employee this technique. Use it only where required to minimize complexity and maintenance burden).
Regarding item #2, I'm not seeing the table structure you reference. Still, you can apply this technique wherever you have table that lists dimension key and user key combos.
Regarding item #3, the relationship-type you should employ between the security fact table and the user and the MyDimension dimensions would be "Regular". You use this because your fact table has the key of both the User and the MyDimension dimensions. (You could technically build a many-to-many relationship between your other fact tables and the User dimension using the security fact table (measure group) as the intermediate measure group. Still, I'd stick with just the simple relationships and the security approach outlined in the previous posts.)
Regarding an overall security strategy, keep your initial approach very simple. SSAS security is different from more traditional security. Try implementing something basic, verify it works, and then add one more element to the mix.
Good luck,
Bryan
Bryan
Have you experienced performance issues with this solution? I implemented a solution similar to this but for some reason my performance has slowed significantly on MDX queries for users in my dynamic security role.
Thanks
|||What are you using to define your allowed set?
B.
Wednesday, March 7, 2012
dynamic datasource
Our application is used by multiple companies. Each company has its own SQL
Server 2k database. Each company has access to the same list of reports in
SRS. We would like to deploy one physical copy of each report in the report
database for simplest maintenance of RDL files. Our application is deployed
in a web browser as an ASP application.
When a user in company A accesses Report 2 we need to render the report with
datasource parameters that will connect him to the correct db. At the same
time, users in companies B, C and D may be running the same report, each with
the correct datasource parameters for their companies.
Hence, as a report is requested by each user, we need to set up (or connect
to) the correct datasource parameters dynamically and then render the report
for the user.
Is there a way to do this with SRS? If multiple ways I would appreciate
knowing some of the options. If there are good books that discuss these
options I would appreciate references to them as well.
TIAHi joe,
We do something similar. We have multiple clients who use their own SQL
Database. We are using a custom data source (using ADO.net dataset). There
are numerous articles on the web which show you how to create one. Its pretty
simple and straightforward. The connection string is dynamically formed based
on the SQL Database name passed in as a parameter. This way we are able to
support multiple clients using a single copy of the rdl files.
Thanks,
George Tharakan
"Joe" wrote:
> Our application is used by multiple companies. Each company has its own SQL
> Server 2k database. Each company has access to the same list of reports in
> SRS. We would like to deploy one physical copy of each report in the report
> database for simplest maintenance of RDL files. Our application is deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report with
> datasource parameters that will connect him to the correct db. At the same
> time, users in companies B, C and D may be running the same report, each with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or connect
> to) the correct datasource parameters dynamically and then render the report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>|||Are you using the Report Manager or are you integrating with your own
application using either URL integration or Web services?
Assuming you are using Report Manager you can do the following:
1. have a report parameter that is based on the query. For the query use
pass the User!userid to a stored procedure (or use in a query) to query a
table and find out what database they should be using. Have this parameter
hidden so it can not be seen or modified by the user.
2. Using the generic query designer have the query use an expression like
this:
= "Select * from " & Parameters!DBName & ".dbo.mytable"
I usually test all this out with a report that has a single text box set to
this expression so I can see the query string and be sure it is correct
before I set a dataset source to it.
But, one issue you will have (with both this and the other solution on
creating your own extension) is that the UserID global parameter is not
usable from subscriptions. Really any solution this will be a problem
because you have to set the subcription to be for someone or it is run as
someone.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <dexter@.newco.com> wrote in message
news:28360A4A-BB83-4B5B-B55E-83A9F3B378E9@.microsoft.com...
> Our application is used by multiple companies. Each company has its own
> SQL
> Server 2k database. Each company has access to the same list of reports
> in
> SRS. We would like to deploy one physical copy of each report in the
> report
> database for simplest maintenance of RDL files. Our application is
> deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report
> with
> datasource parameters that will connect him to the correct db. At the
> same
> time, users in companies B, C and D may be running the same report, each
> with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or
> connect
> to) the correct datasource parameters dynamically and then render the
> report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>|||Thanks very much for both of your responses.
I have been attempting to build an extension. It's hard to tell how far I
am from success because I don't know how to debug the code. Can you offer
advice on this?
I'm using the VS.NET Report Designer to build my reports. I can see the new
extension there, but cannot get a connection to work.
FYI - I'm using the ReportViewer control to embed the reports in my web app.|||Sorry, I have not done an extension. I suggest posting a new subject with
your questions for that so people that have done extensions know that is
what the question is about and can jump in and help you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <dexter@.newco.com> wrote in message
news:3537E448-CE86-4E68-87C7-C8719A62112B@.microsoft.com...
> Thanks very much for both of your responses.
> I have been attempting to build an extension. It's hard to tell how far I
> am from success because I don't know how to debug the code. Can you offer
> advice on this?
> I'm using the VS.NET Report Designer to build my reports. I can see the
> new
> extension there, but cannot get a connection to work.
> FYI - I'm using the ReportViewer control to embed the reports in my web
> app.|||We have struggled with the same issue.
We were able to implement the @.server parameter and use that parameter to
dynamically choose the sql server at runtime.
Have fun trying to figure out the authentication nightmare you will encounter.
"Joe" wrote:
> Our application is used by multiple companies. Each company has its own SQL
> Server 2k database. Each company has access to the same list of reports in
> SRS. We would like to deploy one physical copy of each report in the report
> database for simplest maintenance of RDL files. Our application is deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report with
> datasource parameters that will connect him to the correct db. At the same
> time, users in companies B, C and D may be running the same report, each with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or connect
> to) the correct datasource parameters dynamically and then render the report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>
Server 2k database. Each company has access to the same list of reports in
SRS. We would like to deploy one physical copy of each report in the report
database for simplest maintenance of RDL files. Our application is deployed
in a web browser as an ASP application.
When a user in company A accesses Report 2 we need to render the report with
datasource parameters that will connect him to the correct db. At the same
time, users in companies B, C and D may be running the same report, each with
the correct datasource parameters for their companies.
Hence, as a report is requested by each user, we need to set up (or connect
to) the correct datasource parameters dynamically and then render the report
for the user.
Is there a way to do this with SRS? If multiple ways I would appreciate
knowing some of the options. If there are good books that discuss these
options I would appreciate references to them as well.
TIAHi joe,
We do something similar. We have multiple clients who use their own SQL
Database. We are using a custom data source (using ADO.net dataset). There
are numerous articles on the web which show you how to create one. Its pretty
simple and straightforward. The connection string is dynamically formed based
on the SQL Database name passed in as a parameter. This way we are able to
support multiple clients using a single copy of the rdl files.
Thanks,
George Tharakan
"Joe" wrote:
> Our application is used by multiple companies. Each company has its own SQL
> Server 2k database. Each company has access to the same list of reports in
> SRS. We would like to deploy one physical copy of each report in the report
> database for simplest maintenance of RDL files. Our application is deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report with
> datasource parameters that will connect him to the correct db. At the same
> time, users in companies B, C and D may be running the same report, each with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or connect
> to) the correct datasource parameters dynamically and then render the report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>|||Are you using the Report Manager or are you integrating with your own
application using either URL integration or Web services?
Assuming you are using Report Manager you can do the following:
1. have a report parameter that is based on the query. For the query use
pass the User!userid to a stored procedure (or use in a query) to query a
table and find out what database they should be using. Have this parameter
hidden so it can not be seen or modified by the user.
2. Using the generic query designer have the query use an expression like
this:
= "Select * from " & Parameters!DBName & ".dbo.mytable"
I usually test all this out with a report that has a single text box set to
this expression so I can see the query string and be sure it is correct
before I set a dataset source to it.
But, one issue you will have (with both this and the other solution on
creating your own extension) is that the UserID global parameter is not
usable from subscriptions. Really any solution this will be a problem
because you have to set the subcription to be for someone or it is run as
someone.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <dexter@.newco.com> wrote in message
news:28360A4A-BB83-4B5B-B55E-83A9F3B378E9@.microsoft.com...
> Our application is used by multiple companies. Each company has its own
> SQL
> Server 2k database. Each company has access to the same list of reports
> in
> SRS. We would like to deploy one physical copy of each report in the
> report
> database for simplest maintenance of RDL files. Our application is
> deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report
> with
> datasource parameters that will connect him to the correct db. At the
> same
> time, users in companies B, C and D may be running the same report, each
> with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or
> connect
> to) the correct datasource parameters dynamically and then render the
> report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>|||Thanks very much for both of your responses.
I have been attempting to build an extension. It's hard to tell how far I
am from success because I don't know how to debug the code. Can you offer
advice on this?
I'm using the VS.NET Report Designer to build my reports. I can see the new
extension there, but cannot get a connection to work.
FYI - I'm using the ReportViewer control to embed the reports in my web app.|||Sorry, I have not done an extension. I suggest posting a new subject with
your questions for that so people that have done extensions know that is
what the question is about and can jump in and help you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <dexter@.newco.com> wrote in message
news:3537E448-CE86-4E68-87C7-C8719A62112B@.microsoft.com...
> Thanks very much for both of your responses.
> I have been attempting to build an extension. It's hard to tell how far I
> am from success because I don't know how to debug the code. Can you offer
> advice on this?
> I'm using the VS.NET Report Designer to build my reports. I can see the
> new
> extension there, but cannot get a connection to work.
> FYI - I'm using the ReportViewer control to embed the reports in my web
> app.|||We have struggled with the same issue.
We were able to implement the @.server parameter and use that parameter to
dynamically choose the sql server at runtime.
Have fun trying to figure out the authentication nightmare you will encounter.
"Joe" wrote:
> Our application is used by multiple companies. Each company has its own SQL
> Server 2k database. Each company has access to the same list of reports in
> SRS. We would like to deploy one physical copy of each report in the report
> database for simplest maintenance of RDL files. Our application is deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report with
> datasource parameters that will connect him to the correct db. At the same
> time, users in companies B, C and D may be running the same report, each with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or connect
> to) the correct datasource parameters dynamically and then render the report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>
Sunday, February 26, 2012
Dynamic data elements for a data collection application
What is the better table design for a data collection application.
1. Vertical model (pk, attributeName, AttributeValue)
2. Custom columns (pk, custom1, custom2, custom3...custom50)
1. Vertical model (pk, attributeName, AttributeValue)
2. Custom columns (pk, custom1, custom2, custom3...custom50)
Since the data elements collected may change year over year, which
model better takes of this column dynamicness(mittal.pradeep@.gmail.com) writes:
> What is the better table design for a data collection application.
> 1. Vertical model (pk, attributeName, AttributeValue)
> 2. Custom columns (pk, custom1, custom2, custom3...custom50)
> Since the data elements collected may change year over year, which
> model better takes of this column dynamicness
The vertical model is certainly cleaner from a relational perspective.
It also requires less maintenance.
But admittedly queries can be more complex. If attributes can be of
different data types, you need some triggers to check this. A tip
is that the sql_variant data type is good in this case.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||1. But the data collection and reporting is in horizontal format. If
collected data is edited vertically, won't there be a extra steps of
converting horizontally obtained data to vertical and then vertical to
horizontal reports. In custom column model data always remains
horizontal. Won;t performance not be a issue in taking care of two
extra steps required in vertical model.
2. Won;t the concurrency be a issue, considering the fact that a
logical single horizontal row is edited as say 10 rows. Two people
might be changing same Primary key's different attributes at the same
time.
Erland Sommarskog wrote:
> (mittal.pradeep@.gmail.com) writes:
> > What is the better table design for a data collection application.
> > 1. Vertical model (pk, attributeName, AttributeValue)
> > 2. Custom columns (pk, custom1, custom2, custom3...custom50)
> > Since the data elements collected may change year over year, which
> > model better takes of this column dynamicness
> The vertical model is certainly cleaner from a relational perspective.
> It also requires less maintenance.
> But admittedly queries can be more complex. If attributes can be of
> different data types, you need some triggers to check this. A tip
> is that the sql_variant data type is good in this case.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(mittal.pradeep@.gmail.com) writes:
> 1. But the data collection and reporting is in horizontal format. If
> collected data is edited vertically, won't there be a extra steps of
> converting horizontally obtained data to vertical and then vertical to
> horizontal reports. In custom column model data always remains
> horizontal. Won;t performance not be a issue in taking care of two
> extra steps required in vertical model.
If you are to present ten of those custom values as columns in a report,
you get a 10-way self-join. Certainly bulky in code. Performance is
probably not top-notch, but I don't see that it would be absymal.
> 2. Won;t the concurrency be a issue, considering the fact that a
> logical single horizontal row is edited as say 10 rows. Two people
> might be changing same Primary key's different attributes at the same
> time.
Good point. This can be handled fairly easily, but it requires more
careful programming than the horizontal method.
Overall, there certainly is a tradeoff. If the set of custom fields are
faily stable, only change once per year or so, you might be prepared to
take the extra maintenance cost. But if users asks for new fields every
week, then the horizontal method could be a nightmare.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks a lot for the reply.
1. I am not able to understand why i require a 10 way join though. If i
have a mapping between custom column and actual column, all i need is a
dyanamic sql generated from the mapping.
E.g.
table
--
pk, custom1, custom2, custom3...custom10
map
--
customColName ActualColName
custom1 ActualName1
custom2 ActualName2
...
Now I can generate dynamic sql using map.
2. As long as number of custom columns is enough to take care data
element additions which happen in a year. All that is needed is
addition of new elements to mapping table to decifer newly assigned
custom columns
Erland Sommarskog wrote:
> (mittal.pradeep@.gmail.com) writes:
> > 1. But the data collection and reporting is in horizontal format. If
> > collected data is edited vertically, won't there be a extra steps of
> > converting horizontally obtained data to vertical and then vertical to
> > horizontal reports. In custom column model data always remains
> > horizontal. Won;t performance not be a issue in taking care of two
> > extra steps required in vertical model.
> If you are to present ten of those custom values as columns in a report,
> you get a 10-way self-join. Certainly bulky in code. Performance is
> probably not top-notch, but I don't see that it would be absymal.
> > 2. Won;t the concurrency be a issue, considering the fact that a
> > logical single horizontal row is edited as say 10 rows. Two people
> > might be changing same Primary key's different attributes at the same
> > time.
> Good point. This can be handled fairly easily, but it requires more
> careful programming than the horizontal method.
> Overall, there certainly is a tradeoff. If the set of custom fields are
> faily stable, only change once per year or so, you might be prepared to
> take the extra maintenance cost. But if users asks for new fields every
> week, then the horizontal method could be a nightmare.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Neither. The design flaw you are calling a vertical model is actually
known as "EAV" or "Entity-Attribute-Value" because it is a common
newbie mistake. I have no idea what your #2 means.
As your data elements change, you need to re-design the schema --
constraints, keys, data types, etc. Learn RDBMS and do it right.
I found an old "cut & paste". Someone like you posted this:
CREATE TABLE EAV -- no key declared
(key_col VARCHAR (10) NULL,
attrib_value VARCHAR (50) NULL);
INSERT INTO EAV VALUES ('LOCATION','Bedroom');
INSERT INTO EAV VALUES ('LOCATION','Dining Room');
INSERT INTO EAV VALUES ('LOCATION','Bathroom');
INSERT INTO EAV VALUES ('LOCATION','courtyard');
INSERT INTO EAV VALUES ('EVENT','verbal aggression');
INSERT INTO EAV VALUES ('EVENT','peer');
INSERT INTO EAV VALUES ('EVENT','bad behavior');
INSERT INTO EAV VALUES ('EVENT','other');
CREATE TABLE EAV_DATA -note lack of constraints, defaults, DRI
(id INTEGER IDENTITY (1,1) NOT NULL,
bts_id INTEGER NULL,
key_col VARCHAR (10) NULL,
attrib_value VARCHAR (50) NULL );
INSERT INTO EAV_DATA VALUES (1, 'LOCATION', 'Bedroom');
INSERT INTO EAV_DATA VALUES (1, 'EVENT', 'other');
INSERT INTO EAV_DATA VALUES (1, 'EVENT', 'bad behavior');
INSERT INTO EAV_DATA VALUES (2, 'LOCATION', 'Bedroom');
INSERT INTO EAV_DATA VALUES (2, 'EVENT', 'other');
INSERT INTO EAV_DATA VALUES (2, 'EVENT', 'verbal aggression');
INSERT INTO EAV_DATA VALUES (3, 'LOCATION', 'courtyard');
INSERT INTO EAV_DATA VALUES (3, 'EVENT', 'other');
INSERT INTO EAV_DATA VALUES (3, 'EVENT', 'peer');
Ideally, the result set of the query would be Location Event count
(headings if possible)
Bedroom verbal aggression 1
Bedroom peer 0
Bedroom bad behavior 0
Bedroom other 2
Dining Room verbal aggression 0
Dining Room peer 0
Dining Room bad behavior 0
Dining Room other 0
Bathroom verbal aggression 0
Bathroom peer 0
Bathroom bad behavior 0
Bathroom other 0
courtyard verbal aggression 0
courtyard peer 1
courtyard bad behavior 0
courtyard other 1
Also, if possible, another query would return this result set. (I think
I know how to do this one.)
Location Event count
Bedroom verbal aggression 1
Bedroom other 2
courtyard peer 1
courtyard other 1
Here is a From: Thomas Coleman
SELECT Locations.locationvalue, Events.eventvalue,
(SELECT COUNT(*)
FROM (SELECT LocationData.locationvalue, EventData.eventvalue
FROM (SELECT TD1.bts_id, TD1.value AS locationvalue
FROM eav_data AS TD1
WHERE TD1.key = 'location') AS LocationData
INNER JOIN
(SELECT TD2.bts_id, TD2.value AS eventvalue
FROM eav_data AS TD2
WHERE TD2.key = 'event'
) AS EventData
ON LocationData.bts_id = EventData.bts_id
) AS CollatedEventData
WHERE CollatedEventData.locationvalue = Locations.locationvalue
AND CollatedEventData.eventvalue = Events.eventvalue
FROM (SELECT T1.value AS locationvalue
FROM EAV AS T1
WHERE T1.key = 'location') AS Locations,
(SELECT T2.value AS eventvalue
FROM EAV AS T2
WHERE T2.key = 'event') AS Events
ORDER BY Locations.locationvalue, Events.eventvalue ,
SELECT Locations.locationvalue, Events.eventvalue
(SELECT COUNT(*)
FROM (SELECT LocationData.locationvalue, EventData.eventvalue
FROM (SELECT TD1.bts_id, TD1.value AS locationvalue
FROM eav_data AS TD1
WHERE TD1.key = 'location') AS LocationData
INNER JOIN
(SELECT TD2.bts_id, TD2.value AS eventvalue
FROM eav_data AS TD2
WHERE TD2.key = 'event') AS EventData
ON LocationData.bts_id = EventData.bts_id)
AS CollatedEventData
WHERE CollatedEventData.locationvalue = Locations.locationvalue
AND CollatedEventData.eventvalue = Events.eventvalue)
FROM (SELECT T1.value AS locationvalue
FROM EAV AS T1
WHERE T1.key = 'location') AS Locations,
(SELECT T2.value AS eventvalue
FROM EAV AS T2
WHERE T2.key = 'event') AS Events;
Is the same thing in a proper schema as:
SELECT L.locationvalue, E.eventvalue, COUNT(*)
FROM Locations AS L, Events AS E
WHERE L.btd_id = E.btd_id
GROUP BY L.locationvalue, E.eventvalue;
The reason that I had to use so many subqueries is that those entities
are all lopped into the same table. There should be separate tables for
Locations and Events.
The column names are seriously painful. Beyond the fact that I
personally hate underscores in column names, using underscores at the
end of the column name is really non-intuitive. I removed them for my
example and came across the next column name faux pas. Don't use "key"
and "value" for column names. It means that the developer *has*
surround the column name with square brackets for everything which is a
serious pain.
There is such a thing as "too" generic. There has to be some structure
or everything becomes nothing more than a couple of tables called
"things". The real key (no pun intended) is commonality. Is there a
pattern to the data that they want to store? It may not be possible to
create one structure to rule them all and in the darkness bind them.
"To be is to be something in particular; to be nothing in particular is
to be nothing." --Aristole
All data integrity is destroyed. Any typo becomes a new attribute or
entity. Entities are found missing attributes, so all the reports are
wrong.
ry to write a single CHECK() constraint that works for all the
attributes of those 30+ entities your users created because you were
too dumb or too lazy to do your job. It can be done! You need a case
expression almost 70 WHEN clauses for a simple invoice and order system
when I tried it as an exercise.
ry to write a single DEFAULT clause for 30+ entities crammed into one
column. Impossible!
Try to set up DRI actions among the entities. If you thought the WHEN
clauses in the single CASE expression were unmaintainable, wait until
you see the "TRIGGERs from Hell" -- Too bad that they might not fit
into older SQL Server which had some size limits. Now maintain it.
For those who are interested, there are couple of links to articles I
found on the net:
Generic Design of Web-Based Clinical Databases
http://www.jmir.org/2003/4/e27*/
The EAV/CR Model of Data Representation
http://ycmi.med.yale.edu/nadka*rni/eav_CR_contents.htm
An Introduction to Entity-Attribute-Value Design for Generic
Clinical Study Data Management Systems
http://ycmi.med.yale.edu/nadka*rni/...%20*systems.htm
Data Extraction and Ad Hoc Query of an Entity- Attribute- Value
Database
http://www.pubmedcentral.nih.g*ov/a...l=pub*med&pubme...
Exploring Performance Issues for a Clinical Database Organized Using
an Entity-Attribute-Value Representation
http://www.pubmedcentral.nih.g*ov/a...l=pub*med&pubme...|||(mittal.pradeep@.gmail.com) writes:
> Thanks a lot for the reply.
> 1. I am not able to understand why i require a 10 way join though. If i
> have a mapping between custom column and actual column, all i need is a
> dyanamic sql generated from the mapping.
> E.g.
> table
> --
> pk, custom1, custom2, custom3...custom10
>
> map
> --
> customColName ActualColName
> custom1 ActualName1
> custom2 ActualName2
> ...
> Now I can generate dynamic sql using map.
When I said 10-way join I was thinking of the vertical solution. For the
horisontal solution it's a simple join - once you have gone through all
that SQL building. To me, this sounds more complex to implement. Then
again, if the user selects dynamically which columns he wants to see,
the horizontal solution would require dynamic SQL as well.
> 2. As long as number of custom columns is enough to take care data
> element additions which happen in a year. All that is needed is
> addition of new elements to mapping table to decifer newly assigned
> custom columns
I didn't realise that you had this mapping table. One could say that
this is a kind of compromise between the horizonal model and an entirely
static vertical model.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||CELKO,
If I change the data elements 10 times during the year, I cannot change
the front end 10 to accomodate the same. I need to come up with a
dyanamic solution to take care of same. Vertical approach is the best
way to get the same. Using custom columns is a compromise, as vertical
approach is harder to implement.
--CELKO-- wrote:
> Neither. The design flaw you are calling a vertical model is actually
> known as "EAV" or "Entity-Attribute-Value" because it is a common
> newbie mistake. I have no idea what your #2 means.
> As your data elements change, you need to re-design the schema --
> constraints, keys, data types, etc. Learn RDBMS and do it right.
> I found an old "cut & paste". Someone like you posted this:
> CREATE TABLE EAV -- no key declared
> (key_col VARCHAR (10) NULL,
> attrib_value VARCHAR (50) NULL);
> INSERT INTO EAV VALUES ('LOCATION','Bedroom');
> INSERT INTO EAV VALUES ('LOCATION','Dining Room');
> INSERT INTO EAV VALUES ('LOCATION','Bathroom');
> INSERT INTO EAV VALUES ('LOCATION','courtyard');
> INSERT INTO EAV VALUES ('EVENT','verbal aggression');
> INSERT INTO EAV VALUES ('EVENT','peer');
> INSERT INTO EAV VALUES ('EVENT','bad behavior');
> INSERT INTO EAV VALUES ('EVENT','other');
> CREATE TABLE EAV_DATA -note lack of constraints, defaults, DRI
> (id INTEGER IDENTITY (1,1) NOT NULL,
> bts_id INTEGER NULL,
> key_col VARCHAR (10) NULL,
> attrib_value VARCHAR (50) NULL );
> INSERT INTO EAV_DATA VALUES (1, 'LOCATION', 'Bedroom');
> INSERT INTO EAV_DATA VALUES (1, 'EVENT', 'other');
> INSERT INTO EAV_DATA VALUES (1, 'EVENT', 'bad behavior');
> INSERT INTO EAV_DATA VALUES (2, 'LOCATION', 'Bedroom');
> INSERT INTO EAV_DATA VALUES (2, 'EVENT', 'other');
> INSERT INTO EAV_DATA VALUES (2, 'EVENT', 'verbal aggression');
> INSERT INTO EAV_DATA VALUES (3, 'LOCATION', 'courtyard');
> INSERT INTO EAV_DATA VALUES (3, 'EVENT', 'other');
> INSERT INTO EAV_DATA VALUES (3, 'EVENT', 'peer');
> Ideally, the result set of the query would be Location Event count
> (headings if possible)
> Bedroom verbal aggression 1
> Bedroom peer 0
> Bedroom bad behavior 0
> Bedroom other 2
> Dining Room verbal aggression 0
> Dining Room peer 0
> Dining Room bad behavior 0
> Dining Room other 0
> Bathroom verbal aggression 0
> Bathroom peer 0
> Bathroom bad behavior 0
> Bathroom other 0
> courtyard verbal aggression 0
> courtyard peer 1
> courtyard bad behavior 0
> courtyard other 1
> Also, if possible, another query would return this result set. (I think
> I know how to do this one.)
> Location Event count
> Bedroom verbal aggression 1
> Bedroom other 2
> courtyard peer 1
> courtyard other 1
> Here is a From: Thomas Coleman
> SELECT Locations.locationvalue, Events.eventvalue,
> (SELECT COUNT(*)
> FROM (SELECT LocationData.locationvalue, EventData.eventvalue
> FROM (SELECT TD1.bts_id, TD1.value AS locationvalue
> FROM eav_data AS TD1
> WHERE TD1.key = 'location') AS LocationData
> INNER JOIN
> (SELECT TD2.bts_id, TD2.value AS eventvalue
> FROM eav_data AS TD2
> WHERE TD2.key = 'event'
> ) AS EventData
> ON LocationData.bts_id = EventData.bts_id
> ) AS CollatedEventData
> WHERE CollatedEventData.locationvalue = Locations.locationvalue
> AND CollatedEventData.eventvalue = Events.eventvalue
> FROM (SELECT T1.value AS locationvalue
> FROM EAV AS T1
> WHERE T1.key = 'location') AS Locations,
> (SELECT T2.value AS eventvalue
> FROM EAV AS T2
> WHERE T2.key = 'event') AS Events
> ORDER BY Locations.locationvalue, Events.eventvalue ,
> SELECT Locations.locationvalue, Events.eventvalue
> (SELECT COUNT(*)
> FROM (SELECT LocationData.locationvalue, EventData.eventvalue
> FROM (SELECT TD1.bts_id, TD1.value AS locationvalue
> FROM eav_data AS TD1
> WHERE TD1.key = 'location') AS LocationData
> INNER JOIN
> (SELECT TD2.bts_id, TD2.value AS eventvalue
> FROM eav_data AS TD2
> WHERE TD2.key = 'event') AS EventData
> ON LocationData.bts_id = EventData.bts_id)
> AS CollatedEventData
> WHERE CollatedEventData.locationvalue = Locations.locationvalue
> AND CollatedEventData.eventvalue = Events.eventvalue)
> FROM (SELECT T1.value AS locationvalue
> FROM EAV AS T1
> WHERE T1.key = 'location') AS Locations,
> (SELECT T2.value AS eventvalue
> FROM EAV AS T2
> WHERE T2.key = 'event') AS Events;
> Is the same thing in a proper schema as:
> SELECT L.locationvalue, E.eventvalue, COUNT(*)
> FROM Locations AS L, Events AS E
> WHERE L.btd_id = E.btd_id
> GROUP BY L.locationvalue, E.eventvalue;
> The reason that I had to use so many subqueries is that those entities
> are all lopped into the same table. There should be separate tables for
> Locations and Events.
> The column names are seriously painful. Beyond the fact that I
> personally hate underscores in column names, using underscores at the
> end of the column name is really non-intuitive. I removed them for my
> example and came across the next column name faux pas. Don't use "key"
> and "value" for column names. It means that the developer *has*
> surround the column name with square brackets for everything which is a
> serious pain.
> There is such a thing as "too" generic. There has to be some structure
> or everything becomes nothing more than a couple of tables called
> "things". The real key (no pun intended) is commonality. Is there a
> pattern to the data that they want to store? It may not be possible to
> create one structure to rule them all and in the darkness bind them.
> "To be is to be something in particular; to be nothing in particular is
> to be nothing." --Aristole
> All data integrity is destroyed. Any typo becomes a new attribute or
> entity. Entities are found missing attributes, so all the reports are
> wrong.
> ry to write a single CHECK() constraint that works for all the
> attributes of those 30+ entities your users created because you were
> too dumb or too lazy to do your job. It can be done! You need a case
> expression almost 70 WHEN clauses for a simple invoice and order system
> when I tried it as an exercise.
> ry to write a single DEFAULT clause for 30+ entities crammed into one
> column. Impossible!
> Try to set up DRI actions among the entities. If you thought the WHEN
> clauses in the single CASE expression were unmaintainable, wait until
> you see the "TRIGGERs from Hell" -- Too bad that they might not fit
> into older SQL Server which had some size limits. Now maintain it.
> For those who are interested, there are couple of links to articles I
> found on the net:
> Generic Design of Web-Based Clinical Databases
> http://www.jmir.org/2003/4/e27*/
> The EAV/CR Model of Data Representation
> http://ycmi.med.yale.edu/nadka*rni/eav_CR_contents.htm
> An Introduction to Entity-Attribute-Value Design for Generic
> Clinical Study Data Management Systems
> http://ycmi.med.yale.edu/nadka*rni/...20*systems..htm
>
> Data Extraction and Ad Hoc Query of an Entity- Attribute- Value
> Database
> http://www.pubmedcentral.nih.g*ov/a...l=pub*med&pubme...
>
> Exploring Performance Issues for a Clinical Database Organized Using
> an Entity-Attribute-Value Representation
> http://www.pubmedcentral.nih.g*ov/a...l=pub*med&pubme...
Dynamic Data
Thanks for reading this post.
We have an application that can import data from various sources
containined in various formats. This data is to be stored in one table!
How do we handle this scenario? The only way I could think of is to
have a generic table with columns called columnName, stringValue and
numericValue and store every field in each row of the input data as a
row in the generic table.
Is this good design or is there is a better to handle the problem
please.
Thanks...Hi,John
When you say > We have an application that can import data from various
sources
> containined in various formats? do you mean that you get the data from
> TEXT files ,Access Database ,Excel documents?
"John Smith" <postmaster@.sumanthcp.plus.com> wrote in message
news:1138098782.505092.292110@.g44g2000cwa.googlegroups.com...
> Thanks for reading this post.
> We have an application that can import data from various sources
> containined in various formats. This data is to be stored in one table!
> How do we handle this scenario? The only way I could think of is to
> have a generic table with columns called columnName, stringValue and
> numericValue and store every field in each row of the input data as a
> row in the generic table.
> Is this good design or is there is a better to handle the problem
> please.
>
> Thanks...
>|||Hi Uri,
Yes, we get data in different physical formats but the data we get can
have diferent schema/columns. We import everything into SQLSERVER and
use the imported data for reporting. If we recoginse some columns then
they will be used to produce some hash totals. I hope I have given you
the picture.
Thanks|||John Smith wrote:
> Thanks for reading this post.
> We have an application that can import data from various sources
> containined in various formats. This data is to be stored in one table!
> How do we handle this scenario? The only way I could think of is to
> have a generic table with columns called columnName, stringValue and
> numericValue and store every field in each row of the input data as a
> row in the generic table.
> Is this good design or is there is a better to handle the problem
> please.
>
> Thanks...
The external format shouldn't determine your database design. Design
the correct logical data model first. Then worry about how to get the
data into it.
Since you have given us zero information about the data you are
modelling I can't help you with the design. Anyway, newsgroups are not
the place to solve design problems. Good design requires more knowledge
about your business and requirements than we can reasonably expect to
learn through an online discussion.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||John Smith (postmaster@.sumanthcp.plus.com) writes:
> Thanks for reading this post.
> We have an application that can import data from various sources
> containined in various formats. This data is to be stored in one table!
> How do we handle this scenario? The only way I could think of is to
> have a generic table with columns called columnName, stringValue and
> numericValue and store every field in each row of the input data as a
> row in the generic table.
> Is this good design or is there is a better to handle the problem
> please.
Whether this a good design or not depends on what you will use the data
for. Without further knowledge, I would not put much faith in this
solution though. And you did mention reporting - that reporting will
not be any fun.
Probably you need to conduct further analysis of the data import, so you
can define more tables.
As for stringValue, numericValue etc, you may want to investigate the
type sql_variant, that can host any other datatype in SQL Server, save
text, ntext and image.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The real question is - what are you doing with this data. When you have the
answer to that, you'll have the fundamentals of your logical model. Which
still has little to do with actually creating tables.
So, please tell us what this data is for (on your end) and we can guide you
to a solution. I can't imagine anyone just coming up with a solution based o
n
your post.
ML
http://milambda.blogspot.com/|||Look up various ETL tools for this kind of problem. They will do the
data scrubbing and format conversions. I would take a look at
Sunopsis.
numericValue and store every field [sic] in each row of the input data
as a row in the generic table. <<
There is no such animal as a "generic table" in a properly designed
RDBMS. Tables model one and only kind of entity and all the attributes
are clearly defined. To be is to be something in particular; to be
nothing in particular or everything in general is to be nothing at all
-- Aristotle.
The design flaw you have re-discovered is called EAV and you can
Google it.|||Thanks to all for your answers,
In fact we are trying to develop a tool which is capable of ELT and
that's why we need a central table to hold input data temporarily
before transforming it to create files/other databases.
The full picture is as follows: Our application will recieve
files/databases from external sources. The data we receive will have
some known attributes like for example - Quantity, Name, Cost etc. but
there may be more information (very likely) and we will not know what
those extra columns would be. We are planning to create two tables -
Core and Extra. Core will contain all the columns we know before hand
and will be used to run queries/reports (like checking hash values etc)
and Extra table which will be a EAV table used only during tranforming
data to other formats (formats are dynamic - users define them).
Is there an alternative elegant way of solving our problem or do we
have to live with the EAV flaw?|||John Smith wrote:
> Thanks to all for your answers,
> In fact we are trying to develop a tool which is capable of ELT and
> that's why we need a central table to hold input data temporarily
> before transforming it to create files/other databases.
> The full picture is as follows: Our application will recieve
> files/databases from external sources. The data we receive will have
> some known attributes like for example - Quantity, Name, Cost etc. but
> there may be more information (very likely) and we will not know what
> those extra columns would be. We are planning to create two tables -
> Core and Extra. Core will contain all the columns we know before hand
> and will be used to run queries/reports (like checking hash values etc)
> and Extra table which will be a EAV table used only during tranforming
> data to other formats (formats are dynamic - users define them).
> Is there an alternative elegant way of solving our problem or do we
> have to live with the EAV flaw?
I would echo Joe's suggestion that you consider off-the-shelf data
integration tools. Yours is precisely the type of application where
those packages have benefits over and above hand-coding your own
transformations. Specifically, you have changing metadata and so your
transformations won't be static. Therefore having a separate metadata
repository can work to your advantage because the tool will usually
take away some of the pain of generating the changing transformations.
As regards EAV I'd say that it won't sove your problem. There is no
substitute for developing properly normalized schema and EAV isn't
flexible enough to represent all non-relational data sources. How will
you model a XML hierarchy with EAV for example? Again, integration
tools can help because they will automate or semi-automate the process
of deriving a relational schema from the source. Also, bear in mind
that many of those tools expose their own API sothey are potential
still extensible with your own code.
Microsoft SQL Server Integration Services (SSIS)
www.microsoft.com/sql/technologies/...on/default.mspx
www.sqlis.com
Microsoft Data Transformation Services (DTS - the predecessor of SSIS)
www.sqldts.com
Other integration software
www-306.ibm.com/software/data/integration/dis/
www.abinitio.com
www.datamirror.com
www.datawatch.com
www.embarcadero.com/products/dtstudio/index.html
www.informatica.com
www.pervasive.com/solutions/
www.microsoft.com/biztalk/default.mspx
Enterprise Integration Patterns
www.enterpriseintegrationpatterns.com
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
We have an application that can import data from various sources
containined in various formats. This data is to be stored in one table!
How do we handle this scenario? The only way I could think of is to
have a generic table with columns called columnName, stringValue and
numericValue and store every field in each row of the input data as a
row in the generic table.
Is this good design or is there is a better to handle the problem
please.
Thanks...Hi,John
When you say > We have an application that can import data from various
sources
> containined in various formats? do you mean that you get the data from
> TEXT files ,Access Database ,Excel documents?
"John Smith" <postmaster@.sumanthcp.plus.com> wrote in message
news:1138098782.505092.292110@.g44g2000cwa.googlegroups.com...
> Thanks for reading this post.
> We have an application that can import data from various sources
> containined in various formats. This data is to be stored in one table!
> How do we handle this scenario? The only way I could think of is to
> have a generic table with columns called columnName, stringValue and
> numericValue and store every field in each row of the input data as a
> row in the generic table.
> Is this good design or is there is a better to handle the problem
> please.
>
> Thanks...
>|||Hi Uri,
Yes, we get data in different physical formats but the data we get can
have diferent schema/columns. We import everything into SQLSERVER and
use the imported data for reporting. If we recoginse some columns then
they will be used to produce some hash totals. I hope I have given you
the picture.
Thanks|||John Smith wrote:
> Thanks for reading this post.
> We have an application that can import data from various sources
> containined in various formats. This data is to be stored in one table!
> How do we handle this scenario? The only way I could think of is to
> have a generic table with columns called columnName, stringValue and
> numericValue and store every field in each row of the input data as a
> row in the generic table.
> Is this good design or is there is a better to handle the problem
> please.
>
> Thanks...
The external format shouldn't determine your database design. Design
the correct logical data model first. Then worry about how to get the
data into it.
Since you have given us zero information about the data you are
modelling I can't help you with the design. Anyway, newsgroups are not
the place to solve design problems. Good design requires more knowledge
about your business and requirements than we can reasonably expect to
learn through an online discussion.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||John Smith (postmaster@.sumanthcp.plus.com) writes:
> Thanks for reading this post.
> We have an application that can import data from various sources
> containined in various formats. This data is to be stored in one table!
> How do we handle this scenario? The only way I could think of is to
> have a generic table with columns called columnName, stringValue and
> numericValue and store every field in each row of the input data as a
> row in the generic table.
> Is this good design or is there is a better to handle the problem
> please.
Whether this a good design or not depends on what you will use the data
for. Without further knowledge, I would not put much faith in this
solution though. And you did mention reporting - that reporting will
not be any fun.
Probably you need to conduct further analysis of the data import, so you
can define more tables.
As for stringValue, numericValue etc, you may want to investigate the
type sql_variant, that can host any other datatype in SQL Server, save
text, ntext and image.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The real question is - what are you doing with this data. When you have the
answer to that, you'll have the fundamentals of your logical model. Which
still has little to do with actually creating tables.
So, please tell us what this data is for (on your end) and we can guide you
to a solution. I can't imagine anyone just coming up with a solution based o
n
your post.
ML
http://milambda.blogspot.com/|||Look up various ETL tools for this kind of problem. They will do the
data scrubbing and format conversions. I would take a look at
Sunopsis.
numericValue and store every field [sic] in each row of the input data
as a row in the generic table. <<
There is no such animal as a "generic table" in a properly designed
RDBMS. Tables model one and only kind of entity and all the attributes
are clearly defined. To be is to be something in particular; to be
nothing in particular or everything in general is to be nothing at all
-- Aristotle.
The design flaw you have re-discovered is called EAV and you can
Google it.|||Thanks to all for your answers,
In fact we are trying to develop a tool which is capable of ELT and
that's why we need a central table to hold input data temporarily
before transforming it to create files/other databases.
The full picture is as follows: Our application will recieve
files/databases from external sources. The data we receive will have
some known attributes like for example - Quantity, Name, Cost etc. but
there may be more information (very likely) and we will not know what
those extra columns would be. We are planning to create two tables -
Core and Extra. Core will contain all the columns we know before hand
and will be used to run queries/reports (like checking hash values etc)
and Extra table which will be a EAV table used only during tranforming
data to other formats (formats are dynamic - users define them).
Is there an alternative elegant way of solving our problem or do we
have to live with the EAV flaw?|||John Smith wrote:
> Thanks to all for your answers,
> In fact we are trying to develop a tool which is capable of ELT and
> that's why we need a central table to hold input data temporarily
> before transforming it to create files/other databases.
> The full picture is as follows: Our application will recieve
> files/databases from external sources. The data we receive will have
> some known attributes like for example - Quantity, Name, Cost etc. but
> there may be more information (very likely) and we will not know what
> those extra columns would be. We are planning to create two tables -
> Core and Extra. Core will contain all the columns we know before hand
> and will be used to run queries/reports (like checking hash values etc)
> and Extra table which will be a EAV table used only during tranforming
> data to other formats (formats are dynamic - users define them).
> Is there an alternative elegant way of solving our problem or do we
> have to live with the EAV flaw?
I would echo Joe's suggestion that you consider off-the-shelf data
integration tools. Yours is precisely the type of application where
those packages have benefits over and above hand-coding your own
transformations. Specifically, you have changing metadata and so your
transformations won't be static. Therefore having a separate metadata
repository can work to your advantage because the tool will usually
take away some of the pain of generating the changing transformations.
As regards EAV I'd say that it won't sove your problem. There is no
substitute for developing properly normalized schema and EAV isn't
flexible enough to represent all non-relational data sources. How will
you model a XML hierarchy with EAV for example? Again, integration
tools can help because they will automate or semi-automate the process
of deriving a relational schema from the source. Also, bear in mind
that many of those tools expose their own API sothey are potential
still extensible with your own code.
Microsoft SQL Server Integration Services (SSIS)
www.microsoft.com/sql/technologies/...on/default.mspx
www.sqlis.com
Microsoft Data Transformation Services (DTS - the predecessor of SSIS)
www.sqldts.com
Other integration software
www-306.ibm.com/software/data/integration/dis/
www.abinitio.com
www.datamirror.com
www.datawatch.com
www.embarcadero.com/products/dtstudio/index.html
www.informatica.com
www.pervasive.com/solutions/
www.microsoft.com/biztalk/default.mspx
Enterprise Integration Patterns
www.enterpriseintegrationpatterns.com
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Friday, February 24, 2012
Dynamic Connection String For ReportDatasource
Hi,
Is there any way to Dynamically change(Based on User login) Connection String for a report data source from code behind. In My application each user may have different data base. I am using a single shared data source for all the report. Please give me a solution.
Thanks
Sonu
You can use a report parameter and have the user choose from a list of different datasources and then you can use an expression for the connection string using the value of this report parameter.
Hope this helps.
|||Hi,
Since I am using shared data source for reports I cannot write expression for connection string. So I cannot use parameter.
Thanks
Sonu
Dynamic Connection string (config file)
Hi,
I have a web application (.NET 2005), which reads data from a multi-company database either SQL 2005 or Oracle. The company_id and connection string and other information is specified through a config file. I need to display the report from the click on a menu item.
The developers design the reports using Report Designer and store the RDL file in a designated folder (configured in the config file of the website). On the click of the menu the page (.aspx) page accepts values for the parameters of the report.
1. In this scenario, can these parameters, company_id (from config file) be passed to the report (which is already designed using Report Designer) and the RDL is already generated ?
If yes, then I can programmatically publish the data source (connection string from config file) and the report (from RDL file) onto the Report server and open the report using URL re-direction and display.
If no, then do I need to edit the RDL content programmatically to include the connection string and values for the parameters etc. I mean, the developer must require the connection string and company_id and other information to design the report. Can the developer user the config file to read the information and use in the report design.
2. The report parameters can also be specified in the Report designer. Is there any performance benefit of using this method ?
Sorry for the long post, but required to explain the scenario.
Thanks in advance.There are multiple options to achieve dynamic connection strings. Below is a description of available options.
In your case, on way of doing it is to use the new VS 2005 report viewer controls (particularly the Webforms control), read the parameter from the config file and pass it on to the report viewer control which renderers the report in remote mode on a report server and displays the output locally (see http://www.gotreportviewer.com/).
For the data source connection string and command text you could use expressions to dynamically construct these from the parameters (see the bottom of this posting for a simple example).
General approaches for dynamic database connections in RS 2000 are available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server; please check BOL for more details
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.
In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" & Parameters!ServerName.Value & ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>
You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx
-- Robert
|||dynamic variable sql database Connection String
this is work easy
http://www.codeproject.com/useritems/PersistConnectionStrings.asp
Dynamic Connection string (config file)
Hi,
I have a web application (.NET 2005), which reads data from a multi-company database either SQL 2005 or Oracle. The company_id and connection string and other information is specified through a config file. I need to display the report from the click on a menu item.
The developers design the reports using Report Designer and store the RDL file in a designated folder (configured in the config file of the website). On the click of the menu the page (.aspx) page accepts values for the parameters of the report.
1. In this scenario, can these parameters, company_id (from config file) be passed to the report (which is already designed using Report Designer) and the RDL is already generated ?
If yes, then I can programmatically publish the data source (connection string from config file) and the report (from RDL file) onto the Report server and open the report using URL re-direction and display.
If no, then do I need to edit the RDL content programmatically to include the connection string and values for the parameters etc. I mean, the developer must require the connection string and company_id and other information to design the report. Can the developer user the config file to read the information and use in the report design.
2. The report parameters can also be specified in the Report designer. Is there any performance benefit of using this method ?
Sorry for the long post, but required to explain the scenario.
Thanks in advance.There are multiple options to achieve dynamic connection strings. Below is a description of available options.
In your case, on way of doing it is to use the new VS 2005 report viewer controls (particularly the Webforms control), read the parameter from the config file and pass it on to the report viewer control which renderers the report in remote mode on a report server and displays the output locally (see http://www.gotreportviewer.com/).
For the data source connection string and command text you could use expressions to dynamically construct these from the parameters (see the bottom of this posting for a simple example).
General approaches for dynamic database connections in RS 2000 are available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server; please check BOL for more details
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.
In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" & Parameters!ServerName.Value & ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>
You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx
-- Robert
|||dynamic variable sql database Connection String
this is work easy
http://www.codeproject.com/useritems/PersistConnectionStrings.asp
Dynamic connect...?
I am trying to implement a web application user login system where every user is an Oracle user, so I can avoid having tables containing passwords and what not. In fact, having passwords in a table is not an option, even if they're encrypted. Anyway, I'm trying to set it up so that the login page is under a DAD that logs in as a user with rights to the login package only. Then, once the user has typed in their name and password and submitted, I want to then log them in as their user that has already been created in Oracle.
The first part is easy enough, but I have tried unsuccessfully to find some way to use dynamic sql to change users, such as EXECUTE IMMEDIATE 'CONNECT user/pass@.db'; and concatenating the appropriate values, but nothing seems to work.
I'm trying to avoid the basic authentication dialog box, as well as avoiding storing passwords in tables. I have looked into the custom authorization stuff provided by owa_custom, but I can't see any way to implement it with Oracle users. Any help on this would be greatly appreciated. Thanks!"connect" is a SQL*Plus command and hence cannot be executed dynamically. Other examples would be "show user", "desc table" etc. Only sql commands can be executed using dynamic sql.
But if you have the uid & pwd, can you not connect from your web application to see if the user is a valid database user or not ?|||Yeah, that is an option. I'm trying to avoid using JDBC or anything like that. However, if need be, I suppose that is something I can try. If there are any other ways to connect from the app, I would be interested in hearing about them. My experience with web application login systems is extremely limited, so any kind of help is greatly appreciated.
The first part is easy enough, but I have tried unsuccessfully to find some way to use dynamic sql to change users, such as EXECUTE IMMEDIATE 'CONNECT user/pass@.db'; and concatenating the appropriate values, but nothing seems to work.
I'm trying to avoid the basic authentication dialog box, as well as avoiding storing passwords in tables. I have looked into the custom authorization stuff provided by owa_custom, but I can't see any way to implement it with Oracle users. Any help on this would be greatly appreciated. Thanks!"connect" is a SQL*Plus command and hence cannot be executed dynamically. Other examples would be "show user", "desc table" etc. Only sql commands can be executed using dynamic sql.
But if you have the uid & pwd, can you not connect from your web application to see if the user is a valid database user or not ?|||Yeah, that is an option. I'm trying to avoid using JDBC or anything like that. However, if need be, I suppose that is something I can try. If there are any other ways to connect from the app, I would be interested in hearing about them. My experience with web application login systems is extremely limited, so any kind of help is greatly appreciated.
Subscribe to:
Posts (Atom)