Download source code - 836 KB

Introduction

This article is about a way by which we can achieve writeback and writethrough on SSAS 2008 on the web following the below steps:

The user will do a forecasting for an item and it will be visible on the web.
The forecasted value will not be visible to anybody else until it is committed.
The committed (writethrough) item will be saved in a writeback table in SSAS and will be visible to all.
The rolled back item will not be saved to a writeback table and even not visible to the specific user.

Background

The basic idea presented here is how we can see changes done by a specific user which are not committed yet as we see in Excel.

Here the idea is to show the updated value (not saved on SSAS server) on the web page. For this specific purpose, an XML file can be created and the query for the update should be saved in this file whenever the web page is requested by the specific user. First of all it will check the file created for the user. If found, all the update queries will be executed and and then select queries for the page will be executed and shown on the web page.

Using the code

First of all, our connection string should be like below:

<add key="DBString" value="Data
Source=Your server Name;Integrated Security=SSPI;Initial Catalog=Your cube database
name;Disable Prefetch Facts=true;Cache Ratio=1"/>

The method to execute our MDX query and our write-back functionality:

public CellSet GetCellset(string strQuery, string strCubeName)
{
    CellSet cst = null;
    strCubeName = FCL.SqlConstants.cubeName;
    string cubeName = strCubeName;
    try
    {
        OpenConnection();
        string FileNameQuery = (@"~\Documents\") + 
          Environment.UserName + cubeName + "Query.xml";
        if (File.Exists(System.Web.HttpContext.Current.Server.MapPath(FileNameQuery)))
        {
            File.SetAttributes(System.Web.HttpContext.Current.Server.MapPath(
              @"~\Documents\" + Environment.UserName + cubeName + 
              "Query.xml"), FileAttributes.Normal);
            var xmlDoc = new XmlDocument();
            xmlDoc.Load(System.Web.HttpContext.Current.Server.MapPath(FileNameQuery));
            XmlElement xmlElement = xmlDoc.DocumentElement;

            XmlNodeList xnList = xmlElement.ChildNodes;
            for (int i = 0; i < xnList.Count; i++)
            {
                for (int j = 0; j < xnList[i].Attributes.Count; j++)
                {
                    try
                    {
                        string xmlQuery = xnList[i].Attributes[j].InnerText;
                        mAdomdCommand = new AdomdCommand(xmlQuery, (AdomdConnection)this.Connection);
                        mAdomdCommand.CommandType = CommandType.Text;
                        mAdomdCommand.CommandTimeout = 360000;
                        mAdomdCommand.ExecuteNonQuery();
                    }
                    catch (Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException error)
                    {
                        try
                        {
                            if (error.ErrorCode == -1054212081)
                            {
                                string xmlQuery = xnList[i].Attributes[j].InnerText.Replace(
                                  "USE_WEIGHTED_ALLOCATION", "USE_EQUAL_INCREMENT");
                                mAdomdCommand = new AdomdCommand(xmlQuery, (AdomdConnection)this.Connection);
                                mAdomdCommand.CommandType = CommandType.Text;
                                mAdomdCommand.CommandTimeout = 360000;
                                mAdomdCommand.ExecuteNonQuery();
                            }
                        }
                        
                        catch (Exception EX)
                        {
                            throw new Exception(EX.Message.ToString());
                        }
                    }
                }
            }
            try
            {
                string strNewCubeName = strCubeName;
                mAdomdCommand = new AdomdCommand(strQuery, (AdomdConnection)this.Connection);
                mAdomdCommand.CommandType = CommandType.Text;
                mAdomdCommand.CommandTimeout = 360000;
                cst = mAdomdCommand.ExecuteCellSet();
            }
            catch (Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException error)
            {
                try
                {
                    if (error.ErrorCode == -1054212081)
                    {
                        string strNewCubeName = strCubeName;
                        mAdomdCommand = new AdomdCommand(strQuery.Replace(
                          "USE_WEIGHTED_ALLOCATION", "USE_EQUAL_INCREMENT"), 
                          (AdomdConnection)this.Connection);
                        mAdomdCommand.CommandType = CommandType.Text;
                        mAdomdCommand.CommandTimeout = 360000;
                        cst = mAdomdCommand.ExecuteCellSet();
                    }
                }
                
                catch (Exception EX)
                {
                    throw new Exception(EX.Message.ToString());
                }
            }
       }
       else
       {
            try
            {
                mAdomdCommand = new AdomdCommand(strQuery, (AdomdConnection)this.Connection);
                mAdomdCommand.CommandType = CommandType.Text;
                mAdomdCommand.CommandTimeout = 360000;
                cst = mAdomdCommand.ExecuteCellSet();
            }
            catch (Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException error)
            {
                    if (error.ErrorCode == -1054212081)
                    {
                        mAdomdCommand = new AdomdCommand(strQuery.Replace("USE_WEIGHTED_ALLOCATION", 
                          "USE_EQUAL_INCREMENT"), (AdomdConnection)this.Connection);
                        mAdomdCommand.CommandType = CommandType.Text;
                        mAdomdCommand.CommandTimeout = 360000;
                        cst = mAdomdCommand.ExecuteCellSet();
                    }
           }
        }
    }
    catch (Exception EX)
    {
        throw new Exception(EX.Message);
    }
    finally
    {
        CloseConnection();
    }
    return cst;
}

Here "Documents" is a folder which is created for storing the user specific XML file.

The method to commit or write-through:

public bool CommitTransaction(bool status, string cubeName)
{
    bool transactionStatus = false;
    cubeName = FCL.SqlConstants.cubeName;
    using (TransactionScope scope = new TransactionScope())
    {
        OpenConnection();
        if (status)
        {
            try
            {
                string FileNameQuery = (@"~\Documents\") + 
                          Environment.UserName + cubeName + "Query.xml";
                try
                {
                    if (File.Exists(System.Web.HttpContext.Current.Server.MapPath(FileNameQuery)))
                    {
                        var xmlDoc = new XmlDocument();
                        xmlDoc.Load(System.Web.HttpContext.Current.Server.MapPath(FileNameQuery));
                        XmlElement xElement = xmlDoc.DocumentElement;
                        XmlNodeList xnList = xElement.ChildNodes;
                        for (int i = 0; i < xnList.Count; i++)
                        {
                            string strQuery = xnList[i].Attributes[0].InnerText;
                            AdomdConnection adomdConnection = new AdomdConnection((AdomdConnection)this.Connection);
                            adomdConnection.Open();
                            AdomdCommand adomdCommand = new AdomdCommand(strQuery, adomdConnection);
                            AdomdTransaction adomdTransaction = adomdConnection.BeginTransaction();
                            adomdCommand.CommandType = CommandType.Text;
                            adomdCommand.ExecuteNonQuery();
                                    
                            string strNextQuery = xnList[i].Attributes[1].InnerText;
                            AdomdConnection adomdNextConnection = new AdomdConnection((AdomdConnection)this.Connection);
                            adomdNextConnection.Open();
                            AdomdCommand adomdNextCommand = new AdomdCommand(strNextQuery, adomdNextConnection);
                            AdomdTransaction adomdNextTransaction = adomdNextConnection.BeginTransaction();
                            adomdNextCommand.CommandType = CommandType.Text;
                            adomdNextCommand.ExecuteNonQuery();

                            
                            adomdCommand = new AdomdCommand("Refresh Cube Sales", adomdConnection);
                            adomdCommand.CommandType = CommandType.Text;
                            adomdCommand.ExecuteNonQuery();
                            adomdTransaction.Commit();
                            adomdConnection.Close();

                            adomdNextCommand = new AdomdCommand("Refresh Cube Sales", adomdNextConnection);
                            adomdNextCommand.CommandType = CommandType.Text;
                            adomdNextCommand.ExecuteNonQuery();
                            adomdNextTransaction.Commit();
                            adomdNextConnection.Close();

                        }
                        File.Delete(System.Web.HttpContext.Current.Server.MapPath(FileNameQuery));
                       
                        transactionStatus = true;
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message.ToString());
                }
            }
            catch (Exception)
            {
                throw new Exception("No transaction to commit");
            }
        }
    }
    return transactionStatus;
}

The method to rollback which will delete the XML file created for the update for the specific user:

public bool RollbackTransaction(bool status, string cubeName)
{
    bool transactionStatus = false;
    cubeName = FCL.SqlConstants.cubeName;
    string strCubeName = cubeName;
    OpenConnection();

    string fileLoc = System.Web.HttpContext.Current.Server.MapPath(
      @"~\Documents\" + Environment.UserName + strCubeName + ".xml");

    if (File.Exists((fileLoc)))
    {
        File.SetAttributes(System.Web.HttpContext.Current.Server.MapPath(@"~\Documents\" + 
          Environment.UserName + strCubeName + ".xml"), FileAttributes.Normal);
    }
    if (File.Exists(fileLoc))
    {
        File.Delete(fileLoc);
    }
    string FileNameQuery = (@"~\Documents\") + Environment.UserName +  "Query.xml";
    try
    {
        if (File.Exists(System.Web.HttpContext.Current.Server.MapPath(FileNameQuery)))
        {
            File.SetAttributes(System.Web.HttpContext.Current.Server.MapPath(@"~\Documents\" + 
              Environment.UserName + strCubeName + "Query.xml"), FileAttributes.Normal);
            File.Delete(System.Web.HttpContext.Current.Server.MapPath(FileNameQuery));
            transactionStatus = true;
        }
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message.ToString());
    }

    finally
    {
        CloseConnection();
    }

    return transactionStatus;
}

Points of Interest

The point of interest is that the functionality was available in Excel 2010 only for forecasting. I Googled a lot to implement it on the web but there was no such kind of specific help available. This step may help out all the developers who are working on a forecasting project.

History

v1.0.0 | Initial release | 12.07.2012.

Writeback and writethrough using C#