AMO olap Test C# generate tsql and mdx

通过AMO访问online的cube,生成等值的TSql和mdx

自动生成等值的TSQL和MDX进行Cube测试.其中难度比较大的部分是拼接TSQL.

暂时不处理calculations,只除理metrics和Regular Type的dimension Usage.

metric的聚合方法只处理(max,min,sum,count,distinct count)

以下是问题的记录与处理情况.

1.adventureworks示例cube中,会把Date dimension的attributes的表名取成dimtime. 未解决 已经解决此问题

2.如果dimension 表中的attributes引用了其他表的字段,则暂时没有解决这个问题. 解决

使用TableJoin和TableLinks方法来解决这个问题.

Tablelinks方法获取dimensionattributes中使用到的字段所在的表与主表的关系链.

Tablejoin:生成连接的语句.

或许不完善.

3.新问题 如果hierarchy的level的source attribute用的是calculation,那么现在仍然会报错,我应该判断

这个字段是不是计算成员,如果是计算成员,则要去取计算成员的定义.

http://technet.microsoft.com/en-us/library/ms345093.aspx

4.没有考虑dimension attribute的orderby 属性与name column. 未解决(尽快解决 已经解决此问题)

5.问题 自连接的情况 如果dimension usage 当中事实表和维度表是一个表的话,需要给表加别名.

现在只考虑事实表与维度表相同的情况,其他的暂时不考虑.

6.在某些情况下,sum出来的结果会发生溢出,所以需要提前把它转化为bigint.

可以在判断其列的类型为int时自动将其转化成bigint. 未解决

7.遇到父子dimension会形成死循环,或者类似存在回链关系的DSV维度,忽略掉. 解决:第二次出现的时候直接断开.

以下是生成等值TSQL与MDX的运行情况,使用Adventurework示例数据仓库SSAS进行测试,除了提到的第一个问题外,

生成的TSQL均可以运行.

   

界面介绍:填入OLAP CUBE的连接字符串,选择DB,CUBE与dimensions.直接点击生成mdxtsql.

以下贴一些主要的方法(代码)

由于dimension可能引用多个表,所以要把这几个表关联起来,有了以下代码:   

   private string TableJoin(DimensionAttribute da)
        {
            //根据l找到sourcetable
            //然后去匹配tablelinks中的长度 然后再去获取
            string morejoin = "";
            string tablelinks = GetTableLinks(da.Parent);
            if (tablelinks.Split(',').Length<3) return "";
            string TableName = da.KeyColumns[0].Source.ToString().Split('.')[0]+",";
            if (tablelinks.IndexOf(TableName).Equals(0)) return "";
            string strUsedTables = tablelinks.Substring(0, tablelinks.IndexOf(TableName)-1);
            List<string> usedTables = strUsedTables.Split(',').ToList<string>();
            usedTables.Remove("");

            DataSet ds = da.Parent.DataSourceView.Schema;
            for (int i = 0; i < ds.Relations.Count && usedTables.Count > 0; i++)
            {
                string childTable = usedTables[0];
                DataRelation dr = ds.Relations[i];
                if (dr.ChildTable.TableName.Equals(childTable))
                {
                    morejoin = morejoin.Append(" JOIN " 
                         + GetSourceQueryDefinition(da.Parent,dr.ParentTable.TableName.ReplaceFirstUnderLineWithDot())
                        ).AppendNewLine().Append("ON ");
                    for (int j = 0; j < dr.ChildColumns.Length; j++)
                    {
                        morejoin = morejoin.Append(
                            dr.ParentTable.TableName.ReplaceFirstUnderLineWithDot()+"." + dr.ParentColumns[j].ColumnName + " = " +
                            dr.ChildTable.TableName.ReplaceFirstUnderLineWithDot() +"." + dr.ChildColumns[j].ColumnName
                            ).AppendNewLine();
                    }
                    usedTables.RemoveAt(0);
                    i = 0;
                }
            }

            return morejoin;
        }
        private string GetTableLinks(Dimension dim)
        {
            HashSet<string> tables = new HashSet<string>();
            string  MainTable =  "";
            string  tablelinks = string.Empty;
            DataSet ds = dim.DataSourceView.Schema;
            MainTable= dim.KeyAttribute.KeyColumns[0].Source.ToString().Split('.')[0];
            tablelinks = tablelinks.Append(MainTable+",");
            for (int i = 0; i < ds.Relations.Count; i++ )
            {
                DataRelation dr = ds.Relations[i];
                if (dr.ChildTable.TableName.Equals(MainTable))
                {
                    tablelinks = tablelinks.Append(dr.ParentTable.TableName + ",");
                    MainTable = dr.ParentTable.TableName;
                    if (tables.Contains(MainTable))
                        break;
                    else
                        tables.Add(MainTable);
                    i = 0;
                }
            }
            return tablelinks;
        }

 public DataTable GetMDXSQL(Cube _cube)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("MDX", typeof(String));
            dt.Columns.Add("TSQL",typeof(String));
            string mdx = "";
            string tsql = "";

            StringBuilder sbMeasures = new StringBuilder("{");
            foreach (MeasureGroup mg in _cube.MeasureGroups)
            {
                #region Measure
                foreach (Measure m in mg.Measures)
                {
                    #region Metrics
                    if (m.AggregateFunction.Equals(AggregationFunction.Sum)
                        ||
                        m.AggregateFunction.Equals(AggregationFunction.Count)
                        ||
                        m.AggregateFunction.Equals(AggregationFunction.DistinctCount)
                        ||
                        m.AggregateFunction.Equals(AggregationFunction.Max)
                        ||
                        m.AggregateFunction.Equals(AggregationFunction.Min)
                        )
                    {
                        sbMeasures.Append(m.Name.QuoteName().AppendComma());
                        if (m.AggregateFunction.Equals(AggregationFunction.Sum))
                            sbsql.Append(
                                AggregationFunction.Sum.ToString().AppendLeftParenthesis()
                                + m.Source.ToString().ReplaceFirstUnderLineWithDot()
                                .AppendRightParenthesis()
                                + " AS "
                                + m.Name.QuoteName().AppendComma().AppendNewLine()
                                );
                        if (m.AggregateFunction.Equals(AggregationFunction.Count))
                            sbsql.Append("COUNT(*) AS "
                            + m.Name.QuoteName().AppendComma().AppendNewLine()
                            );
                        if (m.AggregateFunction.Equals(AggregationFunction.DistinctCount))
                            sbsql.Append(
                              "COUNT(Distinct "
                               + m.Source.ToString().ReplaceFirstUnderLineWithDot()
                               .AppendRightParenthesis()
                               + " AS "
                               + m.Name.QuoteName().AppendComma().AppendNewLine()
                               );
                        if (m.AggregateFunction.Equals(AggregationFunction.Max))
                            sbsql.Append(
                               AggregationFunction.Max.ToString().AppendLeftParenthesis()
                               + m.Source.ToString().ReplaceFirstUnderLineWithDot()
                               .AppendRightParenthesis()
                               + " AS "
                               + m.Name.QuoteName().AppendComma().AppendNewLine()
                               );
                        if (m.AggregateFunction.Equals(AggregationFunction.Min))
                            sbsql.Append(
                               AggregationFunction.Min.ToString().AppendLeftParenthesis()
                               + m.Source.ToString().ReplaceFirstUnderLineWithDot()
                               .AppendRightParenthesis()
                               + " AS "
                               + m.Name.QuoteName().AppendComma().AppendNewLine()
                               );
                    #endregion

                        sbMeasures.Remove(sbMeasures.Length - 1, 1);//remove the last coma
                        sbMeasures.Append("}");
                        sbsql.Remove(sbsql.Length - 3, 3);//remove ,


                        HashSet<string> selectedDims = GetSelectedDimensions();
                        #region MeasureGroupDimension
                        foreach (MeasureGroupDimension mgd in mg.Dimensions)
                        {
                            if (!selectedDims.Contains(mgd.Dimension.Name)) continue;

                            string _facttable = string.Empty;
                            string _dimensiontable = string.Empty; 
                            string origindimtable = string.Empty;
                            string _joinby = " ON ";
                            string _groupby = " GROUP BY ";

                            if (!mgd.GetType().Name.Equals("RegularMeasureGroupDimension"))
                                continue;
 
                            foreach (MeasureGroupAttribute mga in ((RegularMeasureGroupDimension)mgd).Attributes)
                            {

                                if (mga.Type != MeasureGroupAttributeType.Granularity) continue;
                                origindimtable = mga.Attribute.KeyColumns[0].Source.ToString().Split('.')[0].ReplaceFirstUnderLineWithDot();
                                
                                _dimensiontable = GetSourceQueryDefinition(mgd.CubeDimension.Dimension, origindimtable);
                                _facttable = GetSourceQueryDefinition(mgd.CubeDimension.Dimension, mga.KeyColumns[0].Source.ToString().Split('.')[0].ReplaceFirstUnderLineWithDot());

                                for (int i = 0; i < mga.KeyColumns.Count; i++)
                                {
                                    _joinby += mga.Attribute.KeyColumns[i].Source.ToString().ReplaceFirstUnderLineWithDot();
                                    _joinby += " = " + mga.KeyColumns[i].Source.ToString().ReplaceFirstUnderLineWithDot().Append(" AND ");
                                }
                                break;
                            }
                            _joinby = _joinby.TrimEnd(" AND ".ToCharArray());

                            #region Hierarchy level
                            foreach (Hierarchy h in mgd.CubeDimension.Dimension.Hierarchies)
                            {
                                foreach (Level l in h.Levels)
                                {
                                    mdx =
                                     string.Format(mdxtemplate, sbMeasures.ToString().TrimEnd(','),
                                     h.Parent.Name.QuoteName().Append(".").Append(h.Name.QuoteName())
                                     .Append(".").Append(l.Name.QuoteName()),
                                    _cube.Name.QuoteName());

                                    foreach (DataItem di in l.SourceAttribute.KeyColumns)
                                    {
                                       // _groupby += di.Source.ToString().ReplaceFirstUnderLineWithDot().AppendComma();
                                        _groupby += GetColumnDefinition(l.Parent.Parent.DataSourceView.Schema,di.Source.ToString());
                                    }
                                    _groupby = _groupby.TrimEnd(',');
                                    //if the _facttable == _dimensiontable then give a alias for dimtable
                                    if (_facttable.Equals(_dimensiontable))
                                    {
                                        _dimensiontable += "2";
                                        _groupby = _groupby.Replace(origindimtable, origindimtable + "2");
                                    }

                                    tsql =
                                        sbsql.ToString()
                                        .AppendNewLine()
                                        .Append(" FROM ")
                                        .Append(_facttable)
                                        .Append(" JOIN ")
                                        .Append(_dimensiontable)
                                        .AppendNewLine()
                                        .Append(_joinby);
                                        //.AppendNewLine();
                                        //.Append(_groupby);

                                    string morejoin = TableJoin(l.SourceAttribute);
                                    tsql = tsql.Append(morejoin);
                                   
                                    tsql = tsql.AppendNewLine().Append(_groupby);
                                    _groupby = " GROUP BY ";
                                    
                                    //Add rows for dt
                                    AddRow(dt, mdx, tsql);
                                    
                                }
                            }

                            #endregion

                            #region Attributes
                            if (mgd.CubeDimension.Dimension.Hierarchies.Count == 0)
                            {
                                foreach (DimensionAttribute da in mgd.CubeDimension.Dimension.Attributes)
                                {
                                    if (da.AttributeHierarchyEnabled == true)
                                    {
                                        mdx = string.Format(mdxtemplate, sbMeasures.ToString().TrimEnd(','),
                                            da.Parent.Name.QuoteName().Append(".").Append(
                                            da.Name.QuoteName())
                                            , _cube.Name.QuoteName());
                                    }
                                    else continue;
                                    foreach (DataItem di in da.KeyColumns)
                                    {
                                      //  _groupby += di.Source.ToString().ReplaceFirstUnderLineWithDot().AppendComma();
                                        _groupby += GetColumnDefinition(da.Parent.DataSourceView.Schema, di.Source.ToString());
                                    }
                                    _groupby = _groupby.TrimEnd(',');
                                    //if the _facttable == _dimensiontable then give a alias for dimtable
                                    if (_facttable.Equals(_dimensiontable))
                                    {
                                        _dimensiontable += "2";
                                        _groupby = _groupby.Replace(origindimtable, origindimtable + "2");
                                    }

                                    tsql=
                                        sbsql.ToString()
                                        .AppendNewLine()
                                        .Append(" FROM ")
                                        .Append(_facttable)
                                        .Append(" JOIN ")
                                        .Append(_dimensiontable)
                                        .AppendNewLine()
                                        .Append(_joinby)
                                        //.AppendNewLine()
                                        //.Append(_groupby)
                                        ;
                                    string morejoin = TableJoin(da);
                                    tsql = tsql.Append(morejoin);

                                    tsql = tsql.AppendNewLine().Append(_groupby);
                                    _groupby = " GROUP BY ";

                                    AddRow(dt, mdx, tsql);
                                }
                            }
                            #endregion

                        }
                        #endregion
                        
                        sbsql = new StringBuilder(" SELECT 
");
                        sbMeasures = new StringBuilder("{");
                    }
                }

                #endregion
            }

            return dt;
        }

由于dimension的source table经常会含有计算列或者本身是named query 所以有了以下代码:

  public string GetSourceQueryDefinition(Dimension dim,string keycolsrc)
        { 
            //获取dim的dsv 获取
            string rst=keycolsrc;
            DataSet ds = dim.DataSourceView.Schema;
            string tbname = keycolsrc.Replace("dbo.","");
            if(ds.Tables.Contains(tbname))
            {
                if(ds.Tables[tbname].ExtendedProperties.Contains("QueryDefinition"))
                    rst = "("+ds.Tables[tbname].ExtendedProperties["QueryDefinition"].ToString() + ") as "+ tbname;
            }

            return rst;
        }
        public string GetColumnDefinition(DataSet ds, string columndatasource)
        {
            string columnDefinition = "";
            string tableName = columndatasource.Split('.')[0];
            string columnName = columndatasource.Split('.')[1];
            if (ds.Tables.Contains(tableName)
               && ds.Tables[tableName].Columns.Contains(columnName)
               && ds.Tables[tableName].Columns[columnName].ExtendedProperties.Contains("ComputedColumnExpression")
                )
            {
                columnDefinition = "(" + ds.Tables[tableName].Columns[columnName].ExtendedProperties["ComputedColumnExpression"].ToString() +")";
            }
            else
            {
                columnDefinition = columndatasource.ReplaceFirstUnderLineWithDot().AppendComma();
            }
            return columnDefinition;
        }