We got many client requests for the ability to show in their web sites the "last updated" date of the data.
It doesn't matter how you show the data of the SSAS - the customers will always want to know for which date the data is true.
My solution includes a ASP.NET 2.0 web site that uses the AMO class libary. It takes the date from the server and shows it to the user.
What you need to do is:
1. Open a new ASP.NET web site using Visual Studio 2005/8.
2. Add the AMO dll (Microsoft.Analysis Services). You'll find it in the SSAS server.
3. In the already-made default.aspx page, just add one Label.
4. Add a configuration file which will hold the name of the SSAS server. That way, when you install the site from the development environment to the production environment, you'll only have to change this file. Call this file config.xml and write in it the following:
<?xml version="1.0" encoding="utf-8" ?>
<ServerName>YourServerFullNameHere</ServerName>
5. In the code-behind file (default.aspx.cs) write the following code instead of what you already have there:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using AMO = Microsoft.AnalysisServices;
using System.Xml;
public partial class _Default : System.Web.UI.Page
{
protected void Load_Page(Object sender, EventArgs e)
{
Label1.Text = GetCubeUpdateDate(Request.QueryString["DBName"],Request.QueryString["CubeId"]);
}
private string GetCubeUpdateDate (string dbName, string cubeId)
{
using (AMO.Server asServer = new AMO.Server())
{
asServer.Connect("Data Source=" + GetAnalysisServerName());
AMO.Database db = asServer.DataBases.FindByName(dbName);
if (db == null)
{
return "DB Name not found";
}
AMO.Cube cube = GetCubeById(cubeId, db);
if (cube == null)
{
return "Cube Name not found";
}
DateTime lastProcessed = cube.LastProcessed;
return lastProcessed.Day.ToString() + "/" + lastProcessed.Month.ToString() + "/" + lastProcessed.Year.ToString();
}
}
private string GetAnalysisServerName ()
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(Request.PhysicalApplicationPath + "config.xml");
return xmlDoc.GetElementsByTagName("ServerName").Item(0).InnetText;
}
private AMO.Cube GetCubeById (string cubeId, AMO.Database db)
{
foreach (AMO.Cube cube in db.Cubes)
{
if (cube.ID.Equals(cubeId))
{
return cube;
}
}
return null;
}
}
Eventhough the code is self-explained, here are some points referring it:
- I chose not to include the server name in the web.config file because I like to seperate application-related configuration and web configuration.
- If you want you can get the cube name from the user (in the query string) and then the code is even shorter - just get the cube like I got the database.
- I wanted to show the date in the format DD/MM/YYYY, so that's why I did the long return statement in the GetCubeUpdateDate method. If you want to return the date in the MM/DD/YYYY format you can use the lastProcessed.GetShortDateFormat() method.
- Note that when you publish the web site you need to create a dedicated virtual folder in the IIS.
- The user uses this site in the following way: All he need to do is to create a frame with this site's address as its source and add it the DBName & CubeId in the query string. In SharePoint it's even easier - the uses only need to create a page shower web part.
enjoy.