Put data refresh timestamp on MSTR webpages and reports

[26point2]

Has anyone tried to put a data refresh timestamp on their MSTR web pages, so that users know when the last time the database was loaded? I'm trying to insert a database call into the genericheader.asp page and placing the max(date_id) on the web page. I've been able to insert this field into a report, but we'd like to have it show under the company logo at login, so that folks don't have to execute a report to see this.

Here's the code I've added just past where you customize the logo in genericheader.asp (about 35 or so lines down):

dim oConn
set oConn=Server.CreateObject("ADODB.Connection&quot
oConn.open "DSN=min2"

sqltext = "select max(date_id) as mydate from load_check where load_flag =6"

oRS.Open sqltext, oConn

Response.Write oRS.field("mydate&quot


======
The code seems to compile okay, but doesn't return data. The System DSN name is "min2" on the web server machine. I'm using the same DNS that Intelligence Server uses to connect to the warehouse, using the MicroStrategy Oracle driver. Must I use Oracle's driver, instead? Or is there a better way to put this refresh timestamp onto the web pages?

I would imagine that a data refresh timestamp is a common request for users. I appreciate any insight you might have.

Thanks,
Dennis

 

[chael]

I haven't had much luck, nor have I tried very hard, to use MicroStrategy's drivers for ADO...we had a problem using it, so we quickly swapped it with the 8.1.7 drivers...

 

[nlim]

I'm going to be a naysayer and scream "no database connections on the web server! your user concurrency will suffer and hackers will grab your db data..."

ok, i got my pet peeve out. My constructive suggestion is that you update the project description field using the api. This allows the max(date_Id) to be shown on the projects page. It's more generic and will scale as you add more projects which point to different dbs. If you need to customize your pages, you can get the description field from the xml, so much better all round...IMHO.

 

[Golan]

We have accomplished this by creating a lookup table where we have columns such as project’s name, timestamp of loading, and some attributes of our time hierarchy. Then we created an attribute called ‘reference date’ in each project, and a filter with an applysimple clause that selects the correct row from the lookup table. Our ETL process updates the corresponding row in that lookup table at the end of the loading. The timestamp of last load appears as a page-by combo, but the only selection possible is, of course, the same date. Not every report use that, only those used with daily analysis (that is, no report with month analysis use the timestamp because it works with ‘closed’ months). And you can also use it for such things as ‘last 15 days’, ‘last 30 days’, etc.

Regards.

 

[26point2]

nlim and chael,

Thank you for your help! It's good to know there are experts who cover this board. Though hacking into the database through an asp page is ugly, we did get it to work through an OLE DB, rather than an ODBC connection (the MicroStrategy Oracle driver is problematic, as chael noted).

Now, if I can just define a session variable, so that it doesn't hit the database each time the page is loaded and keep a bunch of connections open, dragging down performance. Any sample code or pointers on how to do that?

My code below for folks who might be able to use it...
Insert your username, password,database name, SQL code, etc... where appropriate

Thanks,
26point2
==============================

dim objConn, objRs,sSql

set objConn = GetDBConnection_OLEDB("username", "password", "mydb.world&quot

sSql = "select max(date_id) from load_table where load_flag=6"

set objRs = objConn.Execute(sSql)

response.write &quot;<br>&quot; & &quot;Data last refreshed on &quot; & objRs(0) &&quot;<br>&quot;

function GetDBConnection_OLEDB(username, passwd, DSN)

if (DSN = &quot;&quot then
response.write (&quot;<H3>Session timed out... connecting to database</H3>&quot
Session(&quot;ConnectString&quot = DSN
end if

dim odbc, uid, pwd
dim objConn

on error resume next

uid = username
pwd = passwd

set objConn = Server.CreateObject(&quot;ADODB.Connection&quot

if IsObject(objConn) then
set GetDBConnection_OLEDB = objConn

end if

objConn.CommandTimeout=20 'TODO: 3600

objConn.Open &quot;provider=msdaora;&quot; &_
&quot;Data Source=&quot;&DSN&&quot;;&quot; &_
&quot;user ID=&quot;&uid&&quot;;&quot; &_
&quot;Password=&quot;&pwd&&quot;;&quot;


end function

 

[chael]

If you are going to go with this approach, you may want to have the OLEDB call happen only once, and have it happen on a page that doesn't get hit as often as report.asp or folder.asp -- perhaps you could create a function that is called after a successful login...or, you could even insert something into the global.asa file so that it only loads up once.

You have lots of options...another one would be to put the function call into the GenericHeader.asp and the function itself into GenericHeaderCuLib.asp and via the GenericHeader.asp page always ask:

[green]if len(session(&quot;timestamp&quot) = 0 then
call GetTimestampInfo(sTimeStamp)
session(&quot;timestamp&quot = sTimeStamp
end if[/green]

...or something like that.

Seems like you are pretty well set...