public List <YearCategory> Getyearcategory()
{
List <YearCategory> Year = new List <YearCategory>();
AdomdConnection conn = new AdomdConnection(
"Data Source=LAPTOP-ED7T3RSE\\ETELLERANDET;Initial Catalog=Charlie_BI_AnalysisProject;");
conn.Open();
string commandText = @"SELECT {[Dim Product].[Hierarchy].[Main Category]} ON COLUMNS, NONEMPTY({[Dim Date].[Hierarchy].[Year]}) ON ROWS FROM [Charlie BI F Club] WHERE [Measures].[Fact Sale Count]";
AdomdCommand cmd = new AdomdCommand(commandText, conn);
AdomdDataReader dr = cmd.ExecuteReader();
foreach (var item in dr)
{
YearCategory TempYear = new YearCategory();
TempYear.year = Convert.ToString(item[0]);
TempYear.food = Convert.ToString(item[1]);
TempYear.nonFood = Convert.ToString(item[2]);
TempYear.unknown = Convert.ToString(item[3]);
Year.Add(TempYear);
}
dr.Close();
conn.Close();
return(Year);
}
/// <summary>
/// Возвращает список кубов
/// </summary>
/// <returns></returns>
public Dictionary <String, CubeDefInfo> GetCubes()
{
try
{
System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Getting Cubes List Started", DateTime.Now.ToString());
Dictionary <String, CubeDefInfo> list = new Dictionary <String, CubeDefInfo>();
AdomdConnection conn = GetConnection();
foreach (CubeDef cube in conn.Cubes)
{
CubeDefInfo info = InfoHelper.CreateCubeInfo(cube);
if (info != null)
{
if (!list.ContainsKey(info.Name))
{
list.Add(info.Name, info);
}
}
}
return(list);
}
finally {
System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Getting Cubes List Completed", DateTime.Now.ToString());
}
}
public object GetMaxVersion()
{
var ids = new SSASIdentifiers(_input, _output);
var versionField = _output.Entity.GetVersionField();
if (versionField == null)
{
return(null);
}
if (_output.Process.Mode == "init")
{
return(null);
}
object result = null;
using (AdomdConnection conn = new AdomdConnection($"Data Source={_output.Connection.Server};Catalog={ids.DatabaseId}")) {
conn.Open();
var mdx = $"select [MEASURES].[{ids.VersionId}] ON COLUMNS FROM [{ids.CubeId}]";
using (var cmd = new AdomdCommand(mdx, conn)) {
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
result = reader[0];
}
reader.Close();
}
}
conn.Close();
}
return(result);
}
private void RunSSAS(object sender, EventArgs e)
{
//i don't think Dataset is in the Analysis Services directives
DataSet ds = new DataSet();
// provider is the constant olap. datasource is the same server name you provide for Mgmt Studio or localhost
// initial catalog is tricky and important. It is not a standard ms sql database you see in Management Studio,
// even if your cube was create with tables from a particular database.
// the only place I was able to see "initial catalog" value was a File -> Open -> Analysis Services Database in 2012 Management Studio
// it was also the name of the VS2010 solution I used to create the cube.
AdomdConnection myconnect = new AdomdConnection(@"provider=olap;initial catalog=GLCubeThree;datasource=localhost");
AdomdDataAdapter mycommand = new AdomdDataAdapter();
mycommand.SelectCommand = new AdomdCommand();
mycommand.SelectCommand.Connection = myconnect;
// this query was created by the "Browser" you see for an Analysis Services project
// if you poke around the icons on the browser table the Design Mode icon will give you the cube query
// I think it's an MDX query, threre are also xml queries you can run with adomd
mycommand.SelectCommand.CommandText = "SELECT NON EMPTY { [Measures].[Per Balance] } ON COLUMNS, NON EMPTY { ([Gltime].[Fisc Per].[Fisc Per].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Gltime].[Fisc Per].&[201301], [Gltime].[Fisc Per].&[201302], [Gltime].[Fisc Per].&[201307] } ) ON COLUMNS FROM [GL Cube]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS";
myconnect.Open();
mycommand.Fill(ds, "tbl");
myconnect.Close();
// the below assigns the results of the cube query to a dataGridView
// if you drag a dataGridView control to your pallete it will create exactly
// what you need for the line below to work.
// your project type has to be a Window Forms Applications
// this code shown here is in the default Form1.Designer.cs not Form1.cs
dataGridView1.DataSource = new DataView(ds.Tables[0]);
}
public HttpResponseMessage GetItemsByDimension(string dim, string order)
{
string WITH = @"
WITH
SET [OrderDimension] AS
NONEMPTY(
ORDER(
{0}.CHILDREN,
{0}.CURRENTMEMBER.MEMBER_NAME, " + order +
@")
)
";
string COLUMNS = @"
NON EMPTY
{
[Measures].[Ventas]
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
[OrderDimension]
ON ROWS
";
string CUBO_NAME = "[DHW Northwind]";
WITH = string.Format(WITH, dim);
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
Debug.Write(MDX_QUERY);
List <string> dimension = new List <string>();
dynamic result = new
{
datosDimension = dimension
};
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
cmd.Parameters.Add("Dimension", dim);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
dimension.Add(dr.GetString(0));
}
dr.Close();
}
}
}
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}
public DataSet ExecuteDataSet(String query, ref String sessionId)
{
if (!String.IsNullOrWhiteSpace(_connectionString))
{
using (AdomdConnection _connect = new AdomdConnection(_connectionString))
{
try
{
using (AdomdDataAdapter adapter = new AdomdDataAdapter(query, _connect))
{
DataSet ds = new DataSet();
adapter.Fill(ds);
foreach (DataTable tbl in ds.Tables)
{
foreach (DataColumn dc in tbl.Columns)
{
dc.ColumnName = Helpers.GetDimFieldShortName(dc.ColumnName);
}
}
return ds;
}
}
catch (Exception ex)
{
if (_connect.State == ConnectionState.Open)
{
_connect.Close();
}
throw ex;
}
}
}
return null;
}
public DataTable Adomd()
{
//连接的字符串
string conStr = "provider=msolap ;Integrated Security =SSPI ;Data Source= localhost ;Catalog =Scal B2CReport Mdx ;";
//创建个连接对象
AdomdConnection con = new AdomdConnection();
con.ConnectionString = conStr;
con.Open();
// 创建个命令
AdomdCommand cmm = con.CreateCommand();
cmm.CommandText = @"select
{[Measures].[订票数量],
[Measures].[Add Fare],[Measures].[Par Price]} on columns,
{[Sale Sta View Time].[Create Day].[2011-12-01]:[Sale Sta View Time].[Create Day].[2011-12-31]} on rows
from [SCAL3 Test]
where
{
{[Sale Sta View Generic].[Is Member].&[1]}
}";
//执行命令返回单元集合
CellSet result = cmm.ExecuteCellSet();
DataTable table = CellSetToTable(result);
con.Close();
return table;
}
protected virtual AdomdConnection GetConnection()
{
AdomdConnection conn = new AdomdConnection(Connection.ConnectionString);
conn.Open();
return(conn);
}
//Below subroutine will return a data table with results. It takes the DB connection string and query as input
protected System.Data.DataTable GetDataTable(string ConnString, string query)
{
System.Data.DataTable dtOlap = new System.Data.DataTable();
//Connect to Analysis Server
AdomdConnection conn = new AdomdConnection(ConnString);
System.Diagnostics.Debug.WriteLine(conn.ConnectionString);
try
{
conn.Open();
//Create adomd command using connection and MDX query
AdomdCommand cmd = new AdomdCommand(query, conn);
AdomdDataAdapter adr = new AdomdDataAdapter(cmd);
adr.Fill(dtOlap);
}
catch (InvalidCastException e)
{
Response.Write("Access denied on " + ConnString);
}
finally
{
//Close DB Connection
conn.Close();
}
return dtOlap;
}
public DimensionInfo GetDimensionToHierarchy(String cubeName, String hierarchyUniqueName)
{
try
{
System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Dimension To Hierarchy '{1}' Started \r\n cubeName: '{2}' ",
DateTime.Now.ToString(), hierarchyUniqueName, cubeName);
if (!String.IsNullOrEmpty(hierarchyUniqueName) && !String.IsNullOrEmpty(cubeName))
{
AdomdConnection conn = GetConnection();
CubeDef cube = FindCube(cubeName);
if (cube != null)
{
foreach (Dimension dim in cube.Dimensions)
{
foreach (Hierarchy hierarchy in dim.Hierarchies)
{
if (hierarchy.UniqueName.ToLower() == hierarchyUniqueName.ToLower())
{
return(InfoHelper.CreateDimensionInfo(dim));
}
}
}
}
}
return(null);
}
finally {
System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Dimension To Hierarchy Completed \r\n", DateTime.Now.ToString());
}
}
public Dictionary <String, HierarchyInfo> GetHierarchies(String cubeName, string dimensionUniqueName)
{
try
{
System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Hierarchies Started \r\n cubeName: '{1}' \r\n dimensionUniqueName: '{2}'",
DateTime.Now.ToString(), cubeName, dimensionUniqueName);
Dictionary <String, HierarchyInfo> list = new Dictionary <String, HierarchyInfo>();
AdomdConnection conn = GetConnection();
Dimension dim = FindDimension(cubeName, dimensionUniqueName);
if (dim != null)
{
foreach (Hierarchy hierarchy in dim.Hierarchies)
{
HierarchyInfo info = InfoHelper.CreateHierarchyInfo(hierarchy);
if (info != null)
{
if (!list.ContainsKey(info.UniqueName))
{
list.Add(info.UniqueName, info);
}
}
}
}
return(list);
}
finally {
System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Hierarchies Completed \r\n", DateTime.Now.ToString());
}
}
public Dictionary <String, DimensionInfo> GetDimensions(string cubeName)
{
try
{
System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Dimensions List Started \r\n cubeName: '{1}' ",
DateTime.Now.ToString(), cubeName);
Dictionary <String, DimensionInfo> list = new Dictionary <String, DimensionInfo>();
AdomdConnection conn = GetConnection();
CubeDef cube = FindCube(cubeName);
if (cube != null)
{
foreach (Dimension dim in cube.Dimensions)
{
DimensionInfo info = InfoHelper.CreateDimensionInfo(dim);
if (info != null)
{
if (!list.ContainsKey(info.UniqueName))
{
list.Add(info.UniqueName, info);
}
}
}
}
return(list);
}
finally {
System.Diagnostics.Trace.TraceInformation("{0} Ranet.Olap.Core.Providers.OlapMetadataProvider Get Dimensions List Completed \r\n ",
DateTime.Now.ToString(), cubeName);
}
}
public virtual CellSet ExecuteCellSet(out float elapsedSec)
{
// Open the connection
using (var connection = new AdomdConnection())
{
var connectionString = command.Connection.ConnectionString;
try
{ connection.ConnectionString = connectionString; }
catch (ArgumentException ex)
{ throw new ConnectionException(ex, connectionString); }
//TODO
//try
// {connection.Open();}
//catch (AdomdException ex)
// {throw new ConnectionException(ex);}
Trace.WriteLineIf(NBiTraceSwitch.TraceVerbose, command.CommandText);
foreach (AdomdParameter param in command.Parameters)
{
Trace.WriteLineIf(NBiTraceSwitch.TraceVerbose, string.Format("{0} => {1}", param.ParameterName, param.Value));
}
// capture time before execution
DateTime timeBefore = DateTime.Now;
CellSet cellSet = null;
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
cellSet = command.ExecuteCellSet();
}
catch (AdomdConnectionException ex)
{
throw new ConnectionException(ex, connectionString);
}
catch (AdomdErrorResponseException ex)
{
throw new ConnectionException(ex, connectionString);
}
finally
{
if (command.Connection.State == ConnectionState.Open)
{
command.Connection.Close();
}
}
// capture time after execution
DateTime timeAfter = DateTime.Now;
// setting query runtime
elapsedSec = (float)timeAfter.Subtract(timeBefore).TotalSeconds;
Trace.WriteLineIf(NBiTraceSwitch.TraceInfo, string.Format("Time needed to execute query [ADOMD]: {0}", timeAfter.Subtract(timeBefore).ToString(@"d\d\.hh\h\:mm\m\:ss\s\ \+fff\m\s")));
return(cellSet);
}
}
public static ICollection<string> ExecuteMdxCommand(string command, string connectionString, int index)
{
Logger.Debug(CultureInfo.CurrentCulture, "Send mdx query.");
Logger.Debug(CultureInfo.CurrentCulture, "Query: {0}", command);
Logger.Debug(CultureInfo.CurrentCulture, "AS connection string: {0}", connectionString);
Logger.Debug(CultureInfo.CurrentCulture, "Index: {0}", index);
var resultList = new List<string>();
using (var connection = new AdomdConnection(connectionString))
{
connection.Open();
using (var mdxCommand = new AdomdCommand(command, connection))
{
using (var mdxReader = mdxCommand.ExecuteReader())
{
while (mdxReader.Read())
{
////getName - column name
resultList.Add(mdxReader[index].ToString());
}
}
}
return resultList;
}
}
public string GetCubeInformation()
{
StringBuilder cubeInformation = new StringBuilder();
AdomdConnection conn = new AdomdConnection(ConnString);
conn.Open();
//Cube objects are CubeDef here
foreach (CubeDef cube in conn.Cubes)
{
if (cube.Name.StartsWith('$'))
{
continue;
}
cubeInformation.Append("Cube Name: " + cube.Name + '\n');
cubeInformation.Append("Cube KPIs: " + cube.Kpis.Count + '\n');
cubeInformation.Append("Cube Measures: " + cube.Measures.Count + '\n');
cubeInformation.Append("Updated at " + cube.LastUpdated + '\n' + "Dimensions: " + '\n');
foreach (Dimension dim in cube.Dimensions)
{
cubeInformation.AppendLine(dim.Name);
}
cubeInformation.Append("\n\n");
}
conn.Close();
return(cubeInformation.ToString());
}
public string GetHierarchies(int cubeId, int dimensionId)
{
using (AdomdConnection mdConn = new AdomdConnection())
{
mdConn.ConnectionString = "provider=msolap;Data Source=V1LGORPC\\ASMAIN;initial catalog=AdventureWorksDW2014Multidimensional-EE;";
mdConn.Open();
List <CubeDef> realCubeList = Utilities.Utilities.GetRealCubeList(mdConn.Cubes);
CubeDef cubeDef = realCubeList[cubeId];
Microsoft.AnalysisServices.AdomdClient.Dimension dimension = cubeDef.Dimensions[dimensionId];
List <Models.Hierarchy> hierarchyList = new List <Models.Hierarchy>();
foreach (Microsoft.AnalysisServices.AdomdClient.Hierarchy hierarchy in dimension.Hierarchies)
{
Models.Hierarchy newHierarchy = new Models.Hierarchy(hierarchy);
hierarchyList.Add(newHierarchy);
}
return(JsonConvert.SerializeObject(hierarchyList, Formatting.Indented));
}
}
public List <Day> Getdays()
{
List <Day> Day = new List <Day>();
AdomdConnection conn = new AdomdConnection(
"Data Source=.;Initial Catalog=Charlie_BI_AnalysisProject;");
conn.Open();
string commandText = @"SELECT {[Measures].[Fact Sale Count] } ON COLUMNS, NonEmpty({[Dim Date].[Hierarchy].[Day Of Month]}) ON ROWS FROM[Charlie BI F Club]";
AdomdCommand cmd = new AdomdCommand(commandText, conn);
AdomdDataReader dr = cmd.ExecuteReader();
foreach (var item in dr)
{
Day Tempday = new Day();
Tempday.month = Convert.ToString(item[1]);
Tempday.day = Convert.ToString(item[2]);
Tempday.amount = Convert.ToString(item[3]);
Day.Add(Tempday);
}
dr.Close();
conn.Close();
return(Day);
}
//Below subroutine will return a data table with results. It takes the DB connection string and query as input
protected System.Data.DataTable GetDataTable(string ConnString, string query)
{
System.Data.DataTable dtOlap = new System.Data.DataTable();
//Connect to Analysis Server
AdomdConnection conn = new AdomdConnection(ConnString);
System.Diagnostics.Debug.WriteLine(conn.ConnectionString);
try
{
conn.Open();
//Create adomd command using connection and MDX query
AdomdCommand cmd = new AdomdCommand(query, conn);
AdomdDataAdapter adr = new AdomdDataAdapter(cmd);
adr.Fill(dtOlap);
}
catch (InvalidCastException e)
{
Response.Write("Access denied on " + ConnString);
}
finally
{
//Close DB Connection
conn.Close();
}
return(dtOlap);
}
static void Main(string[] args)
{
// prepare adomd connection
using (AdomdConnection mdConn = new AdomdConnection())
{
mdConn.ConnectionString = "provider=msolap;Data Source=(local);initial catalog=HabraCube;";
mdConn.Open();//new comment
AdomdCommand mdCommand = mdConn.CreateCommand();
mdCommand.CommandText = "SELECT {[Measures].[Vote], [Measures].[Votes Count]} ON COLUMNS, [Dim Time].[Month Name].MEMBERS ON ROWS FROM [Habra DW]"; // << MDX Query // work with CellSet
CellSet cs = mdCommand.ExecuteCellSet(); // our method supports only 2-Axes CellSets
if (cs.Axes.Count != 2) return;
TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples;
TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples; // output column headers
Console.Write("{0,-12}", "Item");
for (int col = 0; col < tuplesOnColumns.Count; col++)
{
Console.Write("{0,-12}", tuplesOnColumns[col].Members[0].Caption);
}
Console.WriteLine(); // output rows
for (int row = 0; row < tuplesOnRows.Count; row++)
{
Console.Write("{0,-12}", tuplesOnRows[row].Members[0].Caption); // fill columns
for (int col = 0; col < tuplesOnColumns.Count; col++)
{ Console.Write("{0,-12}", cs.Cells[col, row].Value); } Console.WriteLine();
}
Console.ReadLine();
}
}
static void ExecuteDaxQuery()
{
// DAX query to be submitted totabuar database engine
String query = @"
EVALUATE
SUMMARIZECOLUMNS(
//GROUP BY
Customers[State],
//FILTER BY
TREATAS( {""Western Region""} , 'Customers'[Sales Region] ) ,
// MEASURES
""Sales Revenue"" , SUM(Sales[SalesAmount]) ,
""Units Sold"" , SUM(Sales[Quantity])
)
";
AdomdConnection adomdConnection = new AdomdConnection(connectString);
adomdConnection.Open();
AdomdCommand adomdCommand = new AdomdCommand(query, adomdConnection);
AdomdDataReader reader = adomdCommand.ExecuteReader();
ConvertReaderToCsv(reader);
reader.Dispose();
adomdConnection.Close();
}
static public List <string> getAllMeasures(string datasource, string catalog, string cube)
{
List <string> measureNames = new List <string>();
string connectionString = string.Format(Constants.dataSource, datasource, catalog);
using (AdomdConnection conn = new AdomdConnection(connectionString))
{
conn.Open();
using (DataSet measures = conn.GetSchemaDataSet(Constants.mdSchema, null, true))
{
foreach (DataTable dta in measures.Tables)
{
foreach (DataRow dro in dta.Rows)
{
foreach (DataColumn dco in dta.Columns)
{
// Get non-null unique measure names
if (dro[dco] != null && dco.ColumnName.ToString() == Constants.measureUniqueName)
{
measureNames.Add(dro[dco].ToString());
}
}
}
}
}
conn.Close();
}
return(measureNames);
}
private void btnRequest_Click(object sender, EventArgs e)
{
AdomdRestrictionCollection restrictions = new AdomdRestrictionCollection();
try
{
foreach (DataGridViewRow row in gridRestrictions.Rows)
{
if (row.Cells["AdomdRestrictionName"].Value != null && row.Cells["AdomdRestrictionName"].Value != DBNull.Value && !string.IsNullOrEmpty(row.Cells["AdomdRestrictionName"].Value.ToString()))
restrictions.Add(row.Cells["AdomdRestrictionName"].Value.ToString().Trim(), row.Cells["AdomdRestrictionValue"].Value.ToString().Trim());
}
AdomdConnection conn = new AdomdConnection(txtConnStr.Text);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
DataTable table = conn.GetSchemaDataSet(cboxRequestType.Text, restrictions).Tables[0];
gridResults.DataSource = table;
}
catch (Exception ex)
{
this.ShowMessage(ex);
}
}
public List <Year> GetYear()
{
List <Year> Year = new List <Year>();
AdomdConnection conn = new AdomdConnection(
"Data Source=.;Initial Catalog=Charlie_BI_AnalysisProject;");
conn.Open();
string commandText = @"SELECT {[Measures].[Fact Sale Count] } ON COLUMNS, NONEMPTY({[Dim Date].[Hierarchy].[Year]} ) ON ROWS FROM[Charlie BI F Club]";
AdomdCommand cmd = new AdomdCommand(commandText, conn);
AdomdDataReader dr = cmd.ExecuteReader();
foreach (var item in dr)
{
Year Tempyear = new Year();
Tempyear.year = Convert.ToString(item[0]);
Tempyear.amount = Convert.ToString(item[1]);
Year.Add(Tempyear);
}
dr.Close();
conn.Close();
return(Year);
}
/// <summary>
/// 根据mdx语句、连接字符串
/// </summary>
/// <param name="strMdx"></param>
/// <param name="ConnectionStringName"></param>
/// <returns></returns>
public DataTable GetDataByCon(string strMdx, string ConStr)
{
DataTable dt = new DataTable();
try
{
using (AdomdConnection _connection = new AdomdConnection(ConStr))
{
if (_connection != null)
{
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
AdomdCommand cmd = _connection.CreateCommand();
cmd.CommandText = strMdx;
var executexml = cmd.ExecuteXmlReader();
CellSet cellset = CellSet.LoadXml(executexml);
//_connection.GetSchemaDataSet
_connection.Close();
dt = ToDataTable(cellset);
}
}
return(dt);
}
catch (Exception ex)
{
throw ex;
}
}
public static ICollection <string> ExecuteMdxCommand(string command, string connectionString, int index)
{
Logger.Debug(CultureInfo.CurrentCulture, "Send mdx query.");
Logger.Debug(CultureInfo.CurrentCulture, "Query: {0}", command);
Logger.Debug(CultureInfo.CurrentCulture, "AS connection string: {0}", connectionString);
Logger.Debug(CultureInfo.CurrentCulture, "Index: {0}", index);
var resultList = new List <string>();
using (var connection = new AdomdConnection(connectionString))
{
connection.Open();
using (var mdxCommand = new AdomdCommand(command, connection))
{
using (var mdxReader = mdxCommand.ExecuteReader())
{
while (mdxReader.Read())
{
////getName - column name
resultList.Add(mdxReader[index].ToString());
}
}
}
return(resultList);
}
}
/// <summary>
/// 建立连接
/// </summary>
/// <param name="connection">AdomdConnection对象的实例</param>
/// <param name="connectionString">连接字符串</param>
public void Connect(ref AdomdConnection connection, string connectionString)
{
if (connectionString == "")
{
throw new ArgumentNullException("connectionString", "The connection string is not valid.");
}
// Ensure an AdomdConnection object exists and that its ConnectionString property is set.
if (connection == null)
{
connection = new AdomdConnection(connectionString);
}
else
{
Disconnect(ref connection, false);
connection.ConnectionString = connectionString;
}
try
{
connection.Open();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取OLAP数据库。
/// </summary>
/// <param name="connection">AdomdConnection对象的实例</param>
/// <param name="connectionString">连接字符串</param>
/// <returns></returns>
public DataTable GetSchemaDataSet_Catalogs(ref AdomdConnection connection, string connectionString)
{
bool connected = true; //判断connection在调用此函数时，是否已经处于连接状态
DataTable objTable = new DataTable();
try
{
// Check if a valid connection was provided.
if (IsConnected(ref connection) == false)
{
//如果连接不存在，则建立连接
Connect(ref connection, connectionString);
connected = false; //更改connection为未连接状态。
}
objTable = connection.GetSchemaDataSet(AdomdSchemaGuid.Catalogs, null).Tables[0];
if (connected == false)
{
//关闭连接
Disconnect(ref connection, false);
}
}
catch (Exception err)
{
throw err;
}
return(objTable);
}
public static AdomdConnection GetConnection(string connectionString, string sessionId)
{
HttpContext context = HttpContext.Current;
if (context != null && context.Session != null)
{
AdomdConnection conn = context.Session[connectionString] as AdomdConnection;
if (conn == null)
{
conn = new AdomdConnection(connectionString);
AdomdConnectionPool.OpenConnection(conn, sessionId);
context.Session[connectionString] = conn;
}
if (conn.State == ConnectionState.Closed)
{
AdomdConnectionPool.OpenConnection(conn, sessionId);
}
return conn;
}
var tmpConn = new AdomdConnection(connectionString);
AdomdConnectionPool.OpenConnection(tmpConn, sessionId);
return tmpConn;
}
public Connect()
{
InitializeComponent();
InitializeTextEditor();
moConn = new AdomdConnection();
}
/// <summary>
/// 获取立方体的维度
/// </summary>
/// <param name="connection">AdomdConnection对象的实例</param>
/// <param name="connectionString">连接字符串</param>
/// <param name="CubeName">立方体名称</param>
/// <returns></returns>
public string[] GetDimensions(ref AdomdConnection connection, string connectionString, string CubeName)
{
string[] strDimensions = null;
bool connected = true;
if (IsConnected(ref connection) == false)
{
try
{
Connect(ref connection, connection.ConnectionString);
connected = false;
}
catch (Exception err)
{
throw err;
}
}
int rowcount = connection.Cubes[CubeName].Dimensions.Count;
strDimensions = new string[rowcount];
for (int i = 0; i < rowcount; i++)
{
strDimensions[i] = connection.Cubes[CubeName].Dimensions[i].Caption.ToString();
}
if (connected == false)
{
Disconnect(ref connection, false);
}
return(strDimensions);
}
protected virtual string InquireFurtherAnalysisService(string connectionString)
{
try
{
var parsedMode = string.Empty;
using (var conn = new AdomdConnection(connectionString))
{
conn.Open();
var restrictions = new AdomdRestrictionCollection();
restrictions.Add(new AdomdRestriction("ObjectExpansion", "ReferenceOnly"));
var ds = conn.GetSchemaDataSet("DISCOVER_XML_METADATA", restrictions);
var xml = ds.Tables[0].Rows[0].ItemArray[0].ToString();
var doc = new XmlDocument();
doc.LoadXml(xml);
parsedMode = ParseXmlaResponse(doc);
}
switch (parsedMode)
{
case "Default": return Olap;
case "Multidimensional": return Olap;
case "SharePoint": return Tabular;
case "Tabular": return Tabular;
}
}
catch (Exception ex)
{
Trace.WriteLineIf(NBiTraceSwitch.TraceWarning,"Can't detect server mode for SSAS, using Olap. Initial message:" + ex.Message);
return Olap;
}
return Olap;
}
/// <summary>
/// 以connection的方式获取立方体
/// </summary>
/// <param name="connection">AdomdConnection对象的实例</param>
/// <param name="connectionString">连接字符串</param>
/// <returns></returns>
public string[] GetCubes(ref AdomdConnection connection, string connectionString)
{
string[] strCubesName = null;
bool connected = true; //判断connection是否已与数据库连接
if (IsConnected(ref connection) == false)
{
try
{
Connect(ref connection, connection.ConnectionString);
connected = false;
}
catch (Exception err)
{
throw err;
}
}
int rowcount = connection.Cubes.Count;
strCubesName = new string[rowcount];
for (int i = 0; i < rowcount; i++)
{
strCubesName[i] = connection.Cubes[i].Caption;
}
if (connected == false)
{
Disconnect(ref connection, false);
}
return(strCubesName);
}
internal MiningServiceCollectionInternal(AdomdConnection connection) : base(connection)
{
ListDictionary restrictions = new ListDictionary();
ObjectMetadataCache objectCache = new ObjectMetadataCache(connection, InternalObjectType.InternalTypeMiningService, MiningServiceCollectionInternal.schemaName, restrictions);
base.Initialize(objectCache);
}
/// <summary>
/// 通过SchemaDataSet的方式获取制定立方体的维度
/// </summary>
/// <param name="connection">AdomdConnection对象的实例</param>
/// <param name="connectionString">连接字符串</param>
/// <returns></returns>
public string[] GetSchemaDataSet_Dimensions(ref AdomdConnection connection, string connectionString, string cubeName)
{
string[] strDimensions = null;
bool connected = true; //判断connection是否已与数据库连接
DataTable objTable = new DataTable();
if (IsConnected(ref connection) == false)
{
try
{
Connect(ref connection, connectionString);
connected = false;
}
catch (Exception err)
{
throw err;
}
}
string[] strRestriction = new string[] { null, null, cubeName, null, null };
objTable = connection.GetSchemaDataSet(AdomdSchemaGuid.Dimensions, strRestriction).Tables[0];
if (connected == false)
{
Disconnect(ref connection, false);
}
strDimensions = new string[objTable.Rows.Count];
int rowcount = 0;
foreach (DataRow tempRow in objTable.Rows)
{
strDimensions[rowcount] = tempRow["DIMENSION_NAME"].ToString();
rowcount++;
}
return(strDimensions);
}
protected void Button1_Click(object sender, EventArgs e)
{
//الاتصال مع قاعدة البيانات
AdomdConnection conn = new AdomdConnection();
conn.ConnectionString = "Data Source=.;Catalog=AnalysisDB";
conn.Open();
// Command
AdomdCommand cmd = new AdomdCommand();
cmd.Connection = conn;
//DMX استعلام
cmd.CommandText = String.Format(@"select Predict ([Product Name]) from [Customer_ProductsMM]
natural prediction join
(select '{0}' as [Age],
'{1}' as [Cars],
'{2}' as [Children],
'{3}' as [Commute Distance],
'{4}' as [Education],
'{5}' as [Gender],
'{6}' as [Home Owner],
'{7}' as [Income],
'{8}' as [Marital Status],
'{9}' as [Occupation],
'{10}' as [Region]) as t",
txtAge.Text, txtcar.Text, txtChildren.Text, DRLCommuteD.SelectedItem, DRLeducation.SelectedItem, RBLGender.SelectedItem,
RBLHomeOwner.SelectedItem, txtIncome.Text, DRLMaritalst.SelectedItem, DRLOccupation.SelectedItem, DRLRegion.SelectedItem);
//إظهار النتيجة
AdomdDataReader adr = cmd.ExecuteReader();
while (adr.Read())
{
txtresult.Text = adr.GetString(0);
}
}
/// <summary>
/// Execute MDX query and populate model with results
/// </summary>
public void GetData()
{
DataSet ds = new DataSet();
using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2"))
{
conn.Open();
using (AdomdCommand cmd = new AdomdCommand(CommandText(), conn))
{
AdomdDataAdapter adapter = new AdomdDataAdapter(cmd);
adapter.Fill(ds);
}
conn.Close();
}
// Single query returns one table
DataTable dt = ds.Tables[0];
// read column names to use to select correct fields for data
// TBD Inspect here to find column names for pivoted Dayname-bikes
Dictionary <string, int> colnbr = new Dictionary <string, int>();
foreach (DataColumn dc in dt.Columns)
{
colnbr.Add(dc.ColumnName, dc.Ordinal);
}
foreach (DataRow dr in dt.Rows)
{
TripByCatTimeDistRow oneresult = new TripByCatTimeDistRow(dr, colnbr);
Add(oneresult);
}
}
public static List <string> getAllAttributes(string datasource, string catalog, string cube)
{
List <string> attributeNames = new List <string>();
string connectionString = string.Format(Constants.dataSource, datasource, catalog);
using (AdomdConnection conn = new AdomdConnection(connectionString))
{
conn.Open();
using (DataSet attributes = conn.GetSchemaDataSet(Constants.mdSchemaLevels, null, true))
{
foreach (DataTable dta in attributes.Tables)
{
foreach (DataRow dro in dta.Rows)
{
if (Convert.ToInt32((dro.ItemArray[9].ToString())) != 0) // Removes levels of type (All)
{
foreach (DataColumn dco in dta.Columns)
{
// Get non-null unique names
if (dro[dco] != null && dco.ColumnName.ToString() == Constants.levelUniqueName)
{
attributeNames.Add(dro[dco].ToString());
}
}
}
}
}
}
conn.Close();
}
return(attributeNames);
}
public async Task <string> getData(string query)
{
try
{
var token = await ADALTokenHelper.GetAppOnlyAccessToken(domain, $"https://{ssasUrl}", clientId, clientSecret);
var connectionString = GetConn(ssasUrl, token);
var ssasConnection = new AdomdConnection(connectionString);
ssasConnection.Open();
var cmd = new AdomdCommand(query)
{
Connection = ssasConnection
};
using (var reader = cmd.ExecuteXmlReader())
{
var value = reader.ReadOuterXml();
return(value);
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <Members> Getmembers()
{
List <Members> members = new List <Members>();
AdomdConnection conn = new AdomdConnection(
"Data Source=LAPTOP-ED7T3RSE\\ETELLERANDET;Initial Catalog=Charlie_BI_AnalysisProject;");
conn.Open();
string commandText = @"SELECT {[Measures].[Fact Sale Count]} ON COLUMNS, NONEMPTY({[Dim Member].[Member ID].[Member ID]}) ON ROWS FROM [Charlie BI F Club]";
AdomdCommand cmd = new AdomdCommand(commandText, conn);
AdomdDataReader dr = cmd.ExecuteReader();
foreach (var item in dr)
{
Members member = new Members();
member.member = Convert.ToString(item[0]);
member.Amount = Convert.ToString(item[1]);
members.Add(member);
}
dr.Close();
conn.Close();
return(members);
}
/// <summary>
/// Initializes a new instance of the AdomdConnectionWrapper class by using the specified command.
/// </summary>
/// <param name="connection">The AdomdConnection to encapsulate.</param>
public AdomdConnectionWrapper(AdomdConnection connection)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
_innerConnection = connection;
}
public override void PreExecute()
{
base.PreExecute();
adoMdConn = new AdomdConnection("Data Source=WHQWSSASQRY1;Catalog=Tyson Analytics");
adoMdConn.Open();
AdomdCommand cmd = new AdomdCommand(Variables.qryCube, adoMdConn);
dataReader = cmd.ExecuteReader();
}
public CubeOperate(String adomdConnectionString)
{
this.ConnectionString = adomdConnectionString;
Conn = new AdomdConnection(adomdConnectionString);
Conn.Open();
CubeDefs = Conn.Cubes.Cast<CubeDef>().ToList();
Cubes = GetCubes();
Dimensions = GetDimensions();
}
public void Execute_AdventureWorksDimensions_ListStructureContainingNotMeasure()
{
var conn = new AdomdConnection(ConnectionStringReader.GetAdomd());
var factory = new OlapStructureDiscoveryFactory(conn);
var cmd = factory.Instantiate(Target.Dimensions, TargetType.Object,
new CaptionFilter[] {
new CaptionFilter(Target.Perspectives, "Adventure Works")
});
var structs = cmd.Execute();
Assert.That(structs, Is.Not.Contains("Measures"));
}
public static void Run()
{
// open connection
// string connStr="Data Source=localhost;Initial Catalog=Foodmart 2000;";
string connStr="Data Source=10.3.0.247;Initial Catalog=Adventure Works DW Standard Edition;";
// string connStr="Data Source=http://localhost/xmla/msxisapi.dll;Initial Catalog=Foodmart 2000;";
AdomdConnection conn = new AdomdConnection(connStr);
AdomdCommand cmd=null;
// execute mdx
string queryStr=
// @"select
// {Product.Members} on rows,
// {{Time.Members}} on columns
//from Sales
//";
@"WITH SET [Promotions_set] AS '{[Promotions].[All Promotions].[Bag Stuffers],[Promotions].[All Promotions].[Best Savings],[Promotions].[All Promotions].[Big Promo],[Promotions].[All Promotions].[Big Time Discounts],[Promotions].[All Promotions].[Big Time Savings],[Promotions].[All Promotions].[Bye Bye Baby],[Promotions].[All Promotions].[Cash Register Lottery],[Promotions].[All Promotions].[Coupon Spectacular],[Promotions].[All Promotions].[Dimes Off],[Promotions].[All Promotions].[Dollar Cutters],[Promotions].[All Promotions].[Dollar Days],[Promotions].[All Promotions].[Double Down Sale],
[Promotions].[All Promotions].[Double Your Savings],[Promotions].[All Promotions].[Fantastic Discounts],[Promotions].[All Promotions].[Free For All],[Promotions].[All Promotions].[Go For It],[Promotions].[All Promotions].[Green Light Days],[Promotions].[All Promotions].[Green Light Special],[Promotions].[All Promotions].[High Roller Savings],[Promotions].[All Promotions].[I Cant Believe It Sale],[Promotions].[All Promotions].[Money Grabbers],[Promotions].[All Promotions].[Money Savers],[Promotions].[All Promotions].[Mystery Sale],[Promotions].[All Promotions].[No Promotion],[Promotions].[All Promotions].[One Day Sale],[Promotions].[All Promotions].[Pick Your Savings],[Promotions].[All Promotions].[Price Cutters],[Promotions].[All Promotions].[Price Destroyers],[Promotions].[All Promotions].[Price Savers],[Promotions].[All Promotions].[Price Slashers],[Promotions].[All Promotions].[Price Smashers],[Promotions].[All Promotions].[Price Winners],[Promotions].[All Promotions].[Sale Winners],[Promotions].[All Promotions].[Sales Days],[Promotions].[All Promotions].[Sales Galore],[Promotions].[All Promotions].[Save-It Sale],[Promotions].[All Promotions].[Saving Days],[Promotions].[All Promotions].[Savings Galore],[Promotions].[All Promotions].[Shelf Clearing Days],
[Promotions].[All Promotions].[Shelf Emptiers],[Promotions].[All Promotions].[Super Duper Savers],[Promotions].[All Promotions].[Super Savers],[Promotions].[All Promotions].[Super Wallet Savers],[Promotions].[All Promotions].[Three for One],[Promotions].[All Promotions].[Tip Top Savings],[Promotions].[All Promotions].[Two Day Sale],[Promotions].[All Promotions].[Two for One],[Promotions].[All Promotions].[Unbeatable Price Savers],[Promotions].[All Promotions].[Wallet Savers],[Promotions].[All Promotions].[Weekend Markdown],[Promotions].[All Promotions].[You Save Days],[Promotions].[All Promotions]}'
SET [Promotions_set_wcalc] AS '{{[Promotions_set]}}' SET [Time_set] AS '{[*SET Time.1997.Children*],[*SET Time.Q1.Children*],[*SET Time.Q2.Children*],[*SET Time.Q3.Children*],[*SET Time.Q4.Children*]}' SET [*SET Time.Q4.Children*] AS '{[Time].[1997].[Q4].Children}' SET [*SET Time.Q3.Children*] AS '{[Time].[1997].[Q3].Children}' SET [*SET Time.Q2.Children*] AS '{[Time].[1997].[Q2].Children}' SET [*SET Time.Q1.Children*] AS '{[Time].[1997].[Q1].Children}' SET [*SET Time.1997.Children*] AS '{[Time].[1997].Children}' SET [Time_set_wcalc] AS '{{[Time_set]}}' SET [Store Size in SQFT_set] AS '{[*SET Store Size in SQFT.All Store Size in SQFT.Children*]}' SET [*SET Store Size in SQFT.All Store Size in SQFT.Children*] AS '{[Store Size in SQFT].[All Store Size in SQFT].Children}' SET [Store Size in SQFT_set_wcalc] AS '{{[Store Size in SQFT_set]}}' SET [Product_set] AS '{[*SET Product.Drink.Children*],[*SET Product.Food.Children*],[*SET Product.Alcoholic Beverages.Children*],[*SET Product.Beverages.Children*],[*SET Product.Dairy.Children*],[*SET Product.Baked Goods.Children*],
[*SET Product.Baking Goods.Children*],[*SET Product.Breakfast Foods.Children*],[*SET Product.Canned Foods.Children*],[*SET Product.Canned Products.Children*],[*SET Product.Deli.Children*],[*SET Product.Eggs.Children*],[*SET Product.Frozen Foods.Children*],[*SET Product.Meat.Children*],[*SET Product.Produce.Children*],[*SET Product.Seafood.Children*],[*SET Product.Snack Foods.Children*],[*SET Product.Snacks.Children*],[*SET Product.Starchy Foods.Children*],[*SET Product.Non-Consumable.Children*],[*SET Product.Carousel.Children*],[*SET Product.Checkout.Children*],[*SET Product.Health and Hygiene.Children*],[*SET Product.Household.Children*],[*SET Product.Periodicals.Children*]}' SET [*SET Product.Periodicals.Children*] AS '{[Product].[All Products].[Non-Consumable].[Periodicals].Children}' SET [*SET Product.Household.Children*] AS '{[Product].[All Products].[Non-Consumable].[Household].Children}' SET [*SET Product.Health and Hygiene.Children*] AS '{[Product].[All Products].[Non-Consumable].[Health and Hygiene].Children}' SET [*SET Product.Checkout.Children*] AS '{[Product].[All Products].[Non-Consumable].[Checkout].Children}'
SET [*SET Product.Carousel.Children*] AS '{[Product].[All Products].[Non-Consumable].[Carousel].Children}' SET [*SET Product.Non-Consumable.Children*] AS '{[Product].[All Products].[Non-Consumable].Children}' SET [*SET Product.Starchy Foods.Children*] AS '{[Product].[All Products].[Food].[Starchy Foods].Children}' SET [*SET Product.Snacks.Children*] AS '{[Product].[All Products].[Food].[Snacks].Children}' SET [*SET Product.Snack Foods.Children*] AS '{[Product].[All Products].[Food].[Snack Foods].Children}' SET [*SET Product.Seafood.Children*] AS '{[Product].[All Products].[Food].[Seafood].Children}' SET [*SET Product.Produce.Children*] AS '{[Product].[All Products].[Food].[Produce].Children}' SET [*SET Product.Meat.Children*] AS '{[Product].[All Products].[Food].[Meat].Children}' SET [*SET Product.Frozen Foods.Children*] AS '{[Product].[All Products].[Food].[Frozen Foods].Children}' SET [*SET Product.Eggs.Children*] AS '{[Product].[All Products].[Food].[Eggs].Children}' SET [*SET Product.Deli.Children*] AS '{[Product].[All Products].[Food].[Deli].Children}' SET [*SET Product.Canned Products.Children*] AS '{[Product].[All Products].[Food].[Canned Products].Children}' SET [*SET Product.Canned Foods.Children*] AS '{[Product].[All Products].[Food].[Canned Foods].Children}'
SET [*SET Product.Breakfast Foods.Children*] AS '{[Product].[All Products].[Food].[Breakfast Foods].Children}' SET [*SET Product.Baking Goods.Children*] AS '{[Product].[All Products].[Food].[Baking Goods].Children}' SET [*SET Product.Baked Goods.Children*] AS '{[Product].[All Products].[Food].[Baked Goods].Children}' SET [*SET Product.Dairy.Children*] AS '{[Product].[All Products].[Food].[Dairy].Children}' SET [*SET Product.Beverages.Children*] AS '{[Product].[All Products].[Drink].[Beverages].Children}' SET [*SET Product.Alcoholic Beverages.Children*] AS '{[Product].[All Products].[Drink].[Alcoholic Beverages].Children}' SET [*SET Product.Food.Children*] AS '{[Product].[All Products].[Food].Children}' SET [*SET Product.Drink.Children*] AS '{[Product].[All Products].[Drink].Children}'
SET [Product_set_wcalc] AS '{{[Product_set]}}' MEMBER [Promotion Media].[*AGGREGATE*] AS 'AGGREGATE({[Promotion Media].[All Media].[Bulk Mail],[Promotion Media].[All Media].[Cash Register Handout],[Promotion Media].[All Media].[Daily Paper],[Promotion Media].[All Media].[Daily Paper, Radio],[Promotion Media].[All Media].[Daily Paper, Radio, TV],[Promotion Media].[All Media].[In-Store Coupon],[Promotion Media].[All Media].[No Media],[Promotion Media].[All Media].[Product Attachment],[Promotion Media].[All Media].[Radio],[Promotion Media].[All Media].[Street Handout],[Promotion Media].[All Media].[Sunday Paper],[Promotion Media].[All Media].[Sunday Paper, Radio],[Promotion Media].[All Media].[Sunday Paper, Radio, TV],[Promotion Media].[All Media].[TV]})' , SOLVE_ORDER=-100
MEMBER [Store Type].[*AGGREGATE*] AS 'AGGREGATE({[Store Type].[All Store Type].[Deluxe Supermarket],[Store Type].[All Store Type].[Gourmet Supermarket],[Store Type].[All Store Type].[HeadQuarters],[Store Type].[All Store Type].[Mid-Size Grocery],[Store Type].[All Store Type].[Small Grocery],[Store Type].[All Store Type].[Supermarket]})' , SOLVE_ORDER=-100 MEMBER [Yearly Income].[*AGGREGATE*] AS 'AGGREGATE({[Yearly Income].[All Yearly Income].[$10K - $30K],[Yearly Income].[All Yearly Income].[$110K - $130K],[Yearly Income].[All Yearly Income].[$130K - $150K],[Yearly Income].[All Yearly Income].[$150K +],[Yearly Income].[All Yearly Income].[$30K - $50K],[Yearly Income].[All Yearly Income].[$50K - $70K],[Yearly Income].[All Yearly Income].[$70K - $90K],[Yearly Income].[All Yearly Income].[$90K - $110K]})' , SOLVE_ORDER=-100 SELECT NON EMPTY HIERARCHIZE({{[Promotions_set_wcalc]}*{[Time_set_wcalc]}*{[Store Size in SQFT_set_wcalc]}}) ON Columns, NON EMPTY HIERARCHIZE({{[Product_set_wcalc]}}) ON Rows FROM [Sales] WHERE ([Customers].[All Customers],[Education Level].[All Education Level],[Gender].[All Gender],[Marital Status].[All Marital Status],[Measures].[Unit Sales],[Promotion Media].[*AGGREGATE*],[Store].[All Stores],[Store Type].[*AGGREGATE*],[Yearly Income].[*AGGREGATE*])";
try
{
conn.Open();
cmd=conn.CreateCommand();
_cmdList.Add(cmd);
cmd.CommandText=queryStr;
CellSet cst=cmd.ExecuteCellSet();
cst=null;
}
catch(Exception exc)
{
throw exc;
}
finally
{
if(cmd!=null)
_cmdList.Remove(cmd);
conn.Close();
conn.Dispose();
}
}
public void Execute_MeasureGroupSalesOrdersLinkedTo_ListStructureContainingCorrectCountOfElements()
{
var conn = new AdomdConnection(ConnectionStringReader.GetAdomd());
var factory = new OlapStructureDiscoveryFactory(conn);
var cmd = factory.Instantiate(Target.Dimensions, TargetType.Relation,
new CaptionFilter[] {
new CaptionFilter(Target.Perspectives, "Adventure Works")
, new CaptionFilter(Target.MeasureGroups, "Sales Orders")
});
var structs = cmd.Execute();
Assert.That(structs.Count(), Is.EqualTo(9));
}
public void Execute_DateDimensionWithHeighTeenHierarchies_ListStructureContainingCorrectCountOfElements()
{
var conn = new AdomdConnection(ConnectionStringReader.GetAdomd());
var factory = new OlapStructureDiscoveryFactory(conn);
var cmd = factory.Instantiate(Target.Hierarchies, TargetType.Object,
new CaptionFilter[] {
new CaptionFilter(Target.Perspectives, "Adventure Works"),
new CaptionFilter(Target.Dimensions, "Date")
});
var structs = cmd.Execute();
Assert.That(structs.Count(), Is.EqualTo(18));
}
public static bool TestADOMD()
{
try
{
AdomdConnection conn = new AdomdConnection(ConfigurationManager.ConnectionStrings["Cube"].ConnectionString);
conn.Open();
if (conn.State == ConnectionState.Open)
return true;
else
return false;
}
catch(Exception ex)
{
SystemHelper.LogEntry("Fail in DbHelper.cs\\TestADOMD(): " + ex.ToString() + "\n");
throw ex;
}
}
public ViewWeekofyearGivenDistanceDurationCategories GetData(string DistanceCategory, string DurationCategory)
{
ViewWeekofyearGivenDistanceDurationCategories model =
new ViewWeekofyearGivenDistanceDurationCategories(DistanceCategory, DurationCategory);
List<BikeTable> hierarchy = new List<BikeTable>();
hierarchy.Add(new TableWeekday());
hierarchy.Add(new TableSubscriber());
hierarchy.Add(new TableHour2());
string CommandText = @"SELECT
NON EMPTY { [Measures].[Bikes] } ON COLUMNS,
NON EMPTY { ([Time Table].[Nameofday].[Nameofday].ALLMEMBERS *
[Time Table].[Weekofyear].[Weekofyear].ALLMEMBERS *
[Subscribers].[Subscriber Info].[Subscriber Info].ALLMEMBERS *
[Time Table].[Hour2ofday].[Hour2ofday].ALLMEMBERS )
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( { [Direction].[Direction].&[A-B] } ) ON COLUMNS
FROM ( SELECT ( STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( STRTOSET(@TripCatTripCategory, CONSTRAINED) ) ON COLUMNS
FROM [Bikeshare])))
WHERE ( IIF( STRTOSET(@TripCatTripCategory, CONSTRAINED).Count = 1,
STRTOSET(@TripCatTripCategory, CONSTRAINED),
[TripCat].[Trip Category].currentmember ),
IIF( STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED).Count = 1,
STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED),
[Station Pair Distance].[Mile Categories].currentmember ),
[Direction].[Direction].&[A-B] )";
DataSet ds = new DataSet();
using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2"))
{
conn.Open();
using (AdomdCommand cmd = new AdomdCommand(CommandText, conn))
{
cmd.Parameters.Add(new AdomdParameter("StationPairDistanceMileCategories", DistanceCategory));
cmd.Parameters.Add(new AdomdParameter("TripCatTripCategory", DurationCategory));
AdomdDataAdapter adapter = new AdomdDataAdapter(cmd);
adapter.Fill(ds);
}
conn.Close();
}
PopulateModel(hierarchy, model, ds);
return model;
}
public static List<object> GetData(string command)
{
DataSet ds = new DataSet();
using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2"))
{
conn.Open();
using (AdomdCommand cmd = new AdomdCommand(command, conn))
{
AdomdDataAdapter adapter = new AdomdDataAdapter(cmd);
adapter.Fill(ds);
}
conn.Close();
}
return ConvertDataTableToObjectList(ds.Tables[0]);
}
public static DataTable ExecuteDataTable(AdomdConnection conn, string CommandText)
{
DataTable dt = null;
try
{
AdomdCommand command = new AdomdCommand();
SetCommand(conn, command, CommandText);
AdomdDataAdapter da = new AdomdDataAdapter(command);
dt = new DataTable();
da.Fill(dt);
}
catch (Exception ex)
{
throw;
}
return dt;
}
public static bool ASSPExists(AdomdConnection conn)
{
AdomdCommand cmd = new AdomdCommand();
cmd.CommandText = "call ASSP.AssemblyVersion()";
cmd.Connection = conn;
cmd.CommandTimeout = 0;
try
{
cmd.ExecuteNonQuery();
return true;
}
catch
{
return false;
}
}
protected AdomdCommand CreateCommand()
{
var conn = new AdomdConnection();
conn.ConnectionString = ConnectionString;
try
{
conn.Open();
}
catch (AdomdConnectionException ex)
{
throw new ConnectionException(ex, conn.ConnectionString);
}
var cmd = new AdomdCommand();
cmd.Connection = conn;
return cmd;
}
public void Disconnect()
{
try
{
Console.WriteLine("Testing Disconnections");
foreach (DictionaryEntry entry in connectionsStr)
{
AdomdConnection conn = new AdomdConnection((String)entry.Value);
conn.Open();
Assert.AreEqual(System.Data.ConnectionState.Open, conn.State);
conn.Close(true);
Assert.AreEqual(System.Data.ConnectionState.Closed, conn.State);
}
}
catch (Exception ex)
{
Assert.Fail(ex.ToString());
}
}
static void Main()
{
// Import MDX query from file
var query = new QueryImporter("../../../../LearningMDX.mdx");
string mdxQuery1 = query.FromFile("Query2");
using (AdomdConnection conn = new AdomdConnection(connectionString))
{
conn.Open();
using (AdomdCommand command = conn.CreateCommand())
{
command.CommandText = mdxQuery1;
CellSet cellSet1 = command.ExecuteCellSet();
}
}
}
public CubeStructure(String database, String cube, String host, Dictionary<int, string> layerDictionary, String sitesDim)
{
m_database = database;
m_cube = cube;
String connectionString = "Initial Catalog=" +database+"; Data Source="+ host +"; integrated security=sspi;";
m_conn = new AdomdConnection();
m_conn = new AdomdConnection(connectionString);
m_conn.Open();
m_xmlcubeschema = new XmlDataDocument();
m_output = new StreamWriter("C:\\inetpub\\wwwroot\\mattsmaps\\App_Code\\App_Data\\" + m_database + "." + m_cube + "." + "Report.txt");
m_sitesDim = sitesDim;
m_exclusionList = new ArrayList();
foreach (String s in layerDictionary.Values){
String tmpName = s.Substring(1, s.Substring(1, s.Length -1).IndexOf("."));
String attributeName = tmpName.Replace("]", "").Replace("[", "");
m_exclusionList.Add(attributeName);
}
generateReport();
}
string SetConnectionString(string argument)
{
try
{
var err = "Argument Schema must be ConnectionId=ConnectionString";
if (string.IsNullOrEmpty(argument))
throw new ArgumentException(err);
var ind = argument.IndexOf('=');
if (ind < 1)
throw new ArgumentException(err);
var connectionName = argument.Substring(0, ind);
var connectionString = argument.Substring(ind + 1);
if (string.IsNullOrEmpty(connectionName))
throw new ArgumentException(err);
if (string.IsNullOrEmpty(connectionString))
connectionString = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
using (var connection = new AdomdConnection(connectionString))
{
string s = "";
connection.Open();
foreach (var c in connection.Cubes)
{
s += c.Name;
}
}
this.Application[connectionName] = connectionString;
return connectionName+"="+connectionString;
}
catch (Exception E)
{
return E.ToString();
}
}
protected AdomdCommand CreateCommand()
{
var conn = new AdomdConnection();
//If connectionString is empty throw a ConnectionException
if (String.IsNullOrEmpty(ConnectionString))
throw new ConnectionException(new ArgumentNullException(), "No connectionString found.");
conn.ConnectionString = ConnectionString;
try
{
conn.Open();
}
catch (AdomdConnectionException ex)
{
throw new ConnectionException(ex, conn.ConnectionString);
}
var cmd = new AdomdCommand();
cmd.Connection = conn;
return cmd;
}
public TableTripCategories GetData()
{
TableTripCategories model = new TableTripCategories();
// hierarchy properly defined within report
List<BikeTable> hierarchy = new List<BikeTable>();
hierarchy.Add(new TableSubscriber());
hierarchy.Add(new TableHour2());
DataSet ds = new DataSet();
using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2"))
{
conn.Open();
using (AdomdCommand cmd = new AdomdCommand(CommandText(), conn))
{
AdomdDataAdapter adapter = new AdomdDataAdapter(cmd);
adapter.Fill(ds);
}
conn.Close();
}
PopulateModel(hierarchy, model, ds);
return (model);
}
public static CellSet ExecuteCellSet(AdomdConnection conn, string CommandText)
{
try
{
// Create a new Adomd command
AdomdCommand command = new AdomdCommand();
//Prepare the command
SetCommand(conn, command, CommandText);
//Execute the command
CellSet cst = command.ExecuteCellSet();
//var aa = cst.Axes[0].Set.Tuples[0].Members[0].GetChildren();
command.Parameters.Clear();
//var bb = cst.Axes[0].Set.Tuples[0].Members[0].GetChildren();
return cst;
}
catch (Exception ex)
{
throw;
}
}
public static int ExecuteNonQuery(AdomdConnection conn, string CommandText)
{
try
{
// Create a new Adomd command
AdomdCommand command = new AdomdCommand();
//Prepare the command
SetCommand(conn, command, CommandText);
//Execute the command
int val = command.ExecuteNonQuery();
command.Parameters.Clear();
return val;
}
catch (Exception ex)
{
throw;
}
}
