3
Vote

ExecuteDrillthroughAndFixColumns fails for money fields

description

I am trying to use ExecuteDrillthroughAndFixColumns for an Action against an Adventureworks cube. As soon as a Money measure is included in the result set of the Drillthrough command, it fails a message "OLE DB was unable to convert a value to the data type requested for column x", which, I believe, is due to the fact that in the DataTable, fields with type money have type "Object".

Of course, changing the datatype of such fields solves the Problem, but that is hardly acceptable.

the Problem can be easily reproduced by calling ExecuteDrillthroughAndFixColumns from SSMS.

comments

furmangg wrote Jul 11, 2013 at 9:20 AM

To help us reproduce faster can you provide the full query against adventure works?

gottfriededer wrote Jul 11, 2013 at 10:04 AM

Reproducing the Problem in SSMS:

call ASSP.ExecuteDrillthroughAndFixColumns(
'drillthrough maxrows 10 select [Measures].[Internet Sales Amount] on 0 from [Adventure Works] return [Internet Sales].[Internet Sales Amount]')
Regards

-- Gottfried

furmangg wrote Jul 12, 2013 at 3:04 PM

I can reproduce this. Thanks. It is only a problem in SSAS 2012, not in SSAS 2008 R2.

It appears the problem has nothing to do with bad ASSP code. We may just report the bug to Microsoft. Will keep you posted how we move forward.

DikAlex wrote Jan 8 at 2:44 PM

Any news about it?
I have SSAS 2012 and the problem is pretty annoying...

furmangg wrote Jan 8 at 7:42 PM

I dropped the ball in reporting this to Microsoft. I've just done it on Connect:
https://connect.microsoft.com/SQLServer/feedback/details/813300/sproc-returning-datatable-with-currency-data-type-generates-error-ole-db-was-unable-to-convert-a-value-to-the-data-type-requested

If this is a blocking issue, I would urge you to open a support case and reference that Connect item for more details. Otherwise, we will see how the Connect item fares. My guess is that if we're lucky, they'll fix this bug in SQL 2016. So open a support case with Microsoft if you need it before then and keep this thread posted.

DikAlex wrote Jan 9 at 10:35 AM

No, that's fine.
I made some workarounds to fix the issue.
Since the issue is relevant for measures only, and most of the measures are numbers, I changed a bit FillAdomdDataAdapterWorker in following manner:
        private static void FillAdomdDataAdapterWorker(object o)
        {
            FillAdomdDataAdapterInfo info = null;
            try
            {
                info = (FillAdomdDataAdapterInfo)o;
                info.adapter.FillSchema(info.table, SchemaType.Source);
                foreach (DataColumn col in info.table.Columns)
                {
                    if ((col.ColumnName.IndexOf("Measure", 0) >= 0) || (col.ColumnName.IndexOf("$", 0) == -1))
                    {
                        col.DataType = typeof(Double);
                    }
                }
                info.adapter.Fill(info.table);
            }
            catch (Exception ex)
            {
                info.ex = ex;
            }
            finally
            {
                info.autoEvent.Set();
            }
        }
and the problem vanished.


Also, I made a replacement for ExecuteDrillthroughAndFixColumns. The replacement allows removing columns, renaming them as you want and changing their order.

Currently, it looks like this:
       public static DataTable DrillthroughAndFixColumns(string sDrillthroughMDX, string colsToRemove = "", string colNames = "", string colOrder = "")
        {
            AdomdClient.AdomdConnection conn = TimeoutUtility.ConnectAdomdClient("Data Source=" + Context.CurrentServerID + ";Initial Catalog=" + Context.CurrentDatabaseName + ";Application Name=ASSPFix");
            try
            {
                AdomdClient.AdomdCommand cmd = new AdomdClient.AdomdCommand();
                cmd.Connection = conn;
                cmd.CommandText = sDrillthroughMDX;
                DataTable tbl = new DataTable();
                AdomdClient.AdomdDataAdapter adp = new AdomdClient.AdomdDataAdapter(cmd);
                TimeoutUtility.FillAdomdDataAdapter(adp, tbl);
                List<string> colsToRem = new List<string>();

                foreach (DataColumn col in tbl.Columns)
                {
                    if ((colsToRemove != "") && (colsToRemove.IndexOf(col.ColumnName, 0) >= 0)) 
                    {
                        colsToRem.Add(col.ColumnName);
                    }
                }

                foreach (string c in colsToRem) 
                {
                    tbl.Columns.Remove(c);
                }

                foreach (DataColumn col in tbl.Columns) {
                    string sNewColumnName = col.ColumnName.Replace(".[MEMBER_CAPTION]", "");
                    sNewColumnName = sNewColumnName.Replace("[Measures].", "");
                    sNewColumnName = sNewColumnName.Replace("[", "").Replace("]", "").Replace("&", "").Replace("$", "").Replace("-", "").Replace(".Members","").Replace(".AllMembers", "").Replace(".Children", "");
                    col.ColumnName = sNewColumnName;
                }

                if (colNames != "") 
                {
                    string[][] cols = colNames.Split(';').Select(t => t.Split(':')).ToArray();
                    if (cols.Length > 0) 
                    {
                        foreach (string[] col in cols) 
                        {
                            Int32 i = -1;
                            bool isParsed = Int32.TryParse(col[0], out i);
                            if (isParsed && (tbl.Columns.Count >= i + 1)) 
                            {
                                tbl.Columns[i].ColumnName = col[1];
                            }
                        }
                    }
                }

                if (colOrder != "") 
                {
                    string[][] cols = colOrder.Split(';').Select(t => t.Split(':')).ToArray();
                    if (cols.Length > 0) {
                        foreach (string[] col in cols) 
                        {
                            Int32 i = -1;
                            bool isParsed = Int32.TryParse(col[1], out i);
                            if (isParsed && (tbl.Columns.Count >= i + 1) && (tbl.Columns.Contains(col[0])))
                            {
                                tbl.Columns[col[0]].SetOrdinal(i);
                            }
                        }
                    }
                }

                return tbl;
            }
            finally
            {
                conn.Close();
            }
        }
Example of usage is given below. Just define custom action with Target Type = Cells, Type = Rowset, and Action Expression = 'call ASSP.DrillthroughAndFixColumns("'
"select {[Measures].[" + [Measures].CurrentMember.Name + "]} on 0, NON EMPTY [Date].[Date].[Date] DIMENSION PROPERTIES MEMBER_CAPTION on 1 from (select (" + ASSP.CurrentCellAttributes([Measures].CurrentMember) + ") on 0 from [Step-By-Step])"
'", null, "0:DateTest", "DateTest:1")'

To replace column names with your values you pass colNames parameter in following form "colIndex1:colName1;colIndex2:colName2;...". Indexes are 0 based.

To change columns order you pass colOrder parameter in following form "colName1:colIndex1;colName2:colIndex2;...". Indexes are 0 based.

DikAlex wrote Jan 9 at 10:53 AM

PS. you would need to add using System.Linq; to the top of Drillthrough.cs.

DikAlex wrote Jan 9 at 2:42 PM

Sorry, my previous version about FillAdomdDataAdapterWorker is a bit wrong, here is fixed one:
private static void FillAdomdDataAdapterWorker(object o)
        {
            FillAdomdDataAdapterInfo info = null;
            try
            {
                info = (FillAdomdDataAdapterInfo)o;
                info.adapter.FillSchema(info.table, SchemaType.Source);
                int i = info.adapter.SelectCommand.CommandText.IndexOf("DRILL", 0);
                foreach (DataColumn col in info.table.Columns)
                {
                    if ((col.ColumnName.IndexOf("Measure", 0) >= 0) || ((col.ColumnName.IndexOf('$', 0) == -1) && (i != -1)))
                    {
                        col.DataType = typeof(Double);
                    }
                }
                info.adapter.Fill(info.table);
            }
            catch (Exception ex)
            {
                info.ex = ex;
            }
            finally
            {
                info.autoEvent.Set();
            }
        }