public void Open()
{
_adomdConn.Open();
ChangeDatabase(_adomdConn.Database);
CacheKeywords();
CacheFunctionGroups();
UpdateServerProperties();
// We do not cache DaxMetadata intentionally - it is saved manually, there is no need to read them every time
}
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 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;
}
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]);
}
//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();
}
}
public string Get(int id)
{
// Создаем соединение с Analysis Services
using (AdomdConnection mdConn = new AdomdConnection())
{
// Определяем строку подключения: указываем экземпляр Analysis Services и необходимую БД
mdConn.ConnectionString = "provider=msolap;Data Source=V1LGORPC\\ASMAIN;initial catalog=AdventureWorksDW2014Multidimensional-EE;";
// Открываем соединение
mdConn.Open();
// Создаем список кубов
List <Cube> cubeList = new List <Cube>();
// Проходим по всем кубам, полученным из Analysis Services
foreach (CubeDef cube in mdConn.Cubes)
{
// Пропускаем скрытые кубы
if (cube.Name.StartsWith('$'))
{
continue;
}
// Создаем новый объект Cube
Cube newCube = new Cube(cube);
// Добавляем его в список кубов
cubeList.Add(newCube);
}
// Возвращаем JSON-представление куба с номером id
return(JsonConvert.SerializeObject(cubeList[id], Formatting.Indented));
}
}
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>
/// Get an open connection to an Azure Analysis Services Model.
/// This connection uses an effective identity to manage the data that is retrieved, this customData should contain the customerId.
/// </summary>
/// <returns>An open Connection</returns>
public async Task <AdomdConnection> GetOpenAasConnectionAsync()
{
AdomdConnection aasConnection = null;
try
{
var clientId = _configuration["ADAuth:ClientId"];
var secret = _configuration["ADAuth:ClientSecret"];
var token = await new AasTokenManager().GetAccessTokenAsync(
clientId,
secret,
_configuration["ADAuth:TenantId"]);
var connStringFormat = _configuration["aas-model-connstring"];
var connString = connStringFormat.Replace("{accessToken}", token);
aasConnection = new AdomdConnection(connString);
aasConnection?.Open();
}
catch (Exception e)
{
Console.WriteLine(e);
CloseAasConnection(aasConnection);
throw;
}
return(aasConnection);
}
//8
public void ConnectToDB8()
{
AdomdConnection connection = new AdomdConnection(@"DataSource=" + serverName);
try
{
connection.Open();
connection.ChangeDatabase(DataBaseName8);
}
catch (Exception ex)
{
MessageBox.Show("Исключение в процессе соединения с базой данных: " + ex.Message);
}
try
{
AdomdDataAdapter adapter = new AdomdDataAdapter(@"select " + columns + " on columns, "
+ rows + " on rows FROM " + from + where, connection);
DataSet set = new DataSet();
adapter.Fill(set);
dataGridViewReport.DataSource = set.Tables[0];
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Исключение при формировании отчёта: " + ex.Message);
}
}
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();
}
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;
}
private void buildReport(string mdx, DataGridView dataGrid)
{
conn.Open();
conn.ChangeDatabase("ProjectAnalysis");
AdomdDataAdapter adapter = new AdomdDataAdapter(mdx, conn);
DataSet setin = new DataSet();
adapter.Fill(setin);
dataGrid.DataSource = setin.Tables[0];
foreach (DataColumn column in setin.Tables[0].Columns)
{
if (column.ColumnName.Contains("Дата"))
{
String columnName = column.ColumnName;
System.Text.RegularExpressions.Match dateMatch = System.Text.RegularExpressions.Regex.Match(columnName, @"(?<year>\d{4})\-(?<month>\d{2})\-(?<day>\d{2})");
Int32 year = Convert.ToInt32(dateMatch.Groups["year"].Value);
Int32 month = Convert.ToInt32(dateMatch.Groups["month"].Value);
Int32 day = Convert.ToInt32(dateMatch.Groups["day"].Value);
DateTime period = new DateTime(year, month, day);
column.ColumnName = period.ToString(DATEFORMAT);
}
}
conn.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);
}
/// <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 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 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;
}
}
private static void InteractiveLoginTest(string upn, string endPoint, string database)
{
using (var con = new AdomdConnection($"Data Source={endPoint};Initial Catalog={database};User Id={upn}"))
{
con.Open();
}
}
private static string GetDataFromAzureAnalysisService(string query)
{
//Grab the token
//Get servername from Azure Analysis Service (Overview) resource
//Format: asazure://.asazure.windows.net/
var serverName = "asazure://westus.asazure.windows.net/agghactice";
var token = GetAccessToken("https://westus.asazure.windows.net");
var connectionString = $"Provider=MSOLAP;Data Source={serverName};Initial Catalog=databasename;User ID=;Password={token};Persist Security Info=True;Impersonation Level=Impersonate";
DataTable _dt = new DataTable();
string JSONString_result = string.Empty;
try
{
//read data from AAS
AdomdConnection connection = new AdomdConnection(connectionString);
connection.Open();
using (AdomdDataAdapter ad = new AdomdDataAdapter(query, connection))
{
ad.Fill(_dt);
if (_dt.Rows.Count > 0)
{
JSONString_result = JsonConvert.SerializeObject(_dt);
}
}
}
catch (Exception ex)
{
string ss = ex.Message.ToString();
Console.WriteLine(ex.Message.ToString());
}
return(JSONString_result);
}
private void getBoughtToghtherMovies(String input, List <string> output)
{
String newInput = input.Trim();
AdomdConnection CON = new AdomdConnection(
"Data Source=.;Catalog=ADMF18");
CON.Open();
AdomdCommand COM = CON.CreateCommand();
string s = "SELECT Flattened PREDICT([Movies],5" + ") FROM [CustomersMM] NATURAL PREDICTION JOIN (SELECT (" + "Select '" + newInput + "' as [movie]" + ") AS [Movies]) As T";
COM.CommandText = s;
AdomdDataReader DR = COM.ExecuteReader();
while (DR.Read())
{
if (DR[0] != null)
{
output.Add(DR[0].ToString());
}
}
DR.Close();
CON.Close();
}
public void MakeConnection(string ConnectionString, SSASConnectionState state)
{
try
{
SSASConnection = new AdomdConnection
{
ConnectionString = ConnectionString
};
Srv.Connect(ConnectionString);
if ((int)Srv.ServerMode != (int)state)
{
throw new Exception("Wrong connection type! Server you've tried to connect is " + Srv.ServerMode.ToString());
}
SSASConnection.SessionID = Srv.SessionID;
SSASConnection.Open();
ConnectionState = state;
DatabaseName = SSASConnection.Database;
PrepareTrace();
}
catch (Exception e)
{
if (SSASConnection.State == System.Data.ConnectionState.Open)
{
SSASConnection.Close();
}
if (Srv.Connected)
{
Srv.Disconnect();
}
throw new Exception(e.Message, e);
}
}
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);
}
/// <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 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);
}
}
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 DmvExtractor(Dax.Metadata.Model daxModel, AdomdConnection connection, string serverName, string databaseName, string extractorApp, string extractorVersion)
{
Connection = connection;
Connection.Open();
// Validate databaseName
if (!CheckDatabaseNameCompatibilityLevel(ref databaseName, out int compatibilityLevel))
{
throw new ExtractorException(connection, databaseName);
}
AssemblyName tomExtractorAssemblyName = this.GetType().Assembly.GetName();
Version version = tomExtractorAssemblyName.Version;
// Create a DAX model if it is not provided in the constructor arguments
// This should be outsider the constructor, but we want to make sure the database name is
// validated before using other DMVs
DaxModel = daxModel ?? new Dax.Metadata.Model(tomExtractorAssemblyName.Name, version.ToString(), extractorApp, extractorVersion);
DaxModel.ServerName = new DaxName(serverName);
DaxModel.ModelName = new DaxName(databaseName);
DaxModel.CompatibilityLevel = compatibilityLevel;
// Update ExtractionDate
DaxModel.ExtractionDate = DateTime.UtcNow;
}
private string GetFullCubeList()
{
using (AdomdConnection mdConn = new AdomdConnection())
{
mdConn.ConnectionString = "provider=msolap;Data Source=V1LGORPC\\ASMAIN;initial catalog=AdventureWorksDW2014Multidimensional-EE;";
mdConn.Open();
List <Cube> cubeList = new List <Cube>();
foreach (CubeDef cube in mdConn.Cubes)
{
if (cube.Name.StartsWith('$'))
{
continue;
}
Cube newCube;
newCube = new Cube(cube);
cubeList.Add(newCube);
}
return(JsonConvert.SerializeObject(cubeList, Formatting.Indented));
}
}
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);
}
//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);
}
protected virtual AdomdConnection GetConnection()
{
AdomdConnection conn = new AdomdConnection(Connection.ConnectionString);
conn.Open();
return(conn);
}
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);
}
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);
}
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);
}
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);
}
}
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 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 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 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 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 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;
}
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 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());
}
}
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();
}
}
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);
}
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;
}
private string ClearCubeCache()
{
string cacheCleared = String.Empty; // an empty string will be returned if the method executes successfully, else the exception text will be returned for display
AdomdConnection cubeConnection = new AdomdConnection();
try
{
cubeConnection.ConnectionString = ConfigurationManager.ConnectionStrings["GrReportingCube"].ConnectionString;
cubeConnection.Open();
AdomdCommand cubeCommand = cubeConnection.CreateCommand();
cubeCommand.CommandType = CommandType.Text;
cubeCommand.CommandText = String.Format(@"<Batch xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">
<ClearCache>
<Object>
<DatabaseID>{0}</DatabaseID>
</Object>
</ClearCache>
</Batch>", cubeConnection.Database);
cubeCommand.Execute();
}
catch (Exception exception)
{
cacheCleared = exception.ToString();
}
finally
{
if (cubeConnection.State == ConnectionState.Open)
{
cubeConnection.Close();
}
cubeConnection.Dispose();
}
return cacheCleared;
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
/* Obtendo campos do modelo*/
if (comboBox1.SelectedIndex != 0)
{
SqlConnection conSql = new SqlConnection(ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString"].ConnectionString);
SqlCommand comSql = new SqlCommand("select * from modelocampo where idModelo = (select id from modelo where estrutura = '" + comboBox1.Text.Substring(0, comboBox1.Text.Length - 13) + "')", conSql);
conSql.Open();
SqlDataReader read = comSql.ExecuteReader();
if (read.HasRows)
{
int y = 0;
string camp = "";
foreach (var item in read)
{
camp += read["campo"].ToString() + ";";
y++;
}
campos = camp.Split(';');
read.Close();
read.Dispose();
comSql.CommandText = "select algoritmo from modelo where estrutura = '" + comboBox1.Text.Substring(0, comboBox1.Text.Length - 13) + "'";
read = comSql.ExecuteReader();
read.Read();
if (read["algoritmo"].ToString() == "MTS (Microsoft Time Serial)")
{
int x = 0;
AdomdConnection conn = new AdomdConnection(ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString_Analysis"].ConnectionString);
AdomdCommand comand = new AdomdCommand();
conn.Open();
comand.Connection = conn;
comand.CommandText = "SELECT StructureColumn('" + campos[0].Trim() + "'),StructureColumn('" + campos[1].Trim() + "') " +
"FROM " + comboBox1.Text.Substring(0, comboBox1.Text.Length - 13) + ".CASES";
AdomdDataAdapter ad = new AdomdDataAdapter(comand);
table.Clear();
ad.Fill(table);
DataTableReader reader = table.CreateDataReader();
// read.Read();
string valores = "";
foreach (var item in reader)
{
valores += reader[1].ToString() + ",";
}
valores = valores.Substring(0, valores.Length - 1);
try
{
//chartHist.Clear();
chartHist.Charts[0].SeriesCollection.Delete(0);
chartHist.Charts.Delete(0);
chartHist.Refresh();
}
catch
{
}
chartHist.ResetText();
chartHist.Charts.Add(0);
chartHist.Charts[0].SeriesCollection.Add(0);
chartHist.Charts[0].Type = OWC11.ChartChartTypeEnum.chChartTypeLine;
chartHist.Charts[0].SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimSeriesNames, Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral), "Quantidade");
chartHist.Charts[0].SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimValues, Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral), valores);
chartHist.Refresh();
}
}
carregaGrid();
}
}
private void tabPage1_Enter(object sender, EventArgs e)
{
if (comboBox1.SelectedIndex != 0 && !string.IsNullOrEmpty(comboBox1.Text))
{
Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn = new AdomdConnection(ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString_Analysis"].ConnectionString);
Microsoft.AnalysisServices.AdomdClient.AdomdCommand comand = new AdomdCommand();
conn.Open();
comand.Connection = conn;
comand.CommandText = "SELECT FLATTENED PredictTimeSeries(" + comboBox1.Text.Substring(0, comboBox1.Text.Length - 13) + "." + campos[1].Trim() + "," + numericUpDown1.Value + ") " +
"From " + comboBox1.Text.Substring(0, comboBox1.Text.Length - 13);
prev = comand.ExecuteReader();
// Expression expression = new Expression();
// expression = (Expression) x[0];
string val = "";
int j = 1;
//CARREGA GRID e valores grafico
dataGridView2.Rows.Clear();
foreach (var item in prev)
{
val += item[1].ToString() + ",";
dataGridView2.Rows.Add(j, prev[0].ToString(), prev[1].ToString());
j++;
}
val = val.Substring(0, val.Length - 1);
try
{
//chartHist.Clear();
chartPrev.Charts[0].SeriesCollection.Delete(0);
chartPrev.Charts.Delete(0);
chartPrev.Refresh();
}
catch
{
}
chartPrev.ResetText();
chartPrev.Charts.Add(0);
chartPrev.Charts[0].SeriesCollection.Add(0);
chartPrev.Charts[0].Type = OWC11.ChartChartTypeEnum.chChartTypeLine;
chartPrev.Charts[0].SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimSeriesNames, Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral), "Quantidade");
chartPrev.Charts[0].SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimValues, Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral), val);
chartPrev.Refresh();
}
}
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;
}
protected virtual AdomdConnection GetConnection()
{
AdomdConnection conn = new AdomdConnection(Connection.ConnectionString);
conn.Open();
return conn;
}
/* This method queries the data cube, puts the results into a DataTable then puts the results
* into an ArcTable
*
* layerid is an int corresponding to the top layer on the map
* MemberState is a data structure that contains all of the attribute members that
* have been selected
*
*
*/
public void createTableFromQuery(int layerid, MemberState ms, bool regionIsInt)
{
String connectionString;
connectionString = "Initial Catalog=" + m_databaseName + "; Data Source=kyle; integrated security=sspi";
AdomdConnection conn = new AdomdConnection(connectionString);
conn.Open();
AdomdCommand cmd = new AdomdCommand();
CellSet cells;
cmd.Connection = conn;
MDXQueryBuilder mdxBuilder = new MDXQueryBuilder(m_layerDictionary,m_siteDim);
String mdxQuery = mdxBuilder.buildQuery(layerid, ms);
m_query = mdxQuery;
cmd.CommandText = mdxQuery;
cells = cmd.ExecuteCellSet();
DataTable dt = dataTableFromCells(cells);
m_dataTable = dt;
ITable table = CreateArcTable(dt, regionIsInt);
m_table = table;
}
public static DataTable RunMDXWithDataTable(String mdx)
{
try
{
AdomdConnection conn = new AdomdConnection(ConfigurationManager.ConnectionStrings["Cube"].ConnectionString);
conn.Open();
AdomdCommand command = conn.CreateCommand();
command.CommandText = mdx;
command.CommandType = CommandType.Text;
//AdomdDataReader reader = command.ExecuteReader();
CellSet cs = command.ExecuteCellSet();
conn.Close();
return GetDataTableFromCellSet(cs);
}
catch (Exception ex)
{
SystemHelper.LogEntry("Fail in DbHelper.cs\\RunMDX(): " + ex.ToString() + "\n");
throw ex;
}
}
public ReportUsingLinq(string title)
{
string CommandText = @"SELECT
NON EMPTY { [Measures].[Bikes] } ON COLUMNS,
NON EMPTY { ([Station Pair Distance].[Mile Categories].[Mile Categories].ALLMEMBERS *
[TripCat].[Trip Category].[Trip Category].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 [Bikeshare])
WHERE ( [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))
{
AdomdDataAdapter adapter = new AdomdDataAdapter(cmd);
adapter.Fill(ds);
}
conn.Close();
}
// Linq using nested groups to build hierarchy, does this work?
// if it works, can intellisense in Razor figure it out?
var HourBikes = (from row in ds.Tables[0].AsEnumerable()
select new //LinqResultTable()
{
DistanceCategory = row.Field<string>("[Station Pair Distance].[Mile Categories].[Mile Categories].[MEMBER_UNIQUE_NAME]"),
DurationCategory = row.Field<string>("[TripCat].[Trip Category].[Trip Category].[MEMBER_UNIQUE_NAME]"),
Subscriber = row.Field<string>("[Subscribers].[Subscriber Info].[Subscriber Info].[MEMBER_UNIQUE_NAME]"),
Hour2 = row.Field<string>("[Time Table].[Hour2ofday].[Hour2ofday].[MEMBER_UNIQUE_NAME]"),
DistanceCategoryCaption = row.Field<string>("[Station Pair Distance].[Mile Categories].[Mile Categories].[MEMBER_CAPTION]"),
DurationCategoryCaption = row.Field<string>("[TripCat].[Trip Category].[Trip Category].[MEMBER_CAPTION]"),
SubscriberCaption = row.Field<string>("[Subscribers].[Subscriber Info].[Subscriber Info].[MEMBER_CAPTION]"),
Hour2Caption = row.Field<string>("[Time Table].[Hour2ofday].[Hour2ofday].[MEMBER_UNIQUE_NAME]"),
Bikes = (row.Field<double>("[Measures].[Bikes]")).ToString()
}); // .ToArray(); //forces immediate execution, not needed.
model = (from row in HourBikes
group row by new
{
DistanceCategory = row.DistanceCategory,
DurationCategory = row.DurationCategory,
DistanceCategoryCaption = row.DistanceCategoryCaption,
DurationCategoryCaption = row.DurationCategoryCaption
} into Cat
select new
{
DistanceCategory = Cat.Key.DistanceCategory,
DurationCategory = Cat.Key.DurationCategory,
DistanceCategoryCaption = Cat.Key.DistanceCategoryCaption,
DurationCategoryCaption = Cat.Key.DurationCategoryCaption,
// BikeValueList = (Cat.Select(row => row.Bikes)), // generates list of string values that can be summed later
TotalBikes = (Cat.Sum(row => Convert.ToDouble(row.Bikes))).ToString(),
subscribers = from srow in HourBikes
where Cat.Key.DistanceCategory == srow.DistanceCategory
&& Cat.Key.DurationCategory == srow.DurationCategory
group srow by new
{
Subsciber = srow.Subscriber,
SubscriberCaption = srow.SubscriberCaption
} into Scat
select new
{
Subscriber = Scat.Key.Subsciber,
SubscriberCaption = Scat.Key.SubscriberCaption,
// BikeValueList = (Scat.Select(row => row.Bikes)),
TotalBikes = (Scat.Sum(row => Convert.ToDouble(row.Bikes))).ToString("#,#.##"),
Hour2 = from hrow in HourBikes
where Cat.Key.DistanceCategory == hrow.DistanceCategory
&& Cat.Key.DurationCategory == hrow.DurationCategory
&& Scat.Key.Subsciber == hrow.Subscriber
group hrow by new
{
Hour2 = hrow.Hour2,
Hour2Caption = hrow.Hour2Caption
} into Hcat
select new
{
Hour2 = Hcat.Key.Hour2,
Hour2Caption = Hcat.Key.Hour2Caption,
// BikeValueList = (Hcat.Select(row => row.Bikes)),
TotalBikes = (Hcat.Sum(row => Convert.ToDouble(row.Bikes))).ToString()
}
}
}).AsEnumerable().Select(c=>c.ToExpando());
Title = title;
// return model;
}
public void Init(MeasureGroup mg,
string strParition,
EnvDTE.ProjectItem projItem)
{
try
{
bool IsOnlineMode = false;
Cube selectedCube = projItem.Object as Cube;
string serverName = "";
string databaseName = "";
string connectionString = "";
if ((selectedCube != null) && (selectedCube.ParentServer != null))
{
// if we are in Online mode there will be a parent server
serverName = selectedCube.ParentServer.Name;
databaseName = selectedCube.Parent.Name;
IsOnlineMode = true;
connectionString = selectedCube.ParentServer.ConnectionString;
}
else
{
// if we are in Project mode we will use the server name from
// the deployment settings
DeploymentSettings deploySet = new DeploymentSettings(projItem);
serverName = deploySet.TargetServer;
databaseName = deploySet.TargetDatabase; //use the target database instead of selectedCube.Parent.Name because selectedCube.Parent.Name only reflects the last place it was deployed to, and we want the user to be able to use the deployment settings to control which deployed server/database to check against
connectionString = "Data Source=" + serverName;
}
mg1 = mg;
part1 = mg.Partitions.FindByName(strParition);
aggDes = part1.AggregationDesign;
this.Text = " Aggregation sizes for partition " + strParition;
//lblSize.Text = part1.EstimatedRows.ToString() + " records"; //base this not on estimated rows but on actual rows... see below
lablPartName.Text = strParition;
txtServerNote.Text = string.Format("Note: The Partition size details have been taken from the currently deployed '{1}' database on the '{0}' server, " +
"which is the one currently configured as the deployment target.", serverName, databaseName);
txtServerNote.Visible = !IsOnlineMode;
//--------------------------------------------------------------------------------
// Open ADOMD connection to the server and issue DISCOVER_PARTITION_STAT request to get aggregation sizes
//--------------------------------------------------------------------------------
AdomdConnection adomdConnection = new AdomdConnection(connectionString);
adomdConnection.Open();
partitionDetails = adomdConnection.GetSchemaDataSet(AdomdSchemaGuid.PartitionStat, new object[] { databaseName, mg1.Parent.Name, mg1.Name, strParition });
DataColumn colItem1 = new DataColumn("Percentage", Type.GetType("System.String"));
partitionDetails.Tables[0].Columns.Add(colItem1);
AddGridStyle();
dataGrid1.DataSource = partitionDetails.Tables[0];
long iPartitionRowCount = 0;
if (partitionDetails.Tables[0].Rows.Count > 0)
{
iPartitionRowCount = Convert.ToInt64(partitionDetails.Tables[0].Rows[0]["AGGREGATION_SIZE"]);
}
lblSize.Text = iPartitionRowCount + " records";
double ratio = 0;
foreach (DataRow row in partitionDetails.Tables[0].Rows)
{
ratio = 100.0 * ((long)row["AGGREGATION_SIZE"] / (double)iPartitionRowCount);
row["Percentage"] = ratio.ToString("#0.00") + "%";
}
CurrencyManager cm = (CurrencyManager)this.BindingContext[dataGrid1.DataSource, dataGrid1.DataMember];
((DataView)cm.List).AllowNew = false;
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message);
try
{
this.Close();
}
catch { }
}
}
public override void Load_InitData()
{
if (Mode != ControlMode.New)
{
return;
}
ASPxComboBox cbbKeyField;
try
{
cbbKeyField = (ASPxComboBox)this.FindControl("cbbKeyField");
if (cbbKeyField == null) { return; }
}
#if DEBUG
catch (Exception ex)
{
throw ex;
}
#else
catch ()
{
return;
}
#endif
// Load fields
String connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["OLAPConnection"].ConnectionString;
using (AdomdConnection conn = new AdomdConnection(connectionString))
{
conn.Open();
// Find a cube
CubeDef cubeDef = null;
CubeCollection cubeCollection = conn.Cubes;
int numOfCube = cubeCollection.Count - 1;
for (; numOfCube > 0; numOfCube--)
{
cubeDef = cubeCollection[numOfCube];
if (cubeDef.Type == CubeType.Cube) { break; }
}
if (this is wcNormalFilter
|| this is wcTimeFilter)
{
Olap.DimensionFieldInfoCollection fields = new Olap.DimensionFieldInfoCollection();
// Load dimension field
DimensionCollection dimCollection = cubeDef.Dimensions;
int numOfDim = dimCollection.Count;
for (int dimIndex = 0; dimIndex < numOfDim; dimIndex++)
{
Dimension dim = dimCollection[dimIndex];
if (this is wcNormalFilter)
{
if (dim.UniqueName == "[ARDimTime]"
|| dim.DimensionType == DimensionTypeEnum.Measure)
{
continue;
}
}
else if (this is wcNumFilter)
{
if (dim.DimensionType != DimensionTypeEnum.Measure)
{
continue;
}
}
else // Date
{
if (dim.UniqueName != "[ARDimTime]")
{
continue;
}
}
foreach (Hierarchy hier in dim.Hierarchies)
{
fields.Add(new Olap.DimensionFieldInfo(hier));
}
}
Helpers.SetDataSource(cbbKeyField, fields, "UniqueName", "Caption");
}
else // wcNumFilter
{
Olap.MeasureFieldInfoCollection fields = new Olap.MeasureFieldInfoCollection();
MeasureCollection measureCollection = cubeDef.Measures;
int numOfMeasure = measureCollection.Count;
for (int mIndex = 0; mIndex < numOfMeasure; mIndex++)
{
Measure m = measureCollection[mIndex];
fields.Add(new Olap.MeasureFieldInfo(m));
}
Helpers.SetDataSource(cbbKeyField, fields, "UniqueName", "Caption");
}
}
}
public static void DeployScript(ProjectItem projItem, DTE2 ApplicationObject)
{
Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object;
try
{
//validate the script because deploying an invalid script makes cube unusable
Microsoft.AnalysisServices.Design.Scripts script = new Microsoft.AnalysisServices.Design.Scripts(oCube);
}
catch (Microsoft.AnalysisServices.Design.ScriptParsingFailed ex)
{
string throwaway = ex.Message;
MessageBox.Show("MDX Script in " + oCube.Name + " is not valid.", "Problem Deploying MDX Script");
return;
}
if (oCube.MdxScripts.Count == 0)
{
MessageBox.Show("There is no MDX script defined in this cube yet.");
return;
}
try
{
ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 1, 5);
// Check if the file is read-only (and probably checked in to a source control system)
// before attempting to save. (issue: 10327 )
FileAttributes fa = System.IO.File.GetAttributes(projItem.get_FileNames(1));
if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly )
{
//TODO - can I check and maybe prompt before saving?
//Save the cube
projItem.Save("");
}
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 2, 5);
// extract deployment information
DeploymentSettings deploySet = new DeploymentSettings(projItem);
// use xlst to create xmla alter command
XslCompiledTransform xslt = new XslCompiledTransform();
XmlReader xsltRdr;
XmlReader xrdr;
// read xslt from embedded resource
xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployMdxScript));
using ((xsltRdr))
{
// read content from .cube file
xrdr = XmlReader.Create(projItem.get_FileNames(1));
using (xrdr)
{
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 3, 5);
// Connect to Analysis Services
Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();
svr.Connect(deploySet.TargetServer);
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 4, 5);
// execute the xmla
try
{
Microsoft.AnalysisServices.Scripter scr = new Microsoft.AnalysisServices.Scripter();
// Build up the Alter MdxScript command using XSLT against the .cube file
XslCompiledTransform xslta = new XslCompiledTransform();
StringBuilder sb = new StringBuilder();
XmlWriterSettings xws = new XmlWriterSettings();
xws.OmitXmlDeclaration = true;
xws.ConformanceLevel = ConformanceLevel.Fragment;
XmlWriter xwrtr = XmlWriter.Create(sb, xws);
xslta.Load(xsltRdr);
XsltArgumentList xslarg = new XsltArgumentList();
Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase);
if (targetDB == null)
{
throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer));
}
string targetDatabaseID = targetDB.ID;
xslarg.AddParam("TargetDatabase", "", targetDatabaseID);
xslta.Transform(xrdr, xslarg, xwrtr);
// Extract the current script from the server and keep a temporary backup copy of it
StringBuilder sbBackup = new StringBuilder();
XmlWriterSettings xwSet = new XmlWriterSettings();
xwSet.ConformanceLevel = ConformanceLevel.Fragment;
xwSet.OmitXmlDeclaration = true;
xwSet.Indent = true;
XmlWriter xwScript = XmlWriter.Create(sbBackup,xwSet);
Cube oServerCube = targetDB.Cubes.Find(oCube.ID);
if (oServerCube == null)
{
throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer));
}
else if (oServerCube.State == AnalysisState.Unprocessed)
{
throw new System.Exception(string.Format("The {0} cube is not processed the {1} server.", oCube.Name, deploySet.TargetServer));
}
if (oServerCube.MdxScripts.Count == 0)
{
scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { oServerCube }, xwScript, true);
}
else
{
MdxScript mdxScr = oServerCube.MdxScripts[0];
scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { mdxScr }, xwScript, true);
}
xwScript.Close();
// update the MDX Script
XmlaResultCollection xmlaRC = svr.Execute(sb.ToString());
if (xmlaRC.Count == 1 && xmlaRC[0].Messages.Count == 0)
{
// all OK - 1 result - no messages
}
else
{
StringBuilder sbErr = new StringBuilder();
for (int iRC = 0; iRC < xmlaRC.Count;iRC ++)
{
for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++)
{
sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description);
}
}
MessageBox.Show(sbErr.ToString(),"BIDSHelper - Deploy MDX Script" );
}
// Test the MDX Script
AdomdConnection cn = new AdomdConnection("Data Source=" + deploySet.TargetServer + ";Initial Catalog=" + deploySet.TargetDatabase);
cn.Open();
AdomdCommand cmd = cn.CreateCommand();
string qry = "SELECT {} ON 0 FROM [" + oCube.Name +"];";
cmd.CommandText = qry;
try
{
// test that we can query the cube without errors
cmd.Execute();
// Building the project means that the .asdatabase file gets re-built so that
// we do not break the Deployment Wizard.
// --
// This line is included in this try block so that it is only executed if we can
// successfully query the cube without errors.
projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false);
}
catch (System.Exception ex)
{
// undo the deployment if we caught an exception during the deployment
svr.Execute(sbBackup.ToString());
MessageBox.Show(ex.Message);
}
}
catch (System.Exception ex)
{
if (MessageBox.Show("The following error occured while trying to deploy the MDX Script\r\n"
+ ex.Message
+ "\r\n\r\nDo you want to see a stack trace?"
,"BIDSHelper - Deploy MDX Script"
, MessageBoxButtons.YesNo
, MessageBoxIcon.Error
, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
{
MessageBox.Show(ex.StackTrace);
}
}
finally
{
ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 5, 5);
// report any results back (status bar?)
svr.Disconnect();
}
}
}
}
finally
{
ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(false, "Deploying MdxScript", 5, 5);
}
}
/// <summary>
/// Static method which we be called from Main. This method
/// is responsible for processing the cube, which will take
/// place one the ETL process is complete.
/// </summary>
/// <returns>
/// bool - success or failure, true for errors, false for no errors
/// </returns>
public static bool ProcessCube()
{
log.Info("Starting Processing of CUBE");
string cubeConnectionString = ConfigurationManager.ConnectionStrings["sqlConnStringOLAP"].ConnectionString;
AdomdConnection conn = new AdomdConnection(cubeConnectionString);
AdomdCommand cmd = new AdomdCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"<Batch xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine""> <Parallel><Process xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:ddl2=""http://schemas.microsoft.com/analysisservices/2003/engine/2"" xmlns:ddl2_2=""http://schemas.microsoft.com/analysisservices/2003/engine/2/2"" xmlns:ddl100_100=""http://schemas.microsoft.com/analysisservices/2008/engine/100/100""><Object><DatabaseID>OLAP</DatabaseID><CubeID>Data Warehouse</CubeID></Object><Type>ProcessFull</Type><WriteBackTableCreation>UseExisting</WriteBackTableCreation></Process></Parallel></Batch>";
try
{
conn.Open();
log.Info("Executing Command to Process Cube: " + cmd.CommandText);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
log.Error("An Error occurred during the processing of the CUBE");
log.Error(ex);
return false;
}
log.Info("Finished Processing of CUBE");
return true;
}
public void StartTest()
{
try
{
Dictionary<Aggregation, long> dictAggRowCount = new Dictionary<Aggregation, long>();
Dictionary<AggregationDesign, long> dictAggDesignRowCount = new Dictionary<AggregationDesign, long>();
AdomdConnection conn = new AdomdConnection("Data Source=" + _currentAggD.ParentServer.Name + ";Initial Catalog=" + _currentAggD.ParentDatabase.Name);
conn.Open();
_sessionID = conn.SessionID;
if (_cancelled) return;
foreach (Partition p in _currentAggD.Parent.Partitions)
{
if (p.AggregationDesignID != _currentAggD.ID) continue;
RaiseProgressEvent(0, "Retrieving list of processed aggs in partition " + p.Name + "...");
AdomdRestrictionCollection coll = new AdomdRestrictionCollection();
coll.Add("DATABASE_NAME", _currentAggD.ParentDatabase.Name);
coll.Add("CUBE_NAME", _currentAggD.ParentCube.Name);
coll.Add("MEASURE_GROUP_NAME", p.Parent.Name);
coll.Add("PARTITION_NAME", p.Name);
DataSet aggDS = conn.GetSchemaDataSet("DISCOVER_PARTITION_STAT", coll);
foreach (DataRow row in aggDS.Tables[0].Rows)
{
if (!string.IsNullOrEmpty(Convert.ToString(row["AGGREGATION_NAME"])))
{
Aggregation a = p.AggregationDesign.Aggregations.FindByName(Convert.ToString(row["AGGREGATION_NAME"]));
if (a == null) throw new Exception("Couldn't find aggregation [" + row["AGGREGATION_NAME"] + "]");
long lngAggRowCount = Convert.ToInt64(row["AGGREGATION_SIZE"]);
if (lngAggRowCount > 0)
{
if (!dictAggRowCount.ContainsKey(a))
dictAggRowCount.Add(a, lngAggRowCount);
else
dictAggRowCount[a] += lngAggRowCount;
}
}
else
{
long lngPartitionRowCount = Convert.ToInt64(row["AGGREGATION_SIZE"]);
if (!dictAggDesignRowCount.ContainsKey(p.AggregationDesign ?? _emptyAggregationDesign))
dictAggDesignRowCount.Add(p.AggregationDesign ?? _emptyAggregationDesign, lngPartitionRowCount);
else
dictAggDesignRowCount[p.AggregationDesign ?? _emptyAggregationDesign] += lngPartitionRowCount;
}
if (_cancelled) return;
}
}
if (dictAggRowCount.Count == 0) return;
//figure out any DefaultMember that aren't the all member
string sDefaultMembersCalcs = "";
string sDefaultMembersCols = "";
foreach (MeasureGroupDimension mgd in _currentAggD.Parent.Dimensions)
{
RegularMeasureGroupDimension rmgd = mgd as RegularMeasureGroupDimension;
if (rmgd == null) continue;
foreach (MeasureGroupAttribute mga in rmgd.Attributes)
{
if (mga.CubeAttribute.AttributeHierarchyEnabled && mga.Attribute.AttributeHierarchyEnabled)
{
sDefaultMembersCalcs += "MEMBER [Measures].[|" + mga.CubeAttribute.Parent.Name + " | " + mga.CubeAttribute.Attribute.Name + "|] as iif([" + mga.CubeAttribute.Parent.Name + "].[" + mga.CubeAttribute.Attribute.Name + "].DefaultMember.Level.Name = \"(All)\", null, [" + mga.CubeAttribute.Parent.Name + "].[" + mga.CubeAttribute.Attribute.Name + "].DefaultMember.UniqueName)\r\n";
if (sDefaultMembersCols.Length > 0) sDefaultMembersCols += ",";
sDefaultMembersCols += "[Measures].[|" + mga.CubeAttribute.Parent.Name + " | " + mga.CubeAttribute.Attribute.Name + "|]\r\n";
}
}
}
RaiseProgressEvent(1, "Detecting DefaultMember on each dimension attribute...");
AdomdCommand cmd = new AdomdCommand();
cmd.Connection = conn;
cmd.CommandText = "with\r\n"
+ sDefaultMembersCalcs
+ "select {\r\n"
+ sDefaultMembersCols
+ "} on 0\r\n"
+ "from [" + _currentAggD.ParentCube.Name.Replace("]", "]]") + "]";
CellSet cs = cmd.ExecuteCellSet();
int iCol = 0;
_dictDefaultMembers.Clear();
foreach (MeasureGroupDimension mgd in _currentAggD.Parent.Dimensions)
{
RegularMeasureGroupDimension rmgd = mgd as RegularMeasureGroupDimension;
if (rmgd == null) continue;
foreach (MeasureGroupAttribute mga in rmgd.Attributes)
{
if (mga.CubeAttribute.AttributeHierarchyEnabled && mga.Attribute.AttributeHierarchyEnabled)
{
string sValue = Convert.ToString(cs.Cells[iCol++].Value);
if (!string.IsNullOrEmpty(sValue))
{
_dictDefaultMembers.Add(mga, sValue);
}
}
}
}
conn.Close(false);
if (_cancelled) return;
RaiseProgressEvent(2, "Starting trace...");
Server s = new Server();
s.Connect("Data Source=" + _currentAggD.ParentServer.Name, _sessionID);
Server sAlt = new Server();
sAlt.Connect("Data Source=" + _currentAggD.ParentServer.Name);
MeasureGroup mgAlt = sAlt.Databases.GetByName(_currentAggD.ParentDatabase.Name).Cubes.GetByName(_currentAggD.ParentCube.Name).MeasureGroups.GetByName(_currentAggD.Parent.Name);
try
{
Database db = s.Databases.GetByName(_currentAggD.ParentDatabase.Name);
string sTraceID = "BIDS Helper Aggs Performance Trace " + System.Guid.NewGuid().ToString();
_trc = s.Traces.Add(sTraceID, sTraceID);
_trc.OnEvent += new TraceEventHandler(trace_OnEvent);
_trc.Stopped += new TraceStoppedEventHandler(trace_Stopped);
_trc.AutoRestart = false;
TraceEvent te;
te = _trc.Events.Add(TraceEventClass.QueryEnd);
te.Columns.Add(TraceColumn.Duration);
te.Columns.Add(TraceColumn.SessionID);
te = _trc.Events.Add(TraceEventClass.GetDataFromAggregation);
te.Columns.Add(TraceColumn.ObjectPath);
te.Columns.Add(TraceColumn.TextData);
te.Columns.Add(TraceColumn.SessionID);
te.Columns.Add(TraceColumn.ConnectionID);
_trc.Update();
_trc.Start();
if (_cancelled) return;
s.BeginTransaction();
UnprocessOtherPartitions(s);
int i = 0;
if (_testAgg)
{
foreach (Aggregation a in dictAggRowCount.Keys)
{
RaiseProgressEvent(3 + (int)(87.0 * i++ / dictAggRowCount.Count / _totalIterations), "Testing performance with agg " + i + " of " + dictAggRowCount.Count + " (" + a.Name + ")...");
AggregationPerformance aggP = new AggregationPerformance(a);
aggP.AggregationRowCount = dictAggRowCount[a];
aggP.PartitionRowCount = dictAggDesignRowCount[a.Parent];
aggP.MeasureGroupRowCount = aggP.PartitionRowCount; //if there are multiple aggregation designs, outside code will fix that
ServerExecute(s, "<ClearCache xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n"
+ " <Object>" + "\r\n"
+ " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n"
+ " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n"
+ " </Object>" + "\r\n"
+ " </ClearCache>");
_queryEnded = false;
//initialize the MDX script with a no-op query
ServerExecuteMDX(db, "with member [Measures].[_Exec MDX Script_] as null select [Measures].[_Exec MDX Script_] on 0 from [" + _currentAggD.ParentCube.Name.Replace("]", "]]") + "]", _sessionID);
while (!this._queryEnded) //wait for session trace query end event
{
if (_cancelled) return;
System.Threading.Thread.Sleep(100);
}
aggP.ScriptPerformanceWithAgg = _queryDuration;
_queryEnded = false;
//don't clear dictHitAggs because if an agg got hit during the ExecuteMDXScript event, then it will be cached for the query
ServerExecuteMDX(db, aggP.PerformanceTestMDX, _sessionID);
while (!this._queryEnded) //wait for session trace query end event
{
if (_cancelled) return;
System.Threading.Thread.Sleep(100);
}
aggP.QueryPerformanceWithAgg = _queryDuration;
if (_dictHitAggs.ContainsKey(a))
aggP.HitAggregation = true;
aggP.AggHits = _dictHitAggs;
_dictHitAggs = new Dictionary<Aggregation, int>();
_listAggPerf.Add(aggP);
if (_cancelled) return;
}
}
if (_testWithoutSomeAggs && _listAggPerf.Count > 0)
{
RaiseProgressEvent(4 + (int)(87.0 * i / dictAggRowCount.Count / _totalIterations), "Dropping some aggs inside a transaction...");
//build list of all aggs which were hit, and which are contained within another agg
List<AggregationPerformance> allAggs = new List<AggregationPerformance>();
foreach (AggregationPerformance ap in _listAggPerf)
{
if (!ap.HitAggregation) continue;
foreach (AggregationPerformance ap2 in _listAggPerf)
{
if (ap.Aggregation != ap2.Aggregation && ap.Aggregation.Parent == ap2.Aggregation.Parent && SearchSimilarAggs.IsAggregationIncluded(ap.Aggregation, ap2.Aggregation, false))
{
allAggs.Add(ap);
break;
}
}
}
allAggs.Sort(delegate(AggregationPerformance a, AggregationPerformance b)
{
int iCompare = 0;
try
{
if (a == b || a.Aggregation == b.Aggregation) return 0;
iCompare = a.AggregationRowCount.CompareTo(b.AggregationRowCount);
if (iCompare == 0)
{
//if the aggs are the same rowcount, then sort by whether one is contained in the other
if (SearchSimilarAggs.IsAggregationIncluded(a.Aggregation, b.Aggregation, false))
return -1;
else if (SearchSimilarAggs.IsAggregationIncluded(b.Aggregation, a.Aggregation, false))
return 1;
else
return 0;
}
}
catch { }
return iCompare;
});
List<AggregationPerformance> deletedAggregationPerfs = new List<AggregationPerformance>();
List<AggregationPerformance> nextAggs = new List<AggregationPerformance>();
List<AggregationPerformance> aggsToSkipTesting = new List<AggregationPerformance>();
System.Diagnostics.Stopwatch timerProcessIndexes = new System.Diagnostics.Stopwatch();
long lngLastProcessIndexesTime = 0;
AggregationPerformance lastDeletedAggregationPerf = null;
while (allAggs.Count > 0)
{
AggregationPerformance aggP = null;
if (nextAggs.Count == 0)
{
aggP = allAggs[0];
allAggs.RemoveAt(0);
}
else
{
aggP = nextAggs[0];
nextAggs.RemoveAt(0);
allAggs.Remove(aggP);
}
deletedAggregationPerfs.Add(aggP);
//capture XMLA for deleting aggs
AggregationDesign aggD = mgAlt.AggregationDesigns.GetByName(aggP.Aggregation.Parent.Name);
aggD.ParentServer.CaptureXml = true;
foreach (AggregationPerformance ap in deletedAggregationPerfs)
{
if (aggD.Aggregations.ContainsName(ap.Aggregation.Name))
{
aggD.Aggregations.RemoveAt(aggD.Aggregations.IndexOfName(ap.Aggregation.Name));
}
}
aggD.Update(UpdateOptions.ExpandFull);
string sAlterXMLA = aggD.ParentServer.CaptureLog[0];
aggD.ParentServer.CaptureLog.Clear();
aggD.ParentServer.CaptureXml = false;
aggD.Refresh(true); //get the deleted aggs back
ServerExecute(s, sAlterXMLA);
if (_cancelled) return;
RaiseProgressEvent(5 + (int)(87.0 * i++ / dictAggRowCount.Count / _totalIterations), "Processing aggs without some aggs " + ((i - 1) % dictAggRowCount.Count + 1) + " of " + dictAggRowCount.Count + " (" + aggP.AggregationName + ")...");
timerProcessIndexes.Reset();
timerProcessIndexes.Start();
//process aggs to delete existing aggs
ServerExecute(s, "<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n"
+ " <Parallel>" + "\r\n"
+ " <Process xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2\" xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2\" xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\">" + "\r\n"
+ " <Object>" + "\r\n"
+ " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n"
+ " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n"
+ " <MeasureGroupID>" + _currentAggD.Parent.ID + "</MeasureGroupID>" + "\r\n"
+ " </Object>" + "\r\n"
+ " <Type>ProcessIndexes</Type>" + "\r\n"
+ " <WriteBackTableCreation>UseExisting</WriteBackTableCreation>" + "\r\n"
+ " </Process>" + "\r\n"
+ " </Parallel>" + "\r\n"
+ "</Batch>" + "\r\n");
if (!string.IsNullOrEmpty(_errors)) throw new Exception(_errors);
timerProcessIndexes.Stop();
//record time it took to process aggs... compare how long the prior one took, then you can determine how much incremental time was spent on the newly deleted agg
if (lastDeletedAggregationPerf != null)
lastDeletedAggregationPerf.ProcessIndexesDuration = lngLastProcessIndexesTime - timerProcessIndexes.ElapsedMilliseconds;
lngLastProcessIndexesTime = timerProcessIndexes.ElapsedMilliseconds;
lastDeletedAggregationPerf = aggP;
if (_cancelled) return;
int j = 0;
foreach (AggregationPerformance deleteAP in deletedAggregationPerfs)
{
RaiseProgressEvent(6 + (int)(87.0 * i / dictAggRowCount.Count / _totalIterations), "Testing performance without some aggs " + ((i - 1) % dictAggRowCount.Count + 1) + " of " + dictAggRowCount.Count + "\r\nTesting agg " + (++j) + " of " + deletedAggregationPerfs.Count + " (" + deleteAP.AggregationName + ")...");
if (aggsToSkipTesting.Contains(deleteAP)) continue; //skip this agg if we've already determined it won't hit another agg
ServerExecute(s, "<ClearCache xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n"
+ " <Object>" + "\r\n"
+ " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n"
+ " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n"
+ " </Object>" + "\r\n"
+ " </ClearCache>");
_queryEnded = false;
//initialize the MDX script with a no-op query
ServerExecuteMDX(db, "with member [Measures].[_Exec MDX Script_] as null select [Measures].[_Exec MDX Script_] on 0 from [" + _currentAggD.ParentCube.Name.Replace("]", "]]") + "]", _sessionID);
while (!this._queryEnded) //wait for session trace query end event
{
if (_cancelled) return;
System.Threading.Thread.Sleep(100);
}
long lngScriptDuration = _queryDuration;
_queryEnded = false;
//don't clear dictHitAggs because if an agg got hit during the ExecuteMDXScript event, then it will be cached for the query
ServerExecuteMDX(db, deleteAP.PerformanceTestMDX, _sessionID);
while (!this._queryEnded) //wait for session trace query end event
{
if (_cancelled) return;
System.Threading.Thread.Sleep(100);
}
long lngQueryDuration = _queryDuration;
List<Aggregation> deletedAggregations = new List<Aggregation>();
foreach (AggregationPerformance a in deletedAggregationPerfs)
{
deletedAggregations.Add(a.Aggregation);
}
MissingAggregationPerformance missingAggPerf = new MissingAggregationPerformance(deleteAP, deletedAggregations.ToArray(), _dictHitAggs);
_listMissingAggPerf.Add(missingAggPerf);
missingAggPerf.QueryPerformance = lngQueryDuration;
missingAggPerf.ScriptPerformance = lngScriptDuration;
foreach (Aggregation a in missingAggPerf.AggHitsDiff)
{
foreach (AggregationPerformance ap in allAggs)
{
if (ap.Aggregation == a && !nextAggs.Contains(ap))
nextAggs.Add(ap);
}
}
if (missingAggPerf.AggHitsDiff.Length == 0)
{
aggsToSkipTesting.Add(deleteAP);
}
else
{
bool bThisAggContainedInRemainingAgg = false;
foreach (AggregationPerformance ap2 in allAggs)
{
if (deleteAP.Aggregation != ap2.Aggregation && deleteAP.Aggregation.Parent == ap2.Aggregation.Parent && SearchSimilarAggs.IsAggregationIncluded(deleteAP.Aggregation, ap2.Aggregation, false))
{
bThisAggContainedInRemainingAgg = true;
break;
}
}
if (!bThisAggContainedInRemainingAgg)
aggsToSkipTesting.Add(deleteAP); //stop testing this agg when it's not contained in any remaining aggs that need to be tested
}
_dictHitAggs = new Dictionary<Aggregation, int>();
}
if (_cancelled) return;
s.RollbackTransaction();
s.BeginTransaction();
UnprocessOtherPartitions(s);
}
}
if (_testNoAggs)
{
//ensure the counter is where it's supposed to be since the "test with some aggs" test may not have done iterations for every agg
i = Math.Max(i, dictAggRowCount.Count * (_totalIterations - 1));
RaiseProgressEvent(4 + (int)(87.0 * i / dictAggRowCount.Count / _totalIterations), "Dropping all aggs inside a transaction...");
//delete all aggs in all aggregation designs
string sXMLA = "<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\" xmlns:as=\"http://schemas.microsoft.com/analysisservices/2003/engine\" xmlns:dwd=\"http://schemas.microsoft.com/DataWarehouse/Designer/1.0\">" + "\r\n"
+ " <Alter AllowCreate=\"true\" ObjectExpansion=\"ExpandFull\">" + "\r\n"
+ " <Object>" + "\r\n"
+ " <DatabaseID>" + _currentAggD.Parent.ParentDatabase.ID + "</DatabaseID>" + "\r\n"
+ " <CubeID>" + _currentAggD.Parent.Parent.ID + "</CubeID>" + "\r\n"
+ " <MeasureGroupID>" + _currentAggD.Parent.ID + "</MeasureGroupID>" + "\r\n"
+ " <AggregationDesignID>" + _currentAggD.ID + "</AggregationDesignID>" + "\r\n"
+ " </Object>" + "\r\n"
+ " <ObjectDefinition>" + "\r\n"
+ " <AggregationDesign>" + "\r\n"
+ " <ID>" + _currentAggD.ID + "</ID>" + "\r\n"
+ " <Name>" + _currentAggD.Name + "</Name>" + "\r\n"
+ " <Aggregations>" + "\r\n"
+ " </Aggregations>" + "\r\n"
+ " </AggregationDesign>" + "\r\n"
+ " </ObjectDefinition>" + "\r\n"
+ " </Alter>" + "\r\n"
+ "</Batch>" + "\r\n";
ServerExecute(s, sXMLA);
RaiseProgressEvent(5 + (int)(87.0 * i / dictAggRowCount.Count / _totalIterations), "Processing empty aggregation design...");
if (_cancelled) return;
//process aggs to delete existing aggs
ServerExecute(s, "<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n"
+ " <Parallel>" + "\r\n"
+ " <Process xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2\" xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2\" xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\">" + "\r\n"
+ " <Object>" + "\r\n"
+ " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n"
+ " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n"
+ " <MeasureGroupID>" + _currentAggD.Parent.ID + "</MeasureGroupID>" + "\r\n"
+ " </Object>" + "\r\n"
+ " <Type>ProcessIndexes</Type>" + "\r\n"
+ " <WriteBackTableCreation>UseExisting</WriteBackTableCreation>" + "\r\n"
+ " </Process>" + "\r\n"
+ " </Parallel>" + "\r\n"
+ "</Batch>" + "\r\n");
if (!string.IsNullOrEmpty(_errors)) throw new Exception(_errors);
if (_cancelled) return;
foreach (AggregationPerformance aggP in _listAggPerf)
{
RaiseProgressEvent(10 + (int)(87.0 * i++ / dictAggRowCount.Count / _totalIterations), "Testing performance with no aggs " + ((i - 1) % dictAggRowCount.Count + 1) + " of " + dictAggRowCount.Count + " (" + aggP.AggregationName + ")...");
ServerExecute(s, "<ClearCache xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">" + "\r\n"
+ " <Object>" + "\r\n"
+ " <DatabaseID>" + _currentAggD.ParentDatabase.ID + "</DatabaseID>" + "\r\n"
+ " <CubeID>" + _currentAggD.ParentCube.ID + "</CubeID>" + "\r\n"
+ " </Object>" + "\r\n"
+ " </ClearCache>");
if (_cancelled) return;
_queryEnded = false;
//initialize the MDX script with a no-op query
ServerExecuteMDX(db, "with member [Measures].[_Exec MDX Script_] as null select [Measures].[_Exec MDX Script_] on 0 from [" + _currentAggD.ParentCube.Name.Replace("]", "]]") + "]", _sessionID);
while (!this._queryEnded) //wait for session trace query end event
{
if (_cancelled) return;
System.Threading.Thread.Sleep(100);
}
aggP.ScriptPerformanceWithoutAggs = _queryDuration;
_queryEnded = false;
ServerExecuteMDX(db, aggP.PerformanceTestMDX, _sessionID);
while (!this._queryEnded) //wait for session trace query end event
{
if (_cancelled) return;
System.Threading.Thread.Sleep(100);
}
aggP.QueryPerformanceWithoutAggs = _queryDuration;
}
} //end of testing with no aggs
RaiseProgressEvent(100, "Finished measure group " + _currentAggD.Parent.Name);
if (!string.IsNullOrEmpty(_errors)) throw new Exception(_errors);
}
finally
{
try
{
if (!s.Connected)
{
s.Connect("Data Source=" + _currentAggD.ParentServer.Name, _sessionID);
}
}
catch
{
try
{
if (!s.Connected)
{
s.Connect("Data Source=" + _currentAggD.ParentServer.Name); //can't connect to that session, so just reconnect
}
}
catch { }
}
try
{
s.RollbackTransaction();
}
catch { }
try
{
_trc.Drop();
}
catch { }
try
{
s.Disconnect();
}
catch { }
}
}
catch (Exception ex)
{
if (!_cancelled)
{
_errors += ex.Message + "\r\n" + ex.StackTrace + "\r\n";
System.Windows.Forms.MessageBox.Show(_errors);
}
}
}