Showing posts with label custom. Show all posts
Showing posts with label custom. Show all posts

Friday, March 30, 2012

Need Help Authentication loses HttpContext?

We have both a custom data extension and a custom authentication extension for Reporting Services 2000.

Reports with one dataset work great, but reports with more than one dataset fail because the Authentication extension call GetUserInfo no longer has an HttpContext when the second dataset calls into it.

I've seen several people post this same problem on other forums but no answers. Documentation of an msdn article states "the security extension will always be loaded in the Report Server app domain" so I don't understand how we could lose the context.

Additionally, ALL samples of an authentication extension I've looked at have GetUserInfo coded the same way:

Code Snippet

Public Sub GetUserInfo(ByRef userIdentity As System.Security.Principal.IIdentity, ByRef userId As System.IntPtr) Implements Microsoft.ReportingServices.Interfaces.IAuthenticationExtension.GetUserInfo

If Not System.Web.HttpContext.Current Is Nothing _

AndAlso Not System.Web.HttpContext.Current.User Is Nothing Then

userIdentity = System.Web.HttpContext.Current.User.Identity

Else

userIdentity = Nothing

End If

userId = IntPtr.Zero

End Sub

Any help or insights would be GREATLY appreciated - thanks, Ken.

OK, Wanted to share what I have found after opening a ticket with MS.

What we wanted to accomplish:
To dynamically change our connection string per userID so we could hit multiple instances of the same database structure. We did this by using the UserID stored in the HTTPContext. However, multiple datasets in a report caused Reportings Services to spawn new threads to access the data and RS did not persist the HTTPContext to these threads.

The GetUserInfo problem was a red herring- this did indeed always have access to the HTTPContext which was part of our Authentication extension. The Data extension was the real problem as we didn't have the HTTPContext during creation of our Connection object. What we found out from MS is that Reporting Services doesn't guarantee calls to be made on the asp.net worker thread and indeed in some instances even reports with one dataset failed.

Workaround:

Reports build with RS have access to a global variable UserID - this is actually retrieved from either WindowsIndentity or FormsIdentity based on authentication. Thus I added a report parameter UserID with a non-queried default value of =User!UserID to each report, and then created a parameter @.UserID for every dataset with the value of =Parameters!UserID.Value.

In the connection object if I don't have my HTTPContext, I default to a known database so the code doesn't fail. Then in the Command object's execute reader, I loop through the parameters until I find @.UserID. I now can recreate my connection object based on the UserID and set this connection as my Command's connection and return the datareader from the correct database. I make sure the connection is closed by always specifying the CloseConnection behavior during the datareader creation.

Whew!!! A bit cheesy, but it is allowing us to do what we need. Hopefully this will help anyone else that needs it.

Need Help Authentication loses HttpContext?

We have both a custom data extension and a custom authentication extension for Reporting Services 2000.

Reports with one dataset work great, but reports with more than one dataset fail because the Authentication extension call GetUserInfo no longer has an HttpContext when the second dataset calls into it.

I've seen several people post this same problem on other forums but no answers. Documentation of an msdn article states "the security extension will always be loaded in the Report Server app domain" so I don't understand how we could lose the context.

Additionally, ALL samples of an authentication extension I've looked at have GetUserInfo coded the same way:

Code Snippet

Public Sub GetUserInfo(ByRef userIdentity As System.Security.Principal.IIdentity, ByRef userId As System.IntPtr) Implements Microsoft.ReportingServices.Interfaces.IAuthenticationExtension.GetUserInfo

If Not System.Web.HttpContext.Current Is Nothing _

AndAlso Not System.Web.HttpContext.Current.User Is Nothing Then

userIdentity = System.Web.HttpContext.Current.User.Identity

Else

userIdentity = Nothing

End If

userId = IntPtr.Zero

End Sub

Any help or insights would be GREATLY appreciated - thanks, Ken.

OK, Wanted to share what I have found after opening a ticket with MS.

What we wanted to accomplish:
To dynamically change our connection string per userID so we could hit multiple instances of the same database structure. We did this by using the UserID stored in the HTTPContext. However, multiple datasets in a report caused Reportings Services to spawn new threads to access the data and RS did not persist the HTTPContext to these threads.

The GetUserInfo problem was a red herring- this did indeed always have access to the HTTPContext which was part of our Authentication extension. The Data extension was the real problem as we didn't have the HTTPContext during creation of our Connection object. What we found out from MS is that Reporting Services doesn't guarantee calls to be made on the asp.net worker thread and indeed in some instances even reports with one dataset failed.

Workaround:

Reports build with RS have access to a global variable UserID - this is actually retrieved from either WindowsIndentity or FormsIdentity based on authentication. Thus I added a report parameter UserID with a non-queried default value of =User!UserID to each report, and then created a parameter @.UserID for every dataset with the value of =Parameters!UserID.Value.

In the connection object if I don't have my HTTPContext, I default to a known database so the code doesn't fail. Then in the Command object's execute reader, I loop through the parameters until I find @.UserID. I now can recreate my connection object based on the UserID and set this connection as my Command's connection and return the datareader from the correct database. I make sure the connection is closed by always specifying the CloseConnection behavior during the datareader creation.

Whew!!! A bit cheesy, but it is allowing us to do what we need. Hopefully this will help anyone else that needs it.

Monday, March 19, 2012

Need an expert advice

Hi,
we have a legacy database on a UNIX box for our production system (OLTP).
This is the only database used for both OLTP and custom reporting. The
database is now hugh and reporting is very limited as well as slow
processing. When certain reports are run the users have latency in inputing
data.
I am thinking of importing some of the tables nightly to SQL Server and
then creating come cubes for reports. The issues I have are:
1. All of the tables in the UNIX box doesn't have timestamp so there is no
way of improting changed data and it database doesn't support replication so
I'll have to import the entire table(s) nightly. Would this affect my cubes?
2. Would it be better to import the data to a separate server (staging) and
then import the data from the second server to a third serer running MSAS?
3. One of the table is an Archive table where all settled sales are
transferred to, however, due to the nature of our business and the current
system design, some users go back and edit previous data so if i run a report
now (for let's say sept last year) I see sales as 2.00 dollars and later
today I see sales as 2.50. There is no way of knowing which row was changed.
So if I import the data to create a cube can I then snapshot that cube since
I'll be importing the antire "Archive" table nightly. I know it sounds
screwed up but it's what I have to work with. Or how can I solve this.
Thanks
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@.microsoft.com...
> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
> This is the only database used for both OLTP and custom reporting. The
> database is now hugh and reporting is very limited as well as slow
> processing. When certain reports are run the users have latency in
> inputing
> data.
>
Even with the reporting load moved to a different server, if the OLTP server
continues to accumulate massive amounts of transactions, the duration of
simple primary key lookups will eventually not meet the user's performance
expectations. In addition to replicating data for reporting purposes, you
may want to have a daily or monthly process that archives unneeded
historical transactions from the OLTP system to an Operational Data Store
(ODS) database (not necessarily a seperate server). If needed, you can still
give the operational applications query access to this data by implementing
partitioned views.
http://www.dmreview.com/article_sub.cfm?articleId=469
http://msdn.microsoft.com/library/de...es_06_17zr.asp

> I am thinking of importing some of the tables nightly to SQL Server and
> then creating come cubes for reports. The issues I have are:
> 1. All of the tables in the UNIX box doesn't have timestamp so there is no
> way of improting changed data and it database doesn't support replication
> so
> I'll have to import the entire table(s) nightly. Would this affect my
> cubes?
>
With no timestamp of any kind, then standard replication is not an option.
You can add a trigger to log insert / update / deletes on the OLTP system to
seperate tables (perhaps only storing IDs of rows that were modified), and
implement a DTS package that copies over the needed data based on this
information from this log.
http://www.microsoft.com/technet/pro.../c07ppcsq.mspx
http://msdn.microsoft.com/library/de...tbpwithdts.asp

> 2. Would it be better to import the data to a separate server (staging)
> and
> then import the data from the second server to a third serer running MSAS?
>
See the above reccomendation for an ODS database. For performance reasons,
it would be best to have this on a dedicated server, but it could be located
on the OLTP or MSAS server.

> 3. One of the table is an Archive table where all settled sales are
> transferred to, however, due to the nature of our business and the current
> system design, some users go back and edit previous data so if i run a
> report
> now (for let's say sept last year) I see sales as 2.00 dollars and later
> today I see sales as 2.50. There is no way of knowing which row was
> changed.
That's another reason for the logging trigger mentioned above; it also
audits changes on these OLTP tables. Also, useful in the log tables would be
a datetime column to keep track of when the changes took place.
http://msdn.microsoft.com/msdnmag/is...04/DataPoints/

> So if I import the data to create a cube can I then snapshot that cube
> since
> I'll be importing the antire "Archive" table nightly. I know it sounds
> screwed up but it's what I have to work with. Or how can I solve this.
The audit log tables on the OLTP system can keep a history of data
modifications to settled sales, and I assume this event would be occasional
and not too frequent. Also, the MSAS cubes can be archived (just like SQL
Server databases), things like Excel pivot tables can be archived, and
reports can be printed as archivable PDF documents.
http://www.microsoft.com/technet/pro.../anservog.mspx

> Thanks
|||what is the number of rows in the OLTP database?
what is your RDBMS on this server?
servers like Oracle can create a simple flatfile which contains all the
transaction made against a table;
there is no timestamp, but can you create triggers in your database?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@.microsoft.com...
> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
> This is the only database used for both OLTP and custom reporting. The
> database is now hugh and reporting is very limited as well as slow
> processing. When certain reports are run the users have latency in
> inputing
> data.
> I am thinking of importing some of the tables nightly to SQL Server and
> then creating come cubes for reports. The issues I have are:
> 1. All of the tables in the UNIX box doesn't have timestamp so there is no
> way of improting changed data and it database doesn't support replication
> so
> I'll have to import the entire table(s) nightly. Would this affect my
> cubes?
> 2. Would it be better to import the data to a separate server (staging)
> and
> then import the data from the second server to a third serer running MSAS?
> 3. One of the table is an Archive table where all settled sales are
> transferred to, however, due to the nature of our business and the current
> system design, some users go back and edit previous data so if i run a
> report
> now (for let's say sept last year) I see sales as 2.00 dollars and later
> today I see sales as 2.50. There is no way of knowing which row was
> changed.
> So if I import the data to create a cube can I then snapshot that cube
> since
> I'll be importing the antire "Archive" table nightly. I know it sounds
> screwed up but it's what I have to work with. Or how can I solve this.
> Thanks
|||Hi ?
(for some reason the first post is not here)

> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
> This is the only database used for both OLTP and custom reporting. The
> database is now hugh and reporting is very limited as well as slow
> processing. When certain reports are run the users have latency in
> inputing
> data.
Well, situation normal....starting point is to look at the business
from a business perspective and determine if there is value in going
the data warehouse route or if all that is warranted from a
profitability point of view is operational reporting on another
database image.
In most businesses today a data warehouse is well justified based on
profit contribution.
If you go the DW route you will need to get to incremental
extracts...if the operational system does not have the ability to
provide them (due to appaulingly limited design in this day and age)
then you must generate them. My company provides free tools to do this.
Then you are on the road to doing 'Business Intelligence' and there is
quite a learning curve. You can choose to 'go it alone' or hire in a
partner to assist. Of course, as a long term BI consultant I would
land on the side of hiring someone but many companies 'buy the books
and go it alone'... ;-)
I sincerely doubt the most profitable approach is getting one answer at
a time from a forum.
Please see my beginners page for the best books on the
subject...www.peternolan.com
Best Regards
Peter

Need an expert advice

Hi,
we have a legacy database on a UNIX box for our production system (OLTP).
This is the only database used for both OLTP and custom reporting. The
database is now hugh and reporting is very limited as well as slow
processing. When certain reports are run the users have latency in inputing
data.
I am thinking of importing some of the tables nightly to SQL Server and
then creating come cubes for reports. The issues I have are:
1. All of the tables in the UNIX box doesn't have timestamp so there is no
way of improting changed data and it database doesn't support replication so
I'll have to import the entire table(s) nightly. Would this affect my cubes?
2. Would it be better to import the data to a separate server (staging) and
then import the data from the second server to a third serer running MSAS?
3. One of the table is an Archive table where all settled sales are
transferred to, however, due to the nature of our business and the current
system design, some users go back and edit previous data so if i run a repor
t
now (for let's say sept last year) I see sales as 2.00 dollars and later
today I see sales as 2.50. There is no way of knowing which row was changed.
So if I import the data to create a cube can I then snapshot that cube since
I'll be importing the antire "Archive" table nightly. I know it sounds
screwed up but it's what I have to work with. Or how can I solve this.
Thanks"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@.microsoft.com...
> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
> This is the only database used for both OLTP and custom reporting. The
> database is now hugh and reporting is very limited as well as slow
> processing. When certain reports are run the users have latency in
> inputing
> data.
>
Even with the reporting load moved to a different server, if the OLTP server
continues to accumulate massive amounts of transactions, the duration of
simple primary key lookups will eventually not meet the user's performance
expectations. In addition to replicating data for reporting purposes, you
may want to have a daily or monthly process that archives unneeded
historical transactions from the OLTP system to an Operational Data Store
(ODS) database (not necessarily a seperate server). If needed, you can still
give the operational applications query access to this data by implementing
partitioned views.
http://www.dmreview.com/article_sub.cfm?articleId=469
http://msdn.microsoft.com/library/d...>
_06_17zr.asp

> I am thinking of importing some of the tables nightly to SQL Server and
> then creating come cubes for reports. The issues I have are:
> 1. All of the tables in the UNIX box doesn't have timestamp so there is no
> way of improting changed data and it database doesn't support replication
> so
> I'll have to import the entire table(s) nightly. Would this affect my
> cubes?
>
With no timestamp of any kind, then standard replication is not an option.
You can add a trigger to log insert / update / deletes on the OLTP system to
seperate tables (perhaps only storing IDs of rows that were modified), and
implement a DTS package that copies over the needed data based on this
information from this log.
http://www.microsoft.com/technet/pr...s/c07ppcsq.mspx
http://msdn.microsoft.com/library/d...ntbpwithdts.asp

> 2. Would it be better to import the data to a separate server (staging)
> and
> then import the data from the second server to a third serer running MSAS?
>
See the above reccomendation for an ODS database. For performance reasons,
it would be best to have this on a dedicated server, but it could be located
on the OLTP or MSAS server.

> 3. One of the table is an Archive table where all settled sales are
> transferred to, however, due to the nature of our business and the current
> system design, some users go back and edit previous data so if i run a
> report
> now (for let's say sept last year) I see sales as 2.00 dollars and later
> today I see sales as 2.50. There is no way of knowing which row was
> changed.
That's another reason for the logging trigger mentioned above; it also
audits changes on these OLTP tables. Also, useful in the log tables would be
a datetime column to keep track of when the changes took place.
http://msdn.microsoft.com/msdnmag/i.../04/DataPoints/

> So if I import the data to create a cube can I then snapshot that cube
> since
> I'll be importing the antire "Archive" table nightly. I know it sounds
> screwed up but it's what I have to work with. Or how can I solve this.
The audit log tables on the OLTP system can keep a history of data
modifications to settled sales, and I assume this event would be occasional
and not too frequent. Also, the MSAS cubes can be archived (just like SQL
Server databases), things like Excel pivot tables can be archived, and
reports can be printed as archivable PDF documents.
http://www.microsoft.com/technet/pr...n/anservog.mspx

> Thanks|||what is the number of rows in the OLTP database?
what is your RDBMS on this server?
servers like Oracle can create a simple flatfile which contains all the
transaction made against a table;
there is no timestamp, but can you create triggers in your database?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:12FD68B7-BD47-4C86-BA66-99BE1EDF9473@.microsoft.com...
> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
> This is the only database used for both OLTP and custom reporting. The
> database is now hugh and reporting is very limited as well as slow
> processing. When certain reports are run the users have latency in
> inputing
> data.
> I am thinking of importing some of the tables nightly to SQL Server and
> then creating come cubes for reports. The issues I have are:
> 1. All of the tables in the UNIX box doesn't have timestamp so there is no
> way of improting changed data and it database doesn't support replication
> so
> I'll have to import the entire table(s) nightly. Would this affect my
> cubes?
> 2. Would it be better to import the data to a separate server (staging)
> and
> then import the data from the second server to a third serer running MSAS?
> 3. One of the table is an Archive table where all settled sales are
> transferred to, however, due to the nature of our business and the current
> system design, some users go back and edit previous data so if i run a
> report
> now (for let's say sept last year) I see sales as 2.00 dollars and later
> today I see sales as 2.50. There is no way of knowing which row was
> changed.
> So if I import the data to create a cube can I then snapshot that cube
> since
> I'll be importing the antire "Archive" table nightly. I know it sounds
> screwed up but it's what I have to work with. Or how can I solve this.
> Thanks|||Hi ?
(for some reason the first post is not here)

> Hi,
> we have a legacy database on a UNIX box for our production system (OLTP).
> This is the only database used for both OLTP and custom reporting. The
> database is now hugh and reporting is very limited as well as slow
> processing. When certain reports are run the users have latency in
> inputing
> data.
Well, situation normal....starting point is to look at the business
from a business perspective and determine if there is value in going
the data warehouse route or if all that is warranted from a
profitability point of view is operational reporting on another
database image.
In most businesses today a data warehouse is well justified based on
profit contribution.
If you go the DW route you will need to get to incremental
extracts...if the operational system does not have the ability to
provide them (due to appaulingly limited design in this day and age)
then you must generate them. My company provides free tools to do this.
Then you are on the road to doing 'Business Intelligence' and there is
quite a learning curve. You can choose to 'go it alone' or hire in a
partner to assist. Of course, as a long term BI consultant I would
land on the side of hiring someone but many companies 'buy the books
and go it alone'... ;-)
I sincerely doubt the most profitable approach is getting one answer at
a time from a forum.
Please see my beginners page for the best books on the
subject...www.peternolan.com
Best Regards
Peter