通过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<) return "";
string TableName = da.KeyColumns[].Source.ToString().Split('.')[]+",";
if (tablelinks.IndexOf(TableName).Equals()) return "";
string strUsedTables = tablelinks.Substring(, tablelinks.IndexOf(TableName)-);
List<string> usedTables = strUsedTables.Split(',').ToList<string>();
usedTables.Remove(""); DataSet ds = da.Parent.DataSourceView.Schema;
for (int i = ; i < ds.Relations.Count && usedTables.Count > ; i++)
{
string childTable = usedTables[];
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 = ; 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();
i = ;
}
} 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[].Source.ToString().Split('.')[];
tablelinks = tablelinks.Append(MainTable+",");
for (int i = ; 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 = ;
}
}
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 - , );//remove the last coma
sbMeasures.Append("}");
sbsql.Remove(sbsql.Length - , );//remove ,\r\n 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[].Source.ToString().Split('.')[].ReplaceFirstUnderLineWithDot(); _dimensiontable = GetSourceQueryDefinition(mgd.CubeDimension.Dimension, origindimtable);
_facttable = GetSourceQueryDefinition(mgd.CubeDimension.Dimension, mga.KeyColumns[].Source.ToString().Split('.')[].ReplaceFirstUnderLineWithDot()); for (int i = ; 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 += "";
_groupby = _groupby.Replace(origindimtable, origindimtable + "");
} 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 == )
{
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 += "";
_groupby = _groupby.Replace(origindimtable, origindimtable + "");
} 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 \r\n");
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('.')[];
string columnName = columndatasource.Split('.')[];
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;
}