Hello,
Could I write ActiveX Script in a DTS Package to return cube info (such as CubeName, CubeStatus, CubeSize, CubePartitions, CubeLastProcessed)? If that's
possible, I could then pump in the result into text file and import them into
a table for reporting purposes.
Please let me know if this is the best route to explore DSO objects or any alternative way of extracting this type of Cube data.
-Lawrence
If you are using Analysis Services 2000, yes, using DSO from a DTS package is a good option.
If you are using Analysis Services 2005, then use AMO (Microsoft.AnalysisServices.dll), I can post sample code, let me know please.
Adrian Dumitrascu
|||Hello Adrian,
If you could post some sample DSO & AMO code for 2000 & 2005, that would be much appreciated. This would get me started as I don't know much about the object schema for DSO nor AMO. Let me know how to get to your code site.
Sincerely,
-Lawrence
|||Hi,
here is a sample - without any warranty. For more information see http://msdn2.microsoft.com/en-us/library/ms345089.aspx:
using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.IO;
using Microsoft.AnalysisServices;
namespace GetCubeInfo
{
class Program
{
static void Main(string[] args)
{
string dateFormat = "yyyy-MM-dd HH:mm:ss";
try
{
FileStream stream;
stream = File.Create(@."GetCubeInfo.xml");
XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
// Causes child elements to be indented
writer.Formatting = Formatting.Indented;
// Report element
writer.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"utf-8\"");
writer.WriteStartElement("Server");
// Connect to the SSAS server
Server server = new Server();
server.Connect(@."Integrated Security=SSPI;Persist Security Info=False;Data Source=localhost\YUKON");
writer.WriteAttributeString("Name", null, server.Name);
writer.WriteAttributeString("ReportCreated", null, DateTime.Now.ToString(dateFormat));
// Get the Adventure Works cube(s)
foreach (Database database in server.Databases)
{
writer.WriteStartElement("Database");
writer.WriteAttributeString("Name", null, database.Name);
writer.WriteAttributeString("CreatedTimestamp", null, database.CreatedTimestamp.ToString(dateFormat));
writer.WriteAttributeString("LastSchemaUpdate", null, database.LastSchemaUpdate.ToString(dateFormat));
writer.WriteAttributeString("LastProcessed", null, database.LastProcessed.ToString(dateFormat));
writer.WriteAttributeString("LastUpdate", null, database.LastUpdate.ToString(dateFormat));
foreach (Cube cube in database.Cubes)
{
writer.WriteStartElement("Cube");
writer.WriteAttributeString("Name", null, cube.Name);
writer.WriteAttributeString("CreatedTimestamp", null, cube.CreatedTimestamp.ToString(dateFormat));
writer.WriteAttributeString("LastSchemaUpdate", null, cube.LastSchemaUpdate.ToString(dateFormat));
writer.WriteAttributeString("LastProcessed", null, cube.LastProcessed.ToString(dateFormat));
writer.WriteEndElement(); // Cube
}
foreach (Dimension dimension in database.Dimensions)
{
writer.WriteStartElement("Dimension");
writer.WriteAttributeString("Name", null, dimension.Name);
writer.WriteAttributeString("CreatedTimestamp", null, dimension.CreatedTimestamp.ToString(dateFormat));
writer.WriteAttributeString("LastSchemaUpdate", null, dimension.LastSchemaUpdate.ToString(dateFormat));
writer.WriteAttributeString("LastProcessed", null, dimension.LastProcessed.ToString(dateFormat));
writer.WriteEndElement(); // Dimension
}
writer.WriteEndElement(); // Database
writer.Flush();
}
writer.WriteEndElement(); // Server
writer.Flush();
}
catch (Exception exception)
{
// Uups
Console.WriteLine(exception.Message);
}
}
}
}
|||Hello,
I really appreciate the AMO sample code! However, my cubes are currently in 2000, so I would wondering if someone could provide some sample DSO code like above. Output to text file would be good enough.
BTW, to setup automation for the AMO code above, would SSIS be the best place to setup the script? I am thinking either in "Script Component" or "ActiveX" tasks. Let me know if there is a better method.
Sincerely,
-Lawrence
|||Hi Lawrence,
I strongly recommend you to use the Script Component if you run this code within a SSIS package, because you can use Visual Basic.Net and can use directly the (managed) AMO interface. The ActiveX task is only for backward compatibility reasons. For new code you should use the new ones otherwise you will have migration efforts in the future.
Whether to put it into a SSIS Package or not depends on your requirements and what you want to do with the data you have just gathered in that task. If it is part of your workflow/ETL process, or if you want to use some of the results in other steps/flows, or if you want to use the configuration support of SSIS, or if you want to use the scheduling support with the SQL Server Agent, or, or, or then the answer will be easy (Yes).
Regards,
Bertil
|||Anyone could provide a sample script of the DSO objects that's related to what I am trying to gather here? I don't think AMO objects would work on 2000 cubes.
Your help is appreciated!
-Lawrence
