具体实施方式
一种基于动态交叉表的多维数据实时分析方法,在前端采用分析向导的形式,自主选择指标和维度;在后台采用SSAS方式,动态地创建CUBE,动态交叉表的运算过程最大程度的放在服务器端运行,在服务端直接动态执行SSAS,减轻客户端的压力。
该方法实现步骤如下:
S1:数据整理,包括体系事实表和维度表。
一个多维指标体系对应一个主题,多维指标体系下包含指标、维度、量度等要素,对应事实表维度模型中的指标、维度、指标量度等信息。指标、维度、量度可独立于多维指标体系定义,与多维指标是多对多关系。一个多维指标体系对应一个指标分类,采用多维指标体系描述指标维度对应关系;多维指标体系提供生成事实表功能,即一个多维指标体系对应一个事实表,减少数据量和ETL处理的复杂性。
每个体系对应一个事实表,体系事实表包含所要分析的指标字段、量度字段和维度编号字段内容,通过维度编号字段与各个维度表相关联。体系编号与对应的数据库中的表名保持一致,指标编号和量度编号对应表中字段名,且保持一致,指标体系中存储纬度编号外键,用于与维度表作关联。
维度表包含各种纬度字段内容,记录各个维度信息,如员工维度表,有工号、姓名、性别、生日、联系电话、学历、所在部门等等维度。
交叉表是报表中常见的类型,属于基本的分析报表,将所有的分析字段按照数据类型分为指标、维度和量度,指标字段和量度字段为数值型,维度字段为字符型。具体应用到交叉表时,维度在行或列中体现,指标和量度在值区域中体现,参与具体的聚合计算。通常聚合计算函数有求和、计数、平均值、方差、标准差等。
S2:指标工具完成指标体系定义、指标定义、维度定义和量度定义,再通过指标体系的包含关系,依次将指标、纬度和量度包含其中,可以通过指标工具将指标、维度和量度包含到指标体系内。
S3:通过语义对象设计器对所有维度加以描述,将维度进行分组和分级;借助语义对象设计器,把维度表中的字段重新描述,在当今大数据中分析维度已变得十分庞大,语义对象设计器按维度属性划分,将维度分为基本维度、统计依据和其他维度属性。
S4:通过分析向导自助完成体系与指标、维度和量度的包含关系;
S5:选择所要分析的指标和维度,形成实时分析表。
通过分析向导,用户自主选择所要分析的指标,由已选指标关联出所用的维度,将这些指标和维度以参数变量的形式传送到服务器端,在SSAS层明确出各个指标和维度属性,动态创建出交叉表,最终分析基于Cube方式进行,进而对已选指标和维度进行条件筛选。使交叉表分析业务变得更精准、更简洁,如“区域等于山东 并且 贷款金额大于等于1000000”的公司运营状况分析,其中,区域是维度,贷款金额是指标。
将所选的指标和维度及筛选条件传到后台程序处理,通过程序控制SSAS操作,从所用的事实表和维度表提取出符合条件的数据,并“告诉”SSAS哪些字段是指标、哪些字段是维度,从而动态创建出CUBE多维分析。指标和量度作sum预处理,CUBE数据反馈到前端,由用户拖拽到行或列,形成动态的实时交叉表。
关于数据库连接,则采用XML配置文件的方式,支持SQL SERVER,ORACLE等主流数据库。
交叉表分析的数据流转过程如下:
1)、语义对象,描述表结构;
2)、多维指标体系,描述事实表和表数据间的关系;
3)、分析向导,并按取数条件筛选数据;
4)、动态生成交叉表Cube。生成的CUBE支持的数据分析模型包括多个指标+多个一般维度+多个退化维度、多个指标+多个退化维度、多个指标+多个复杂维度(对应一个语义对象的维度)、空指标+多个一般维度+多个退化维度和空指标+多个退化维度。
实施例
数据准备工作:指标体系定义、指标定义、量度定义、维度定义、指标体系包含关系、语义对象设计器;
数据库连接设置;
创建分析向导:指标选择、维度选择、条件设定;
SSAS层动态创建CUBE;
前端展现实时多维分析。
指标、维度处理的核心代码如下:
// 参数说明
// zbtx(指标体系编号),zb(指标编号的字符串),wd(维度编号的字符串),rootwd(带有根节点的维度字符串),wherStr(条件设定里的筛选条件字符串)
public string GetGuideSql(string zbtx, string zb, string wd, stringrootwd, string wherStr)
{
string txtable = zbtx.ToUpper() + tableflag; //对应的事实表
string lds = ZBTXLD(zbtx); //量度属性
string wdTables = string.Empty;
//维度主键别名
string wdpkeyAss = string.Empty;
//基本维度名称
string mcwdAss = string.Empty;
//统计依据别名
string tjwdAss = string.Empty;
//其他维度别名
string qtwdAss = string.Empty;
//独立维度别名
string dlwdAss = string.Empty;
//指标别名
string zbAss = string.Empty;
//量度别名
string zbldAss = string.Empty;
///维度主键、基本名称、统计依据、退化维度字段
string keybys = string.Empty;
string mcwdbys = string.Empty;
string tjwdbys = string.Empty;
string dlwdbys = string.Empty;
string dlwdcolums = string.Empty;
string conditions = string.Empty;
string zbcolums = string.Empty;
string zbldcolums = string.Empty;
string wtableName = string.Empty;
string wtablekey = string.Empty;
//按照维度主键个数循环
for (int k = 0; k < rootkeyArr.Length; k++)
{///主键循环开始
string table_key = rootkeyArr[k];
string wdtable = table_key.Split('-')[0].ToUpper();
string rpsobjid = "@GS2000@." + wdtable; //对应的语义对象名称
wtableName = wdtable;
string wdtableCnName = string.Empty;
//从数据库中取得维度表描述
string wdtsql = "select SIMA_DISP from RPSIMAwhere SIMA_OBJID='" + rpsobjid + "'";DataSet wdds = gBBManager.ExecuteDataSet(wdtsql);
if (wdds != null && wdds.Tables[0].Rows.Count >0)
{
//语义对象对应的维度表描述信息
wdtableCnName = wdds.Tables[0].Rows[0]["SIMA_DISP"].ToString();
}
string key = table_key.Split('-')[1].ToUpper();
wtablekey = key;
string keydcolums = string.Empty; //维度主键列
string mcwdcolums = string.Empty; //基本维度列
string tjwdcolums = string.Empty; //统计依据维度列
string qtwdcolums = string.Empty; //其他维度列
string keyby = string.Empty;
string mcwdby = string.Empty;
string tjwdby = string.Empty;
string cubecolEng = string.Empty; //cube所用英文名称
string cubecolCn = string.Empty; //cube所用中文名称
string wdtAs = " W" + k + "."; //维度表别名标识
wdTables += wdtable + " W" + k + " ,";
//维度主键
string keysql = string.Empty;
if (gBBManager.DbType == BIDbType.Oracle) //oracle数据源时
{
keysql = "select SOBJ_SHORT as pkey, SOBJ_DISP from RPSOBJ where substring(SOBJ_OBJID, 10, len(SOBJ_OBJID))||'-'||SOBJ_SHORT IN(" + rootAndwd + ") and SOBJ_OBJID='" + rpsobjid + "' and SOBJ_ATTR='BH'";
}
Else //sql server 数据源
{
keysql = "select SOBJ_SHORT as pkey , SOBJ_DISP from RPSOBJ where substring(SOBJ_OBJID, 10, len(SOBJ_OBJID))+'-'+SOBJ_SHORT IN(" + rootAndwd + ") and SOBJ_OBJID='" + rpsobjid + "' and SOBJ_ATTR='BH'";
}
DataSet keyrootwd = gBBManager.ExecuteDataSet(keysql);
if (keyrootwd != null && keyrootwd.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < keyrootwd.Tables[0].Rows.Count; i++)
{
string col = keyrootwd.Tables[0].Rows[i]["pkey"].ToString();
string keyShow = "T." + col.ToUpper() + "As " + txtable + "_" + col.ToUpper() + ",";
keydcolums += keyShow; //形成AS字段 将英文列名转为可读的中文列名
//group by字段
keyby += "T." + col.ToUpper() + ",";
//字段名
cubecolEng += txtable + "_" + col.ToUpper() + ","; //cube所用的英文列名
//cube所用的英文列名
cubecolCn += keyrootwd.Tables[0].Rows[i]["SOBJ_DISP"].ToString() + ",";
}
}
else { keydcolums = ""; }
//基本维度 名称
string mcsql = string.Empty;
if (gBBManager.DbType == BIDbType.Oracle)//oracle数据源
{
mcsql = "select SOBJ_SHORT as wd ,SOBJ_DISPfrom RPSOBJ where substring(SOBJ_OBJID, 10, len(SOBJ_OBJID))||'-'||SOBJ_SHORTIN(" + rootAndwd + ") and SOBJ_OBJID='" + rpsobjid + "' and SOBJ_ATTR='MC'";
}
Else //SQL SERVER 数据源
{
mcsql = "select SOBJ_SHORT as wd, SOBJ_DISPfrom RPSOBJ where substring(SOBJ_OBJID, 10, len(SOBJ_OBJID))+'-'+SOBJ_SHORTIN(" + rootAndwd + ") and SOBJ_OBJID='" + rpsobjid + "' and SOBJ_ATTR='MC'";
}
DataSet mcrootwd = gBBManager.ExecuteDataSet(mcsql);
if (mcrootwd != null && mcrootwd.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < mcrootwd.Tables[0].Rows.Count; i++)
{
string col = mcrootwd.Tables[0].Rows[i]["wd"].ToString();
string mcwdShow = wdtAs + col.ToUpper() +" As " + wdtable + "_" + col.ToUpper() + ",";
mcwdcolums += mcwdShow;
//group by
mcwdby += wdtAs + col.ToUpper() + ",";
//字段名
cubecolEng += wdtable + "_" + col.ToUpper() + ",";////cube所用的英文列名
cubecolCn += mcrootwd.Tables[0].Rows[i]["SOBJ_DISP"].ToString() + ",";////cube所用的中文列名
}
}
else { mcwdcolums = ""; }
//统计依据
string tjsql = string.Empty;
if (gBBManager.DbType == BIDbType.Oracle)
{
tjsql = "select SOBJ_SHORT as wd , SOBJ_DISP,SOBJ_PKOBJ,SOBJ_PKCOL from RPSOBJ where substring(SOBJ_OBJID, 10, len(SOBJ_OBJID))||'-'||SOBJ_SHORT IN(" + rootAndwd + ") and SOBJ_OBJID='" + rpsobjid +"' and SOBJ_IFJSDX='101' and SOBJ_ATTR NOT IN('BH','MC')";
}
else
{
tjsql = "select SOBJ_SHORT as wd, SOBJ_DISP,SOBJ_PKOBJ,SOBJ_PKCOL from RPSOBJ where substring(SOBJ_OBJID, 10, len(SOBJ_OBJID))+'-'+SOBJ_SHORT IN(" + rootAndwd + ") and SOBJ_OBJID='" + rpsobjid +"' and SOBJ_IFJSDX='101' and SOBJ_ATTR NOT IN('BH','MC')";
}
DataSet tjrootwd = gBBManager.ExecuteDataSet(tjsql);
if (tjrootwd != null && tjrootwd.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < tjrootwd.Tables[0].Rows.Count; i++)
{
string col = tjrootwd.Tables[0].Rows[i]["wd"].ToString();
string tjwdShow = wdtAs + col.ToUpper() +" As " + wdtable + "_" + col.ToUpper() + ",";
//group by
tjwdby += wdtAs + col.ToUpper() + ",";
//字段名
cubecolEng += wdtable + "_" + col.ToUpper() + ",";
cubecolCn += tjrootwd.Tables[0].Rows[i]["SOBJ_DISP"].ToString() + ",";
//码值转换
string mzObj = tjrootwd.Tables[0].Rows[i]["SOBJ_PKOBJ"].ToString();
string mzCol = tjrootwd.Tables[0].Rows[i]["SOBJ_PKCOL"].ToString();
if (mzObj.Length > 9 && mzCol.Length > 1)
{
string mzTable = mzObj.Substring(9,mzObj.Length - 9);
string mzstr = "(select " + mzCol + "FROM " + mzTable + " where systemid=" + col + ") as " + wdtable + "_" +col.ToUpper();
tjwdShow = mzstr + ",";
}
tjwdcolums += tjwdShow;
}
}
else { tjwdcolums = ""; }
//其他维度
string qtsql = string.Empty;
if (gBBManager.DbType == BIDbType.Oracle)
{
qtsql = "select SOBJ_SHORT as wd, SOBJ_DISP,SOBJ_PKOBJ,SOBJ_PKCOL from RPSOBJ where substring(SOBJ_OBJID, 10, len(SOBJ_OBJID))||'-'||SOBJ_SHORT IN(" + rootAndwd + ") and SOBJ_OBJID='" + rpsobjid + "' and SOBJ_IFJSDX='110' and SOBJ_ATTR NOT IN('BH','MC')";
}
else
{
qtsql = "select SOBJ_SHORT as wd, SOBJ_DISP,SOBJ_PKOBJ,SOBJ_PKCOLfrom RPSOBJ where substring(SOBJ_OBJID, 10, len(SOBJ_OBJID))+'-'+SOBJ_SHORTIN(" + rootAndwd + ") and SOBJ_OBJID='" + rpsobjid + "' and SOBJ_IFJSDX='110'and SOBJ_ATTR NOT IN('BH','MC')";
}
DataSet qtrootwd = gBBManager.ExecuteDataSet(qtsql);
if (qtrootwd != null && qtrootwd.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < qtrootwd.Tables[0].Rows.Count; i++)
{
string col = qtrootwd.Tables[0].Rows[i]["wd"].ToString();
string qtwdShow = "MAX(" + wdtAs +col.ToUpper() + ")" + " As " + wdtable + "_" + col.ToUpper() + ",";
//字段名
cubecolEng += wdtable + "_" + col.ToUpper() + ",";
cubecolCn += qtrootwd.Tables[0].Rows[i]["SOBJ_DISP"].ToString() + ",";
//码值转换
string mzObj = qtrootwd.Tables[0].Rows[i]["SOBJ_PKOBJ"].ToString();
string mzCol = qtrootwd.Tables[0].Rows[i]["SOBJ_PKCOL"].ToString();
if (mzObj.Length > 9 && mzCol.Length > 1)
{
string mzTable = mzObj.Substring(9,mzObj.Length - 9);
string mzstr = "(select " + mzCol + "FROM " + mzTable + " where systemid=MAX(" + col + ")) as " + wdtable + "_" +col.ToUpper();
//select khbh,(select ITEMVAL frombimdhcmb where systemid=KHGM) from HC_CUSTOMER
qtwdShow = mzstr + ",";
}
qtwdcolums += qtwdShow;
}
// qtwdcolums = qtwdcolums.Substring(0,qtwdcolums.Length - 1);
}
else { qtwdcolums = ""; }
wdpkeyAss += keydcolums;
mcwdAss += mcwdcolums;
tjwdAss += tjwdcolums;
qtwdAss += qtwdcolums;
keybys += keyby;
mcwdbys += mcwdby;
tjwdbys += tjwdby;
conditions += "T." + key + "=" + " W" + k + "." +key + " and ";
///cube所需数据处理 带级次的普通维度
cubecolEng = wdtable + "," + cubecolEng;
if (cubecolEng.Length > 1)
{
cubecolEng = cubecolEng.Substring(0,cubecolEng.Length - 1);
}
CubeWDEngStr += cubecolEng + "#";
cubecolCn = wdtableCnName + "," + cubecolCn;
if (cubecolCn.Length > 1)
{
cubecolCn = cubecolCn.Substring(0,cubecolCn.Length - 1);
}
CubeWDCnStr += cubecolCn + "#";
}///主键循环结束
//体系包含的量度
string[] ldsArr = lds.Split(',');
string cubezbCn = string.Empty;
string cubeldEng = string.Empty;
string cubeldCn = string.Empty;
//指标列 带聚合sum
string zbscols = string.Empty;
string[] zbsArr = zb.Split(',');
for (int i = 0; i < zbsArr.Length; i++)
{
string zbsql = "select FXZBZD_ZBBH ,FXZBZD_MCfrom FXZBZD WHERE FXZBZD_ZBBH='" + zbsArr[i].ToUpper() + "'";
DataSet zbds = gBBManager.ExecuteDataSet(zbsql);
if (zbds != null && zbds.Tables[0].Rows.Count >0)
{
cubezbCn += zbds.Tables[0].Rows[0]["FXZBZD_MC"].ToString() + ",";
}
zbscols += "sum(" + zbsArr[i].ToUpper() + ") As "+ zbsArr[i].ToUpper() + ", ";
if (lds.Length > 0)
{
for (int j = 0; j < ldsArr.Length; j++)
{
string zbldcol = zbsArr[i].ToUpper() +ldsArr[j].ToUpper();
string zbnamesql = "select FXZBZD_ZBBH ,FXZBZD_MC from FXZBZD WHERE FXZBZD_ZBBH='" + zbsArr[i].ToUpper() + "'";
DataSet zbnameds = gBBManager.ExecuteDataSet(zbnamesql);
string zbBH = zbnameds.Tables[0].Rows[0]["FXZBZD_ZBBH"].ToString().ToUpper();
string zbMC = zbnameds.Tables[0].Rows[0]["FXZBZD_MC"].ToString();
string ldnamesql = "select BIMDZBLDZD_LDBH,BIMDZBLDZD_LDMC from BIMDZBLDZD WHERE BIMDZBLDZD_LDBH='" + ldsArr[j].ToUpper() + "'";
DataSet ldnameds = gBBManager.ExecuteDataSet(ldnamesql);
string ldBH = ldnameds.Tables[0].Rows[0]["BIMDZBLDZD_LDBH"].ToString().ToUpper();
string ldMC = ldnameds.Tables[0].Rows[0]["BIMDZBLDZD_LDMC"].ToString();
zbldcol = "sum(" + zbldcol.ToUpper() +")" + " as " + zbBH + ldBH;
zbldcolums += zbldcol + ",";
//字段名
cubeldEng += zbBH + ldBH + ",";
cubeldCn += zbMC + ldMC + ",";
}
}
else { zbldcolums = ""; }
}
//////整理成cube所用数据////////
////识别退化维度
string[] rootwdArr = rootAndwd.Split(',');
string dlrootwd = string.Empty;
for (int i = 0; i < rootwdArr.Length; i++)
{
string singlewd = rootwdArr[i];
if (singlewd.Contains("DLWD-"))
{
dlrootwd += singlewd + ",";
}
}
if (dlrootwd.Length > 1)
{
dlrootwd = dlrootwd.Substring(0, dlrootwd.Length- 1);
string wdsStr = dlrootwd.Replace("DLWD-", "");
string[] wdsARR = wdsStr.Split(',');
for (int y = 0; y < wdsARR.Length; y++)
{
string dlwdby = string.Empty;
string dlcnSql = "select BIMDZBWDZD_WDBH,BIMDZBWDZD_MC, BIMDZBWDZD_Help from BIMDZBWDZD where BIMDZBWDZD_SX='SJ' ANDBIMDZBWDZD_WDBH=" + wdsARR[y];
DataSet wdcnds = gBBManager.ExecuteDataSet(dlcnSql);
if (wdcnds != null && wdcnds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < wdcnds.Tables[0].Rows.Count; i++)
{
string col = wdcnds.Tables[0].Rows[i]["BIMDZBWDZD_WDBH"].ToString();
string dlwdShow = "T." + col + " As "+ txtable + "_" + col.ToUpper() + ",";
CubeDLWDEngStr += txtable + "_" +col.ToUpper() + "#";
string col2 = wdcnds.Tables[0].Rows[i]["BIMDZBWDZD_MC"].ToString();
CubeDLWDCnStr += col2 + "#";
//group by
dlwdby += "T." + col.ToUpper() + ",";
//码值转换
string mzHelp = wdcnds.Tables[0].Rows[i]["BIMDZBWDZD_Help"].ToString();
if (mzHelp.Length > 16)
{
string[] helpArr = mzHelp.Split(':');
string mzTable = helpArr[0].Substring(9, helpArr[0].Length - 9);
string mzcol = helpArr[1];
string mzstr = "(select " + mzcol+ " FROM " + mzTable + " where systemid=" + col + ") as " + txtable + "_" +col.ToUpper();
dlwdShow = mzstr + ",";
}
dlwdcolums += dlwdShow;
dlwdbys += dlwdby;
}
}
}
}
///量度所用格式
CubeLDEngStr = zb.ToUpper() + "," + cubeldEng;
CubeLDCnStr = cubezbCn + cubeldCn;
zbAss += zbscols;
zbldAss += zbldcolums;
dlwdAss = dlwdcolums;
if (wdTables.Length > 1)
{
wdTables = wdTables.Substring(0, wdTables.Length- 1);
}
if (conditions.Length > 4)
{
conditions = conditions.Substring(0,conditions.Length - 4);
}
string zbAndzbldAss = zbAss + zbldAss;
zbAndzbldAss = zbAndzbldAss.Trim();
if (zbAndzbldAss.Length > 1)
{
zbAndzbldAss = zbAndzbldAss.Substring(0,zbAndzbldAss.Length - 1);
}
string groupbycols = keybys + dlwdbys + mcwdbys +tjwdbys;
if (groupbycols.Length > 1)
{
groupbycols = groupbycols.Substring(0,groupbycols.Length - 1);
}
////sql server 与oracel数据库差异化处理
if (gBBManager.DbType == BIDbType.Oracle)
{
TXSQL = "select SYS_GUID() AS CID, " + wdpkeyAss+ mcwdAss + dlwdAss + tjwdAss + qtwdAss + zbAndzbldAss + " from " + txtable +" T," + wdTables + " where " + conditions + wheres + " group by " +groupbycols;
}
else
{
TXSQL = "select NEWID() AS CID, " + wdpkeyAss +mcwdAss + dlwdAss + tjwdAss + qtwdAss + zbAndzbldAss + " from " + txtable + "T," + wdTables + " where " + conditions + wheres + " group by " +groupbycols;
}
return TXSQL;
}
动态创建CUBE核心代码:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Genersoft.BI.SPI;
using Genersoft.BI.Manager;
using Microsoft.AnalysisServices;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.Web;
// 参数说明
// strCubeDBName (所用数据库名),strFactTableName (事实表名字符),CubeDLWD (退化维度),CubeWD (普通维度字符串), //strMeasure (量度字符串),strFactTableNameCN(事实表中文名),CubeDLWDCN(退化维度中文名),CubeWDZ(普通维度中文),strMeasureZ(量度中文名称),tableandcon(取数条件)
public static void BuildCube(string strCubeDBName, string[]strFactTableName, string[] CubeDLWD, string[] CubeWD, string[] strMeasure,string[] strFactTableNameCN, string[] CubeDLWDCN, string[] CubeWDZ, string[] strMeasureZ, string tableandcon)
{
string strServerName = SERVERIP; //应用服务器ip
string strDBServerName = DBSERVERIP; //数据库服务器IP
string strProviderName = "msolap";
string strDBName = DATABASE; //数据库名称
string strCubeDataSourceName = DATABASE; //cube数据源
string strCubeDataSourceViewName = DATABASE; //cube 视图
int intDimensionTableCount = CubeWD.Length; //维度个数
int intFactTableCount = strFactTableName.Length; //事实表个数
int MeasureNum = strMeasure.Length; //量度个数
int[] intMeasureNum = new int[MeasureNum];
string[][] strMeasureCN = new string[MeasureNum][];//量度中文名
string[][] strMeasureEN = new string[MeasureNum][];//量度英文名
for (int i = 0; i < MeasureNum; i++)
{
strMeasureEN[i] = strMeasure[i].Split('#');
strMeasureCN[i] = strMeasureZ[i].Split('#');
intMeasureNum[i] = strMeasureEN[i].Length;
}
int NumDLWD = CubeDLWD.Length;
int NumWD = CubeWD.Length;
string[][] CubeWDTableAndKey = new string[NumWD][];
string[][] CubeWDFL = new string[NumWD][];
string[][] CubeWDFLCN = new string[NumWD][];
string[][] strTableNamesAndKeys = new string[MeasureNum* NumWD][];
for (int i = 0; i < NumWD; i++)
{
CubeWDFL[i] = CubeWD[i].Split(',');
CubeWDFLCN[i] = CubeWDZ[i].Split(',');
CubeWDTableAndKey[i] = new string[2];
CubeWDTableAndKey[i][0] = CubeWDFL[i][0];
CubeWDTableAndKey[i][1] = CubeWDFL[i][1];
for (int j = 0; j < MeasureNum; j++)
{
strTableNamesAndKeys[i * MeasureNum + j] = newstring[5];
strTableNamesAndKeys[i * MeasureNum + j][0] =CubeWDFL[i][0];
strTableNamesAndKeys[i * MeasureNum + j][1] =CubeWDFL[i][1];
strTableNamesAndKeys[i * MeasureNum + j][2] =strFactTableName[j];
strTableNamesAndKeys[i * MeasureNum + j][3] =CubeWDFL[i][1];
strTableNamesAndKeys[i * MeasureNum + j][4] =CubeWDFLCN[i][0];
}
}
Server objServer = new Server();
Database objDatabase = new Database();
RelationalDataSource objDataSource = newRelationalDataSource();
DataSourceView objDataSourceView = new DataSourceView();
DataSet objDataSet = new DataSet();
Dimension[] objDimensions = new Dimension[intDimensionTableCount];
//连接到 Analysis Services.
objServer = (Server)ConnectAnalysisServices(strServerName, strProviderName);
//ssas层创建数据仓库
objDatabase = (Database)CreateDatabase(objServer,strCubeDBName, strServerName, strProviderName);
//ssas层创建数据源
objDataSource = (RelationalDataSource)CreateDataSource(objServer, objDatabase, strCubeDataSourceName, strDBServerName, strDBName);
//ssas层创建数据视图
objDataSet = (DataSet)GenerateDWSchema(strDBServerName,strDBName, strFactTableName, strTableNamesAndKeys, intDimensionTableCount);
objDataSourceView = (DataSourceView)CreateDataSourceView(CubeWDFL, CubeDLWD, objDatabase, objDataSource, objDataSet,strCubeDataSourceViewName, strFactTableName);
//修改 DSV
ChangeDSV(objDataSourceView, tableandcon,strFactTableName);
//创建维度、属性、层次、成员属性对象
objDimensions = (Dimension[])CreateDimension(NumDLWD,NumWD, CubeDLWD, CubeWDFL, CubeDLWDCN, CubeWDFLCN, strFactTableName,strFactTableNameCN, objDatabase, objDataSourceView, strTableNamesAndKeys,intDimensionTableCount, intFactTableCount);
//创建多维数据集,量度和分配对象
CreateCube(objDatabase, objDataSourceView, objDataSource,objDimensions, strFactTableName, strTableNamesAndKeys,intDimensionTableCount, intFactTableCount, intMeasureNum, strMeasureEN,strMeasureCN, NumDLWD, NumWD, CubeDLWDCN, CubeWDFLCN, strFactTableNameCN,tableandcon, CubeWDTableAndKey);
objDatabase.Process(ProcessType.ProcessFull);
}
///AnalysisServices 连接
private static object ConnectAnalysisServices(stringstrDBServerName, string strProviderName)
{
Server objServer = new Server();
string strConnection = "Data Source=" + strDBServerName +";Provider=" + strProviderName + ";";
//Disconnect from current connection if it's currentlyconnected.
if (objServer.Connected)
objServer.Disconnect();
else
objServer.Connect(strConnection);
return objServer;
}
通过上面具体实施方式,所述技术领域的技术人员可容易的实现本发明。但是应当理解,本发明并不限于上述的具体实施方式。在公开的实施方式的基础上,所述技术领域的技术人员可任意组合不同的技术特征,从而实现不同的技术方案。
除说明书所述的技术特征外,均为本专业技术人员的已知技术。