public static void ReturnConnection(AdomdConnection connection)
{
if (!IsPoolingEnabled)
{
if (connection.State == ConnectionState.Open)
{
connection.Close(true);
}
connection.Dispose();
return;
}
if (connection.State != ConnectionState.Open)
{
connection.Dispose();
return;
}
lock (ConnectionPool)
{
if (ConnectionPool.Count == QueueCapacity)
{
connection.Close(true);
connection.Dispose();
return;
}
ConnectionPool.Enqueue(connection);
}
}
public void Close(bool endSession)
{
if (_adomdConn.State != ConnectionState.Closed && _adomdConn.State != ConnectionState.Broken)
{
_adomdConn.Close(endSession);
_spid = 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;
}
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);
}
private Dictionary <string, int> RequestSales(string MDXQuery)
{
Dictionary <string, int> responseMessage = new Dictionary <string, int>();
try
{
OpenConnection();
AdomdCommand command = new AdomdCommand(MDXQuery, connection);
AdomdDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
responseMessage.Add(Convert.ToString(dataReader[0]), Convert.ToInt32(dataReader[1]));
}
dataReader.Close();
connection.Close();
return(responseMessage);
}
catch (Exception e)
{
Console.WriteLine("Exception has occured while retriving data:\n" +
"Error message: " + e.Message);
return(null);
}
}
protected void Button2_Click(object sender, EventArgs e)
{
AdomdConnection con = new AdomdConnection("Data Source=PC;Initial Catalog=ADM_HW");
con.Open();
AdomdCommand com = new AdomdCommand();
com.Connection = con;
string s = @"SELECT disease From [HeartID3MM]
NATURAL PREDICTION JOIN
(SELECT '" + TextBox1.Text + @"' AS [Age],
'" + DropDownList1.SelectedItem.Text + @"' AS [Chest Pain Type],
'" + TextBox2.Text + @"' AS [Rest Blood Pressure],
'" + DropDownList2.SelectedItem.Text + @"' AS [Blood Sugar],
'" + DropDownList4.SelectedItem.Text + @"' AS [Exercice Angina],
'" + TextBox3.Text + @"' AS [Max Heart Rate],
'" + DropDownList3.SelectedItem.Text + @"' AS [Rest Electro]) AS t";
com.CommandText = s;
AdomdDataReader dr = com.ExecuteReader();
if (dr.Read())
{
if (dr[0] != null)
{
Label2.Text = dr[0].ToString();
}
}
dr.Close();
con.Close();
}
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);
}
private void Button_Click_1(object sender, RoutedEventArgs e)
{
string connectionString = "Data Source=localhost;Catalog=MDX Step-by-Step;ConnectTo=11.0;Integrated Security=SSPI";
AdomdConnection _connection = new AdomdConnection(connectionString);
if (_connection != null)
{
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
}
AdomdCommand command = _connection.CreateCommand();
StringBuilder sb = new StringBuilder();
sb.Append("WITH");
sb.Append(" MEMBER [Product].[Category].[All Products].[X] AS 1+1");
sb.Append("SELECT{ ([Date].[Calendar].[CY 2002]),([Date].[Calendar].[CY 2003])}*{([Measures].[Reseller Sales Amount]) } ON COLUMNS,");
sb.Append("{ ([Product].[Category].[Accessories]),([Product].[Category].[Bikes]),([Product].[Category].[Clothing]),");
sb.Append("([Product].[Category].[Components]),([Product].[Category].[X])} ON ROWS");
sb.Append(" FROM [Step-by-Step]");
command.CommandText = sb.ToString();
var xmlreader = command.ExecuteXmlReader();
CellSet cellSet = CellSet.LoadXml(xmlreader);
_connection.Close();
var dt = ToDataTable(cellSet);
var v = dt.Rows.Count;
}
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 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;
}
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 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();
}
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 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());
}
//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);
}
/// <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 SemesterSales[] GetSalesPrSemester()
{
using (AdomdConnection conn = new AdomdConnection("DataSource = localhost; Initial Catalog = FClubCube"))
{
conn.Open();
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT [Measures].[Fact Sale Count] ON COLUMNS,
NONEMPTY([Dim Member].[Semester].[Semester]*[Dim Product].[Sub Sub Category].[Sub Sub Category]) ON ROWS
FROM [F Club DW]";
AdomdDataReader dr = cmd.ExecuteReader();
List <SemesterSales> semesters = new List <SemesterSales>();
while (dr.Read())
{
SemesterSales semester = new SemesterSales
{
semester = dr[0].ToString(),
SubSubCategory = dr[1].ToString(),
saleCount = int.Parse(dr[2].ToString())
};
semesters.Add(semester);
}
dr.Close();
SemesterSales[] semesterSales = semesters.FindAll(FindBeer).ToArray();
conn.Close();
return(semesterSales);
}
}
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;
}
public SCMonth[] GetSalesPrMonth()
{
StringBuilder result = new StringBuilder();
using (AdomdConnection conn = new AdomdConnection("DataSource = localhost; Initial Catalog = FClubCube"))
{
conn.Open();
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT [Measures].[Fact Sale Count] ON COLUMNS,
NONEMPTY( {[Dim Date].[Month].[Month]} ) ON ROWS
FROM [F Club DW]";
CellSet cs = cmd.ExecuteCellSet();
TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples;
TupleCollection tupleCollection = cs.Axes[1].Set.Tuples;
SCMonth[] monthSales = new SCMonth[tupleCollection.Count];
for (int row = 0; row < tupleCollection.Count; row++)
{
SCMonth month = new SCMonth();
month.month = int.Parse(tupleCollection[row].Members[0].Caption);
for (int col = 0; col < tuplesOnColumns.Count; col++)
{
month.saleCount = int.Parse(cs.Cells[col, row].FormattedValue);
monthSales[row] = month;
}
}
conn.Close();
return(monthSales);
}
}
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();
}
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 <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>
/// Given a CubeInfo, will return a dictionary of measure group to list of measures in the cube.
/// </summary>
/// <param name="cubeInfo"></param>
/// <returns></returns>
private Dictionary <string, List <string> > GetMeasureGroups(CubeInfo cubeInfo)
{
Dictionary <string, List <string> > measureGroupsToMeasures = new Dictionary <string, List <string> >();
string connection = string.Format(Constants.dataSource, cubeInfo.Datasource, cubeInfo.Catalog);
AdomdConnection conn = new AdomdConnection(connection);
conn.Open();
CubeDef cubeDef = null;
// Find cube in question.
foreach (CubeDef cube in conn.Cubes)
{
if (cube.Name == cubeInfo.Cube)
{
cubeDef = cube;
}
}
foreach (Measure m in cubeDef.Measures)
{
Property p = m.Properties.Find(Constants.measureGroup);
if (!measureGroupsToMeasures.ContainsKey(p.Value.ToString()))
{
measureGroupsToMeasures.Add(p.Value.ToString(), new List <string>());
}
measureGroupsToMeasures[p.Value.ToString()].Add(m.UniqueName);
}
conn.Close();
return(measureGroupsToMeasures);
}
public HttpResponseMessage List(string name = "cipla")
{
using (AdomdConnection conn = new AdomdConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
// AdomdConnection conn = new AdomdConnection(
// "Data Source=localhost;Catalog=YourDatabase");
//conn.Open();
string commandText = @"SELECT FLATTENED
PredictAssociation()
From
[Mining Structure Name]
NATURAL PREDICTION JOIN
(SELECT (SELECT 1 AS [UserId]) AS [Vm]) AS t ";
AdomdCommand cmd = new AdomdCommand(commandText, conn);
AdomdDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Console.WriteLine(Convert.ToString(dr[0]));
}
dr.Close();
conn.Close();
return(null);
}
}
protected void Button1_Click(object sender, EventArgs e)
{
AdomdConnection con = new AdomdConnection("Data Source = PC; Initial Catalog = ADM_HW");
con.Open();
AdomdCommand com = new AdomdCommand();
com.Connection = con;
string s = @"select flattened predicthistogram(disease) from [HeartBMM]
natural prediction join
(select '" + TextBox1.Text + @"' AS [Age],
'" + DropDownList1.SelectedItem.Text + @"' AS [Chest Pain Type],
'" + TextBox2.Text + @"' AS [Rest Blood Pressure],
'" + DropDownList2.SelectedItem.Text + @"' AS [Blood Sugar],
'" + DropDownList4.SelectedItem.Text + @"' AS [Exercice Angina],
'" + TextBox3.Text + @"' AS [Max Heart Rate],
'" + DropDownList3.SelectedItem.Text + @"' AS [Rest Electro]) AS t";
com.CommandText = s;
AdomdDataReader dr = com.ExecuteReader();
while (dr.Read())
{
if (dr[0] != null)
{
Label1.Text += dr[0].ToString() + " " + dr[2].ToString();
}
}
dr.Close();
con.Close();
}
private void btnConnect_Click(object sender, EventArgs e)
{
try
{
cos.Clear();
if (this.ConnStr.ToLower().Contains("catalog"))
{
CubeOperate co = new CubeOperate(this.ConnStr);
cos.Add(co.Conn.Database, co);
}
else
{
AdomdConnection conn = new AdomdConnection(this.ConnStr);
conn.Open();
DataSet dsCatalogs = conn.GetSchemaDataSet("DBSCHEMA_CATALOGS", null);
conn.Close();
foreach (DataRow catalogRow in dsCatalogs.Tables[0].Rows.Cast <DataRow>())
{
string catalog = catalogRow[0].ToString();
string connStr = string.Format("{0};Catalog ={1};", this.ConnStr.EndsWith(";") ? this.ConnStr.Remove(this.ConnStr.Length - 1) : this.ConnStr, catalog);
cos.Add(catalog, new CubeOperate(connStr));
}
}
BindServerInfo();
}
catch (Exception ex)
{
this.ShowMessage(ex);
}
}
//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);
}
}
public SCCatD[] GetProductSalesPrDay()
{
using (AdomdConnection conn = new AdomdConnection("DataSource = localhost; Initial Catalog = FClubCube"))
{
conn.Open();
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @"
SELECT [Measures].[Fact Sale Count] ON COLUMNS,
NONEMPTY( [Dim Product].[Category].[Category]*[Dim Date].[Day].[Day]) ON ROWS
FROM [F Club DW]";
AdomdDataReader dr = cmd.ExecuteReader();
List <SCCatD> days = new List <SCCatD>();
while (dr.Read())
{
SCCatD day = new SCCatD
{
cat = dr[0].ToString(),
day = int.Parse(dr[1].ToString()),
saleCount = int.Parse(dr[2].ToString())
};
days.Add(day);
}
dr.Close();
SCCatD[] daySales = days.ToArray();
conn.Close();
return(daySales);
}
}
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 ProductCat[] GetProductsByCategory()
{
using (AdomdConnection conn = new AdomdConnection("DataSource = localhost; Initial Catalog = FClubCube"))
{
conn.Open();
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @"SELECT
[Measures].[Fact Sale Count] ON COLUMNS,
{[Dim Product].[Product Id].[Product Id]*[Dim Product].[Name].[Name]*[Dim Product].[Category].[Category]*[Dim Product].[Sub Category].[Sub Category]*[Dim Product].[Sub Sub Category].[Sub Sub Category]} ON ROWS
From [F Club DW]";
AdomdDataReader dr = cmd.ExecuteReader();
List <ProductCat> productCats = new List <ProductCat>();
while (dr.Read())
{
ProductCat productCat = new ProductCat
{
productID = int.Parse(dr[0].ToString()),
name = dr[1].ToString(),
cat = dr[2].ToString(),
subCat = dr[3].ToString(),
subSubCat = dr[4].ToString(),
};
productCats.Add(productCat);
}
dr.Close();
ProductCat[] productSubCats = productCats.ToArray();
conn.Close();
return(productSubCats);
}
}
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();
}
/// <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 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 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 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);
}
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;
}
public DataTable ExecuteReader(String query, ref String sessionId)
{
if (!String.IsNullOrWhiteSpace(_connectionString))
{
using (AdomdConnection _connect = new AdomdConnection(this._connectionString))
{
try
{
_connect.Open();
sessionId = _connect.SessionID;
using (AdomdCommand cmd = new AdomdCommand(query, _connect))
{
using (var reader = cmd.ExecuteReader())
{
if (reader != null)
{
DataTable table = new DataTable();
var metadata_table = reader.GetSchemaTable();
if (metadata_table != null)
{
foreach (DataRow row in metadata_table.Rows)
{
String columnName = row[0].ToString();
columnName = Enumerable.LastOrDefault<string>((IEnumerable<string>)columnName.Replace(".[MEMBER_CAPTION]", "").Replace("[", "").Replace("]", "").Split(new char[1] { '.' }));
DataColumn dataCol = new DataColumn(columnName);
if (row[0].ToString().Contains("[Measures]"))
{
dataCol.DataType = typeof(double);
dataCol.DefaultValue = (object)0;
}
table.Columns.Add(dataCol);
}
}
if (table.Columns.Count >= reader.FieldCount)
{
while (reader.Read())
{
var values = new object[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
values[i] = reader[i];
}
table.Rows.Add(values);
}
}
return table;
}
}
}
}
catch (Exception ex)
{
_connect.Close();
throw ex;
}
}
}
return null;
}
public CellSet ExecuteQuery(String query, ref String sessionId)
{
if (!String.IsNullOrWhiteSpace(_connectionString))
{
using (AdomdConnection _connect = new AdomdConnection(this._connectionString))
{
try
{
_connect.Open();
sessionId = _connect.SessionID;
using (AdomdCommand cmd = new AdomdCommand(query, _connect))
{
CellSet cs = cmd.ExecuteCellSet();
return cs;
}
}
catch (Exception ex)
{
_connect.Close();
throw ex;
}
}
}
return null;
}
// execute SSAS/OLAP command and return a R language compliant dataframe script representing the result data.
static string SSASConnectAndExec(string mdx)
{
AdomdConnection conn = new AdomdConnection(_connStr);
// string mdx = "SELECT {[Measures].[Page Views],[Measures].[Daily Unique Visitors]} ON 0,{[Dim Date].[Month].[Month]} ON ROWS FROM [Hearst POC];";
AdomdCommand cmd = new AdomdCommand(mdx, conn);
AdomdDataAdapter da = new AdomdDataAdapter(cmd);
DataTable dt = new DataTable();
try
{
conn.Open();
}
catch (AdomdException e)
{
Console.WriteLine(">> Failed to open SSAS Connection");
log.Error("Failed to open SSAS connection for " + _connStr, e);
return null;
}
try
{
da.Fill(dt);
}
catch (AdomdException e)
{
Console.WriteLine(">> Failed to fill the datatable");
log.Error("Failed to fill datatable for query " + mdx, e);
return null;
}
// Clean up ado.net objects
da.Dispose();
conn.Close();
// process datatable and create R language compliant dataframe object as script
return DataTableToRDataFrame(dt);
}
private void btnConnect_Click(object sender, EventArgs e)
{
try
{
cos.Clear();
if (this.ConnStr.ToLower().Contains("catalog"))
{
CubeOperate co = new CubeOperate(this.ConnStr);
cos.Add(co.Conn.Database, co);
}
else
{
AdomdConnection conn = new AdomdConnection(this.ConnStr);
conn.Open();
DataSet dsCatalogs = conn.GetSchemaDataSet("DBSCHEMA_CATALOGS", null);
conn.Close();
foreach (DataRow catalogRow in dsCatalogs.Tables[0].Rows.Cast<DataRow>())
{
string catalog = catalogRow[0].ToString();
string connStr = string.Format("{0};Catalog ={1};", this.ConnStr.EndsWith(";") ? this.ConnStr.Remove(this.ConnStr.Length - 1) : this.ConnStr, catalog);
cos.Add(catalog, new CubeOperate(connStr));
}
}
BindServerInfo();
}
catch (Exception ex)
{
this.ShowMessage(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;
}
/// <summary>
/// Method that is called to retrieve Extracts data from the OLAP DB. Method
/// will put data into the Extracts Report Model. The retrieve criteria depends on the
/// parameters provided, and there is an order of priority within the 'IF' statements.
/// </summary>
/// <returns>
/// ExtractsModel or null if a connection cannot be made to the OLAP DB.
/// </returns>
/// <param name="export">
/// Whether the data is for an Export or not (so all data is retrieved instead of top 10)
/// </param>
/// <param name="dataSourceID">
/// The DataSourceID to retrieve the Extracts for
/// </param>
/// <param name="pnr">
/// The PNR to retrieve the Extracts for
/// </param>
/// <param name="recipientID">
/// The RecipientID to retrieve the Extracts for
/// </param>
public ExtractsModel ExtractsReport(int dataSourceID = 0, int recipientID = 0, string pnr = "", bool export = false)
{
ExtractsModel model = new ExtractsModel();
log.Debug("Retrieving Extracts");
model.ExtractID = new List<string>();
model.PNR = new List<string>();
model.LeadPaxName = new List<string>();
model.TotalPax = new List<string>();
model.UploadDateTime = new List<string>();
model.BookingDate = new List<string>();
model.EmailAddress = new List<string>();
model.OtherPhone = new List<string>();
model.LanguageCode = new List<string>();
model.NumberofFlights = new List<int>();
using (AdomdConnection con = new AdomdConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
log.Debug("Opening connection to OLAP to retrieve data");
try
{
con.Open();
String mdxQuery = "";
if (dataSourceID != 0)
{
//MDX query by data source ID
log.Debug("Get Extracts by data source ID");
if (export == false)
{
mdxQuery = "SELECT [Measures].[Flights Dim Count]ON COLUMNS,NON EMPTY TOPCOUNT({[Extracts Dim].[Extractid].[Extractid].members *[Extracts Dim].[PNR].[PNR].members *[Extracts Dim].[Address Name].[Address Name].members *[Extracts Dim].[Total Pax].[Total Pax].members *[Extracts Dim].[Date Added].[Date Added].members *[Extracts Dim].[Booking Date].[Booking Date].members * [Extracts Dim].[Email Address].[Email Address].members * [Extracts Dim].[Other Phone].[Other Phone].members * [Extracts Dim].[Language Code].[Language Code].members}, 10, [Extracts Dim].[Extractid]) ON ROWS FROM [Data Warehouse] WHERE [Data Source Dim].[Data Source ID].&[" + dataSourceID + "]";
}
else
{
mdxQuery = "SELECT [Measures].[Flights Dim Count]ON COLUMNS,NON EMPTY {[Extracts Dim].[Extractid].[Extractid].members *[Extracts Dim].[PNR].[PNR].members *[Extracts Dim].[Address Name].[Address Name].members *[Extracts Dim].[Total Pax].[Total Pax].members *[Extracts Dim].[Date Added].[Date Added].members *[Extracts Dim].[Booking Date].[Booking Date].members * [Extracts Dim].[Email Address].[Email Address].members * [Extracts Dim].[Other Phone].[Other Phone].members * [Extracts Dim].[Language Code].[Language Code].members} ON ROWS FROM [Data Warehouse] WHERE [Data Source Dim].[Data Source ID].&[" + dataSourceID + "]";
}
}
else if (recipientID != 0)
{
//MDX query by recipientID
log.Debug("Get Extracts by recipientID");
if (export == false)
{
mdxQuery = "SELECT [Measures].[Flights Dim Count]ON COLUMNS,NON EMPTY TOPCOUNT({[Extracts Dim].[Extractid].[Extractid].members *[Extracts Dim].[PNR].[PNR].members *[Extracts Dim].[Address Name].[Address Name].members *[Extracts Dim].[Total Pax].[Total Pax].members *[Extracts Dim].[Date Added].[Date Added].members *[Extracts Dim].[Booking Date].[Booking Date].members * [Extracts Dim].[Email Address].[Email Address].members * [Extracts Dim].[Other Phone].[Other Phone].members * [Extracts Dim].[Language Code].[Language Code].members}, 10, [Extracts Dim].[Extractid]) ON ROWS FROM [Data Warehouse] WHERE [Recipients Dim].[Recipient ID].&[" + recipientID + "]";
}
else
{
mdxQuery = "SELECT [Measures].[Flights Dim Count]ON COLUMNS,NON EMPTY {[Extracts Dim].[Extractid].[Extractid].members *[Extracts Dim].[PNR].[PNR].members *[Extracts Dim].[Address Name].[Address Name].members *[Extracts Dim].[Total Pax].[Total Pax].members *[Extracts Dim].[Date Added].[Date Added].members *[Extracts Dim].[Booking Date].[Booking Date].members * [Extracts Dim].[Email Address].[Email Address].members * [Extracts Dim].[Other Phone].[Other Phone].members * [Extracts Dim].[Language Code].[Language Code].members} ON ROWS FROM [Data Warehouse] WHERE [Recipients Dim].[Recipient ID].&[" + recipientID + "]";
}
}
else if (pnr != "")
{
//MDX query by PNR
log.Debug("Get Extracts by PNR");
if (export == false)
{
mdxQuery = "SELECT [Measures].[Flights Dim Count]ON COLUMNS,NON EMPTY TOPCOUNT(FILTER({[Extracts Dim].[Extractid].[Extractid].members *[Extracts Dim].[PNR].[PNR].members *[Extracts Dim].[Address Name].[Address Name].members *[Extracts Dim].[Total Pax].[Total Pax].members *[Extracts Dim].[Date Added].[Date Added].members*[Extracts Dim].[Booking Date].[Booking Date].members * [Extracts Dim].[Email Address].[Email Address].members * [Extracts Dim].[Other Phone].[Other Phone].members * [Extracts Dim].[Language Code].[Language Code].members},instr([Extracts Dim].[PNR].currentmember.member_caption,'" + pnr + "')>0), 10, [Extracts Dim].[Extractid]) ON ROWS FROM [Data Warehouse]";
}
else
{
mdxQuery = "SELECT [Measures].[Flights Dim Count]ON COLUMNS,NON EMPTY FILTER({[Extracts Dim].[Extractid].[Extractid].members *[Extracts Dim].[PNR].[PNR].members *[Extracts Dim].[Address Name].[Address Name].members *[Extracts Dim].[Total Pax].[Total Pax].members *[Extracts Dim].[Date Added].[Date Added].members*[Extracts Dim].[Booking Date].[Booking Date].members * [Extracts Dim].[Email Address].[Email Address].members * [Extracts Dim].[Other Phone].[Other Phone].members * [Extracts Dim].[Language Code].[Language Code].members},instr([Extracts Dim].[PNR].currentmember.member_caption,'" + pnr + "')>0) ON ROWS FROM [Data Warehouse]";
}
}
else
{
//MDX top 10
log.Debug("Get Extracts by top 10");
mdxQuery = "SELECT [Measures].[Flights Dim Count]ON COLUMNS,NON EMPTY TOPCOUNT({[Extracts Dim].[Extractid].[Extractid].members *[Extracts Dim].[PNR].[PNR].members *[Extracts Dim].[Address Name].[Address Name].members *[Extracts Dim].[Total Pax].[Total Pax].members *[Extracts Dim].[Date Added].[Date Added].members *[Extracts Dim].[Booking Date].[Booking Date].members * [Extracts Dim].[Email Address].[Email Address].members * [Extracts Dim].[Other Phone].[Other Phone].members * [Extracts Dim].[Language Code].[Language Code].members}, 10, [Extracts Dim].[Extractid]) ON ROWS FROM [Data Warehouse]";
}
using (AdomdCommand command = new AdomdCommand(mdxQuery, con))
{
try
{
using (AdomdDataReader reader = command.ExecuteReader())
{
int recordNumber = 0;
while (reader.Read())
{
log.Info("Reading Record #: " + recordNumber);
for (int i = 0; i < reader.FieldCount; i++)
{
log.Debug("Reading Field #: " + i);
log.Debug("Returned Field: " + reader[i]);
//add the data set returned to the model
switch (i)
{
case 0:
model.ExtractID.Add((string)reader[i]);
break;
case 1:
model.PNR.Add((string)reader[i]);
break;
case 2:
model.LeadPaxName.Add((string)reader[i]);
break;
case 3:
model.TotalPax.Add((string)reader[i]);
break;
case 4:
model.UploadDateTime.Add((string)reader[i]);
break;
case 5:
model.BookingDate.Add((string)reader[i]);
break;
case 6:
model.EmailAddress.Add((string)reader[i]);
break;
case 7:
model.OtherPhone.Add((string)reader[i]);
break;
case 8:
model.LanguageCode.Add((string)reader[i]);
break;
case 9:
model.NumberofFlights.Add((int)reader[i]);
break;
}
}
}
recordNumber++;
}
}
catch (AdomdErrorResponseException ex)
{
log.Error("ERROR when trying to read recrods");
log.Error(ex);
return null;
}
}
con.Close();
}
catch (Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException ex)
{
log.Error(ex);
return null;
}
}
return model;
}
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 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);
}
}
}
/// <summary>
/// Method that is called to retrieve Flights data from the OLAP DB. Method
/// will put data into the Flights Report Model. The retrieve criteria depends on the
/// parameters provided, and there is an order of priority within the 'IF' statements.
/// </summary>
/// <returns>
/// FlightsModel or null if a connection cannot be made to the OLAP DB.
/// </returns>
/// <param name="extractID">
/// The ExtractID to retrieve the Flights for
/// </param>
/// <param name="flightID">
/// The FlightID to retrieve the Flights for
/// </param>
public FlightsModel FlightsReport(int flightID = 0, int extractID = 0)
{
FlightsModel model = new FlightsModel();
model.FlightID = new List<string>();
model.FlightLeg = new List<string>();
model.FlightNumber = new List<string>();
model.FlightCode = new List<string>();
model.DepartureDate = new List<string>();
model.DepartureTime = new List<string>();
model.DepartureCountry = new List<string>();
model.DepartureCity = new List<string>();
model.ArrivalDate = new List<string>();
model.ArrivalTime = new List<string>();
model.ArrivalCountry = new List<string>();
model.ArrivalCity = new List<string>();
model.ScheduleChange = new List<string>();
model.ServiceClass = new List<string>();
model.Duration = new List<string>();
using (AdomdConnection con = new AdomdConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
log.Debug("Opening connection to OLAP to retrieve data");
try
{
con.Open();
String mdxQuery = "";
if (flightID != 0)
{
//MDX query by flightID
log.Debug("Get Flights by flightID");
mdxQuery = "SELECT NONEMPTY (ORDER({[Flights Dim].[Flight ID].[" + flightID + "] *[Flights Dim].[Flight Leg].[Flight Leg].members *[Flights Dim].[Flight Number].[Flight Number].members *[Flights Dim].[Flight Code].[Flight Code].members *[Flights Dim].[Departure Date].[Departure Date].members *[Flights Dim].[Departure Time].[Departure Time].members *[Flights Dim].[Departure Country].[Departure Country].members *[Flights Dim].[Departure City].[Departure City].members *[Flights Dim].[Arrival Date].[Arrival Date].members *[Flights Dim].[Arrival Time].[Arrival Time].members *[Flights Dim].[Arrival Country].[Arrival Country].members *[Flights Dim].[Arrival City].[Arrival City].members *[Flights Dim].[Schedule Change].[Schedule Change].members *[Flights Dim].[Service Class].[Service Class].members *[Flights Dim].[Duration].[Duration].members },[Flights Dim].[Flight Leg], asc) )ON ROWS, {[Measures].[Fact Table Count]} ON COLUMNS FROM [Data Warehouse] ";
}
else if (extractID != 0)
{
//MDX query by extractID
log.Debug("Get Flights by extractID");
mdxQuery = "SELECT NONEMPTY (ORDER({[Flights Dim].[Flight ID].[Flight ID] *[Flights Dim].[Flight Leg].[Flight Leg].members *[Flights Dim].[Flight Number].[Flight Number].members *[Flights Dim].[Flight Code].[Flight Code].members *[Flights Dim].[Departure Date].[Departure Date].members *[Flights Dim].[Departure Time].[Departure Time].members *[Flights Dim].[Departure Country].[Departure Country].members *[Flights Dim].[Departure City].[Departure City].members *[Flights Dim].[Arrival Date].[Arrival Date].members *[Flights Dim].[Arrival Time].[Arrival Time].members *[Flights Dim].[Arrival Country].[Arrival Country].members *[Flights Dim].[Arrival City].[Arrival City].members *[Flights Dim].[Schedule Change].[Schedule Change].members *[Flights Dim].[Service Class].[Service Class].members *[Flights Dim].[Duration].[Duration].members },[Flights Dim].[Flight Leg], asc) )ON ROWS, {[Measures].[Fact Table Count]} ON COLUMNS FROM [Data Warehouse] WHERE [Extracts Dim].[Extractid].&[" + extractID + "]";
}
using (AdomdCommand command = new AdomdCommand(mdxQuery, con))
{
try
{
using (AdomdDataReader reader = command.ExecuteReader())
{
int recordNumber = 0;
while (reader.Read())
{
log.Info("Reading Record #: " + recordNumber);
{
for (int i = 0; i < reader.FieldCount; i++)
{
log.Debug("Reading Field #: " + i);
log.Debug("Returned Field: " + reader[i]);
//add the data set returned to the model
switch (i)
{
case 0:
model.FlightID.Add((string)reader[i]);
break;
case 1:
model.FlightLeg.Add((string)reader[i]);
break;
case 2:
model.FlightNumber.Add((string)reader[i]);
break;
case 3:
model.FlightCode.Add((string)reader[i]);
break;
case 4:
model.DepartureDate.Add((string)reader[i]);
break;
case 5:
model.DepartureTime.Add((string)reader[i]);
break;
case 6:
model.DepartureCountry.Add((string)reader[i]);
break;
case 7:
model.DepartureCity.Add((string)reader[i]);
break;
case 8:
model.ArrivalDate.Add((string)reader[i]);
break;
case 9:
model.ArrivalTime.Add((string)reader[i]);
break;
case 10:
model.ArrivalCountry.Add((string)reader[i]);
break;
case 11:
model.ArrivalCity.Add((string)reader[i]);
break;
case 12:
model.ScheduleChange.Add((string)reader[i]);
break;
case 13:
model.ServiceClass.Add((string)reader[i]);
break;
case 14:
model.Duration.Add((string)reader[i]);
break;
}
}
}
recordNumber++;
}
}
}
catch (AdomdErrorResponseException ex)
{
log.Error("ERROR when trying to read recrods");
log.Error(ex);
return null;
}
}
con.Close();
}
catch (Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException ex)
{
log.Error(ex);
return null;
}
}
return model;
}
private void btnAtuCubo_Click(object sender, EventArgs e)
{
try
{
string linha = "";
lblTexto.Visible = true;
prgrsBrCarrega.Value = 0;
prgrsBrCarrega.Visible = true;
tmrTempo.Enabled = true;
//Abrir o arquivo
linha += "<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>Comercial_Mart</DatabaseID> " +
"<DimensionID>Regiao</DimensionID> " +
"</Object> " +
"<Type>ProcessUpdate</Type> " +
"<WriteBackTableCreation>UseExisting</WriteBackTableCreation> " +
"</Process> " +
"<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>Comercial_Mart</DatabaseID> " +
"<CubeID>Vendas_Regiao_Produto</CubeID> " +
"</Object> " +
"<Type>ProcessFull</Type> " +
"<WriteBackTableCreation>UseExisting</WriteBackTableCreation> " +
"</Process> " +
"<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>Comercial_Mart</DatabaseID> " +
"<DimensionID>Produto</DimensionID> " +
"</Object> " +
"<Type>ProcessUpdate</Type> " +
"<WriteBackTableCreation>UseExisting</WriteBackTableCreation> " +
"</Process> " +
"<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>Comercial_Mart</DatabaseID> " +
"<DimensionID>Data</DimensionID> " +
"</Object> " +
"<Type>ProcessUpdate</Type> " +
"<WriteBackTableCreation>UseExisting</WriteBackTableCreation> " +
"</Process> " +
"</Parallel> " +
"</Batch>";
//FileInfo x = new FileInfo(@"E:\Faculdade\Tcc_google\Prototipo\atucubo.txt");
// StreamReader valor = x.OpenText();
//while (valor.ReadLine() != null)
// {
// linha += valor.ReadLine();
//}
string c = ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString_analysis"].ConnectionString;
AdomdConnection conn = new AdomdConnection(c);
conn.Open();
AdomdCommand cmd = new AdomdCommand(linha, conn);
cmd.Execute();
conn.Close();
COMERCIALDataSetTableAdapters.ATUCUBOTableAdapter atu = new Comercial.COMERCIALDataSetTableAdapters.ATUCUBOTableAdapter();
DateTime data = Convert.ToDateTime(DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day);
atu.Insert(_princ.usuarioLogado, data);
preencheGrid();
prgrsBrCarrega.Value = 100;
tmrTempo.Enabled = false;
lblTexto.Visible = false;
MessageBox.Show("Processo executado com sucesso", "Atenção", MessageBoxButtons.OK, MessageBoxIcon.Information);
pictureBox1.Visible = true;
pictureBox2.Visible = true;
}
catch(Exception ex)
{
tmrTempo.Enabled = false;
lblTexto.Visible = false;
prgrsBrCarrega.Visible = false;
MessageBox.Show("Houve um problema na execução da atualização!\nConsulte o DBA.", "Atenção", MessageBoxButtons.OK, MessageBoxIcon.Error);
pictureBox1.Visible = false;
pictureBox1.Visible = false;
}
}
/// <summary>
/// Method that is called to retrieve Recipients data from the OLAP DB. Method
/// will put data into the Recipients Report Model. The retrieve criteria depends on the
/// parameters provided, and there is an order of priority within the 'IF' statements.
/// </summary>
/// <returns>
/// RecipientModel or null if a connection cannot be made to the OLAP DB.
/// </returns>
/// <param name="export">
/// Whether the data is for an Export or not (so all data is retrieved instead of top 10)
/// </param>
/// <param name="extractID">
/// The ExtractID to retrieve the Recipients for
/// </param>
/// <param name="notificationID">
/// The NotificationID to retrieve the Recipients for
/// </param>
/// <param name="sentTo">
/// The SentTo to retrieve the Recipients for
/// </param>
public RecipientModel RecipientsReport(int notificationID = 0, int extractID = 0, string sentTo = "", bool export = false)
{
RecipientModel model = new RecipientModel();
model.RecipientID = new List<string>();
model.SentTo = new List<string>();
model.SendDateTime = new List<string>();
model.NotificationType = new List<string>();
model.TemplateName = new List<string>();
using (AdomdConnection con = new AdomdConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
log.Debug("Opening connection to OLAP to retrieve data");
try
{
con.Open();
String mdxQuery = "";
if (notificationID != 0)
{
//MDX query by notificationID
log.Debug("Get Recipients by NotificationID");
if (export == false)
{
mdxQuery = "SELECT [Measures].[Recipients Dim Count]ON COLUMNS,NON EMPTY TOPCOUNT({[Recipients Dim].[Recipient ID].[Recipient ID].members *[Recipients Dim].[Recipient Sent To].[Recipient Sent To].members *[Recipients Dim].[Recipient Sent Date Time].[Recipient Sent Date Time].members *[Recipients Dim].[Sent Type].[Sent Type].members *[Templates Dim].[Template Desc].[Template Desc].members}, 10, [Recipients Dim].[Recipient ID]) ON ROWS FROM [Data Warehouse] WHERE [Notifications Dim].[Notification ID].&[" + notificationID + "]";
}
else
{
mdxQuery = "SELECT [Measures].[Recipients Dim Count]ON COLUMNS,NON EMPTY {[Recipients Dim].[Recipient ID].[Recipient ID].members *[Recipients Dim].[Recipient Sent To].[Recipient Sent To].members *[Recipients Dim].[Recipient Sent Date Time].[Recipient Sent Date Time].members *[Recipients Dim].[Sent Type].[Sent Type].members *[Templates Dim].[Template Desc].[Template Desc].members} ON ROWS FROM [Data Warehouse] WHERE [Notifications Dim].[Notification ID].&[" + notificationID + "]";
}
}
else if (extractID != 0)
{
//MDX query by extractID
log.Debug("Get Recipients by extractID");
if (export == false)
{
mdxQuery = "SELECT [Measures].[Recipients Dim Count]ON COLUMNS,NON EMPTY TOPCOUNT({[Recipients Dim].[Recipient ID].[Recipient ID].members *[Recipients Dim].[Recipient Sent To].[Recipient Sent To].members *[Recipients Dim].[Recipient Sent Date Time].[Recipient Sent Date Time].members *[Recipients Dim].[Sent Type].[Sent Type].members *[Templates Dim].[Template Desc].[Template Desc].members}, 10, [Recipients Dim].[Recipient ID]) ON ROWS FROM [Data Warehouse] WHERE [Extracts Dim].[Extractid].&[" + extractID + "]";
}
else
{
mdxQuery = "SELECT [Measures].[Recipients Dim Count]ON COLUMNS,NON EMPTY {[Recipients Dim].[Recipient ID].[Recipient ID].members *[Recipients Dim].[Recipient Sent To].[Recipient Sent To].members *[Recipients Dim].[Recipient Sent Date Time].[Recipient Sent Date Time].members *[Recipients Dim].[Sent Type].[Sent Type].members *[Templates Dim].[Template Desc].[Template Desc].members} ON ROWS FROM [Data Warehouse] WHERE [Extracts Dim].[Extractid].&[" + extractID + "]";
}
}
else if (sentTo != "")
{
//MDX query by sentTo
log.Debug("Get Recipients by sentTo");
if (export == false)
{
mdxQuery = "SELECT [Measures].[Recipients Dim Count]ON COLUMNS,NON EMPTY TOPCOUNT(FILTER({[Recipients Dim].[Recipient ID].[Recipient ID].members *[Recipients Dim].[Recipient Sent To].[Recipient Sent To].members *[Recipients Dim].[Recipient Sent Date Time].[Recipient Sent Date Time].members *[Recipients Dim].[Sent Type].[Sent Type].members*[Templates Dim].[Template Desc].[Template Desc].members},instr([Recipients Dim].[Recipient Sent To].currentmember.member_caption,'" + sentTo + "')>0), 10, [Recipients Dim].[Recipient ID]) ON ROWS FROM [Data Warehouse]";
}
else
{
mdxQuery = "SELECT [Measures].[Recipients Dim Count]ON COLUMNS,NON EMPTY FILTER({[Recipients Dim].[Recipient ID].[Recipient ID].members *[Recipients Dim].[Recipient Sent To].[Recipient Sent To].members *[Recipients Dim].[Recipient Sent Date Time].[Recipient Sent Date Time].members *[Recipients Dim].[Sent Type].[Sent Type].members*[Templates Dim].[Template Desc].[Template Desc].members},instr([Recipients Dim].[Recipient Sent To].currentmember.member_caption,'" + sentTo + "')>0) ON ROWS FROM [Data Warehouse]";
}
}
else
{
//MDX get top 10
log.Debug("Get Recipients by top 10");
mdxQuery = "SELECT [Measures].[Recipients Dim Count] ON COLUMNS, NON EMPTY TOPCOUNT({[Recipients Dim].[Recipient ID].[Recipient ID].members *[Recipients Dim].[Recipient Sent To].[Recipient Sent To].members *[Recipients Dim].[Recipient Sent Date Time].[Recipient Sent Date Time].members *[Recipients Dim].[Sent Type].[Sent Type].members *[Templates Dim].[Template Desc].[Template Desc].members}, 10, [Recipients Dim].[Recipient ID]) ON ROWS FROM [Data Warehouse]";
}
using (AdomdCommand command = new AdomdCommand(mdxQuery, con))
{
try
{
using (AdomdDataReader reader = command.ExecuteReader())
{
int recordNumber = 0;
while (reader.Read())
{
log.Info("Reading Record #: " + recordNumber);
{
for (int i = 0; i < reader.FieldCount; i++)
{
log.Debug("Reading Field #: " + i);
log.Debug("Returned Field: " + reader[i]);
//add the data set returned to the model
switch (i)
{
case 0:
model.RecipientID.Add((string)reader[i]);
break;
case 1:
model.SentTo.Add((string)reader[i]);
break;
case 2:
model.SendDateTime.Add((string)reader[i]);
break;
case 3:
model.NotificationType.Add((string)reader[i]);
break;
case 4:
model.TemplateName.Add((string)reader[i]);
break;
}
}
}
recordNumber++;
}
}
}
catch (AdomdErrorResponseException ex)
{
log.Error("ERROR when trying to read recrods");
log.Error(ex);
return null;
}
}
con.Close();
}
catch (Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException ex)
{
log.Error(ex);
return null;
}
}
return model;
}
/// <summary>
/// Conect to Analysis Services and execute MDX query.
/// </summary>
/// <param name="query">MDX query to execute (Analysis Services)</param>
public static void ExecuteMDXQuery(string query)
{
string connectionString =
ConfigurationManager.ConnectionStrings["ConnectionStringAnalysisServices"].ConnectionString;
var myconnect = new AdomdConnection(connectionString);
var commandToExecute = new AdomdCommand(query, myconnect);
myconnect.Open();
commandToExecute.Execute();
myconnect.Close();
}
public ArrayList MDXquery(string strQuery, string connectionString, string txtUser, string txtPassword, string strCatalogName)
{
ArrayList al = new ArrayList();
string str = "";
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US");
Thread.CurrentThread.CurrentUICulture = CultureInfo.CreateSpecificCulture("en-US");
//AdomdConnection conn = new AdomdConnection(connectionString + ";UID=" + txtUser + ";PWD=" + txtPassword + ";Catalog=" + strCatalogName);
try
{
AdomdConnection conn = new AdomdConnection(connectionString + ";UID=" + txtUser + ";PWD=" + txtPassword);
conn.Open();
AdomdCommand cmd = new AdomdCommand(strQuery, conn);
CellSet cst = cmd.ExecuteCellSet();
str = "";
for (int i = 0; i < cst.Axes[1].Set.Tuples[0].Members.Count; i++)
{
if (i > 0)
str += "\t";
Microsoft.AnalysisServices.AdomdClient.Member myMember = cst.Axes[1].Set.Tuples[0].Members[i];
string strMember = myMember.LevelName;
strMember = strMember.Replace("[", "").Replace("]", "");
str += strMember;
}
for (int i = 0; i < cst.Axes[0].Set.Tuples.Count; i++)
{
for (int j = 0; j < cst.Axes[0].Set.Tuples[i].Members.Count; j++)
{
str += "\t" + cst.Axes[0].Set.Tuples[i].Members[j].Caption;
}
}
al.Add(str);
for (int j = 0; j < cst.Axes[1].Set.Tuples.Count; j++)
{
str = "";
for (int k = 0; k < cst.Axes[1].Set.Tuples[j].Members.Count; k++)
{
if (k > 0)
str += "\t";
str += cst.Axes[1].Set.Tuples[j].Members[k].Caption;
}
for (int k = 0; k < cst.Axes[0].Set.Tuples.Count; k++)
{
str += "\t";
str += cst.Cells[k, j].Value;
}
al.Add(str);
}
conn.Close();
}
catch (Exception e)
{
string errorMessage = e.Source + " " + e.Message;
al.Add(errorMessage);
//MessageBox.Show(e.Source + " " + e.Message);
}
return al;
}
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);
}
finally
{
cmd.Dispose();
cn.Close();
cn.Dispose();
}
}
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();
svr.Dispose();
}
}
}
}
finally
{
ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
ApplicationObject.StatusBar.Progress(false, "Deploying MdxScript", 5, 5);
}
}
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();
}
}
// was (L3ABwHrWDParams param)
public void GetData()
{
// TBD, make view-model consisting of replicating L3 results and details for L2 results
// 1) create view-model model
// 2) populate L3-model sub-model
// 3) populate L2-model sub-model
//
DataSet ds = new DataSet();
// work-around for non-functioning constructor: results = new List<L2ABwHrWD>();
string command = @"
SELECT NON EMPTY { [Measures].[Bikes] } ON COLUMNS,
NON EMPTY { ([CentroidA].[Level2 Locality].[Level2 Locality].ALLMEMBERS *
[CentroidB].[Level2 Locality].[Level2 Locality].ALLMEMBERS *
[Time Table].[Hourofday].[Hourofday].ALLMEMBERS *
[Time Table].[Nameofday].[Nameofday].ALLMEMBERS *
[Time Table].[Dayofweek].[Dayofweek].ALLMEMBERS )
} DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS
FROM
(
SELECT ( STRTOSET(@DirectionDirection, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( STRTOSET(@CentroidBLevelLocality, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( STRTOSET(@CentroidALevelLocality, CONSTRAINED) ) ON COLUMNS
FROM [Bikeshare]
)
)
)
WHERE (
IIF(
STRTOSET(@CentroidALevelLocality, CONSTRAINED).Count = 1,
STRTOSET(@CentroidALevelLocality, CONSTRAINED),
[CentroidA].[Level3 Locality].currentmember
),
IIF(
STRTOSET(@CentroidBLevelLocality, CONSTRAINED).Count = 1,
STRTOSET(@CentroidBLevelLocality, CONSTRAINED),
[CentroidB].[Level3 Locality].currentmember
),
IIF(
STRTOSET(@DirectionDirection, CONSTRAINED).Count = 1,
STRTOSET(@DirectionDirection, CONSTRAINED),
[Direction].[Direction].currentmember
)
)";
using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2"))
{
conn.Open();
using (AdomdCommand cmd = new AdomdCommand(command, conn))
{
cmd.Parameters.Add(new AdomdParameter("DirectionDirection", queryparameters.Direction.UniqueName));
cmd.Parameters.Add(new AdomdParameter("CentroidALevelLocality", queryparameters.Level3A.UniqueName));
cmd.Parameters.Add(new AdomdParameter("CentroidBLevelLocality", queryparameters.Level3B.UniqueName));
AdomdDataAdapter adapter = new AdomdDataAdapter(cmd);
adapter.Fill(ds);
}
conn.Close();
}
// results in ds.Tables[0]
// need to transfer to new list
DataTable dt = ds.Tables[0];
Dictionary<string,int> colnbr = new Dictionary<string,int >();
//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)
{
L2ABwHrWD oneresult = new L2ABwHrWD(dr, colnbr);
results.Add(oneresult);
}
}
/// <summary>
/// Method that is called to retrieve Data Sources data from the OLAP DB. Method
/// will put data into the Data Sources Report Model. The retrieve criteria depends on the
/// parameters provided, and there is an order of priority within the 'IF' statements.
/// </summary>
/// <returns>
/// DataSourcesModel or null if a connection cannot be made to the OLAP DB.
/// </returns>
/// <param name="datasourcename">
/// The Data Source Name to retrieve the Data Sources for
/// </param>
/// <param name="extractID">
/// The ExtractID to retrive the Data Sources for
/// </param>
/// <param name="notificationID">
/// The NotificationID to retrieve the Data Sources for
/// </param>
public DataSourcesModel DataSourcesReport(int notificationID = 0, string datasourcename = "", int extractID = 0)
{
DataSourcesModel model = new DataSourcesModel();
log.Debug("Retrieving Data Sources");
model.DataSourceID = new List<string>();
model.DataSourceName = new List<string>();
model.ExtractsCount = new List<int>();
model.UploadDateTime = new List<string>();
model.DataSourceType = new List<string>();
using (AdomdConnection con = new AdomdConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
log.Debug("Opening connection to OLAP to retrieve data");
try
{
con.Open();
String mdxQuery = "";
if (notificationID != 0)
{
//MDX query by notificationID
log.Debug("Get Data Sources by notificationID");
mdxQuery = "SELECT [Measures].[Extracts Dim Count]ON COLUMNS,NON EMPTY TOPCOUNT({[Data Source Dim].[Data Source ID].[Data Source ID].members *[Data Source Dim].[Data Source Name].[Data Source Name].members *[Data Source Dim].[Create Date].[Create Date].members *[Data Source Dim].[Data Source Type].[Data Source Type].members}, 10, [Data Source Dim].[Data Source ID]) ON ROWS FROM [Data Warehouse] WHERE [Notifications Dim].[Notification ID].&[" + notificationID + "]";
}
else if (extractID != 0)
{
//MDX query by extractID
log.Debug("Get Data Sources by extractID");
mdxQuery = "SELECT [Measures].[Extracts Dim Count] ON COLUMNS,NON EMPTY TOPCOUNT({[Data Source Dim].[Data Source ID].[Data Source ID].members *[Data Source Dim].[Data Source Name].[Data Source Name].members *[Data Source Dim].[Create Date].[Create Date].members *[Data Source Dim].[Data Source Type].[Data Source Type].members}, 10, [Data Source Dim].[Data Source ID]) ON ROWS FROM [Data Warehouse] WHERE [Extracts Dim].[Extractid].&[" + extractID + "]";
}
else if (datasourcename != "")
{
//MDX query by data source name
log.Debug("Get Data Sources by data source name");
mdxQuery = "SELECT [Measures].[Extracts Dim Count]ON COLUMNS,NON EMPTY TOPCOUNT(FILTER({[Data Source Dim].[Data Source ID].[Data Source ID].members *[Data Source Dim].[Data Source Name].[Data Source Name].members *[Data Source Dim].[Create Date].[Create Date].members *[Data Source Dim].[Data Source Type].[Data Source Type].members},instr([Data Source Dim].[Data Source Name].currentmember.member_caption,'" + datasourcename + "')>0), 10, [Data Source Dim].[Data Source ID]) ON ROWS FROM [Data Warehouse]";
}
else
{
//MDX top 10
log.Debug("Get Data Sources by top 10");
mdxQuery = "SELECT [Measures].[Extracts Dim Count]ON COLUMNS,NON EMPTY TOPCOUNT({[Data Source Dim].[Data Source ID].[Data Source ID].members *[Data Source Dim].[Data Source Name].[Data Source Name].members *[Data Source Dim].[Create Date].[Create Date].members *[Data Source Dim].[Data Source Type].[Data Source Type].members}, 10, [Data Source Dim].[Data Source ID]) ON ROWS FROM [Data Warehouse]";
}
using (AdomdCommand command = new AdomdCommand(mdxQuery, con))
{
try
{
using (AdomdDataReader reader = command.ExecuteReader())
{
int recordNumber = 0;
while (reader.Read())
{
log.Info("Reading Record #: " + recordNumber);
for (int i = 0; i < reader.FieldCount; i++)
{
log.Debug("Reading Field #: " + i);
log.Debug("Returned Field: " + reader[i]);
//field in records coming back in the following order:
//0 - DataSourceID
//1 - DataSourceName
//2 - ExtractsCount
//3 - UploadDateTime
//4 - DataSourceType
//add the data set returned to the model
switch (i)
{
case 0:
model.DataSourceID.Add((string)reader[i]);
break;
case 1:
model.DataSourceName.Add((string)reader[i]);
break;
case 4:
model.ExtractsCount.Add((int)reader[i]);
break;
case 2:
model.UploadDateTime.Add((string)reader[i]);
break;
case 3:
model.DataSourceType.Add((string)reader[i]);
break;
}
}
recordNumber++;
}
}
}
catch (AdomdErrorResponseException ex)
{
log.Error("ERROR when trying to read recrods");
log.Error(ex);
return null;
}
}
con.Close();
}
catch (Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException ex)
{
log.Error(ex);
return null;
}
}
return model;
}
public virtual ParserResult Parse()
{
Trace.WriteLineIf(NBiTraceSwitch.TraceVerbose, command.CommandText);
foreach (AdomdParameter param in command.Parameters)
Trace.WriteLineIf(NBiTraceSwitch.TraceVerbose, string.Format("{0} => {1}", param.ParameterName, param.Value));
ParserResult res = null;
using (var connection = new AdomdConnection())
{
var connectionString = command.Connection.ConnectionString;
try
{
connection.ConnectionString = connectionString;
connection.Open();
}
catch (ArgumentException ex)
{ throw new ConnectionException(ex, connectionString); }
using (AdomdCommand cmdIn = new AdomdCommand(command.CommandText, connection))
{
foreach (AdomdParameter param in command.Parameters)
{
var p = param.Clone();
cmdIn.Parameters.Add(p);
}
try
{
cmdIn.ExecuteReader(CommandBehavior.SchemaOnly);
res = ParserResult.NoParsingError();
}
catch (AdomdException ex)
{
res = new ParserResult(ex.Message.Split(new string[] { "\r\n" }, System.StringSplitOptions.RemoveEmptyEntries));
}
}
if (connection.State != System.Data.ConnectionState.Closed)
connection.Close();
}
return res;
}
private void Page_Load(object sender, System.EventArgs e)
{
AdomdConnection conn = new AdomdConnection(ConfigurationSettings.AppSettings["adomdConn"]);
conn.Open();
AdomdCommand comm = new AdomdCommand(@"SELECT {{[Age Range_Member].[Age Range].Members}*{[Gender_Employee].[Gender_Employee].Members}} on rows, {{[Measures].[Member Months],[Measures].[Allowed]} * {[Benefit Plan].[Benefit Plan Type].Members}} on columns from [RCBigMainCube]",conn);
CellSet cst = comm.ExecuteCellSet();
CubeDef def = conn.Cubes["RCBigMainCube"];
conn.Close();
Dimension dim = def.Dimensions["Age Range_Member"];
StringBuilder str = new StringBuilder(),
strTmp = new StringBuilder();
int axisRow = 1,axisCol = 0;
int i,j,k; // indexers
string tmpPrev,tmpCur;//temp string to hjold previous and current value
HierarchyCollection rowHierarchy = cst.Axes[axisRow].Set.Hierarchies,
colHierarchy = cst.Axes[axisCol].Set.Hierarchies;
TupleCollection rowTuples = cst.Axes[axisRow].Set.Tuples,
colTuples = cst.Axes[axisCol].Set.Tuples;
int rowHierCnt = rowHierarchy.Count,
colHierCnt = colHierarchy.Count,
rowTuplCnt = colTuples.Count,
colTuplCnt = colTuples.Count;
str.Append("<table class=\"tableStyle\" cellspacing=\"0\">");
/********************************************Write the column header*************************************************/
/***************Write col dimensions*****************/
str.Append("<tr nowrap class=\"trStyle\">");
for(j=0;j<rowHierCnt;j++) str.Append("<td nowrap class=\"tdStyle\"> </td>");
for(j=0;j<colHierCnt;j++)
{
string dimName = colHierarchy[j].UniqueName;
str.Append("<td nowrap class=\"thStyle\"><b>");
if("Measures".Equals(dimName))
str.Append(dimName);
else
str.Append(Regex.Match(colTuples[0].Members[j].LevelName,@"(?<=\]\.\[)[^\]]+(?=\]$)").Value);
str.Append("</b></td>");
}
str.Append("</tr>");
/***************Write col dimensions*****************/
for(i=0;i<colHierCnt;i++)
{
str.Append("<tr nowrap class=\"trStyle\">");
for(j=0;j<rowHierCnt;j++) str.Append("<td nowrap class=\"tdStyle\"> </td>");
tmpPrev = string.Empty;
for(k=0;k<colTuplCnt;k++)
{
tmpCur = colTuples[k].Members[i].Caption;
if(tmpPrev.Equals(tmpCur)) tmpCur = " ";
else tmpPrev = tmpCur;
strTmp.Append("<td nowrap class=\"thStyle\"><b>");
strTmp.Append(tmpCur);
strTmp.Append("</b></td>");
}
str.Append("</tr>");
}
str.Append(strTmp.ToString());
/********************************************End of write the column header*************************************************/
for(i=0;i<rowTuplCnt;i++)
{
str.Append("<tr nowrap class=\"trStyle\">");
tmpPrev = string.Empty;
for(j=0;j<rowHierCnt;j++)
{
tmpCur = rowTuples[i].Members[j].Caption;
if(tmpPrev.Equals(tmpCur)) tmpCur = " ";
else tmpPrev = tmpCur;
str.Append("<td nowrap class=\"thStyle\"><b>");
str.Append(tmpCur);
str.Append("</b></td>");
}
for(k=0; k<colTuplCnt;k++)
{
tmpCur = cst.Cells[k,i].FormattedValue;
str.Append("<td nowrap class=\"tdStyle\">");
str.Append((tmpCur.Length == 0)?" " : tmpCur);
str.Append("</td>");
}
str.Append("</tr>");
}
str.Append("<table/>");
Response.Write(str.ToString());
// conn.Close();
}
/// <summary>
/// Method that is called to retrieve Passengers data from the OLAP DB. Method
/// will put data into the Passengers Report Model. The retrieve criteria depends on the
/// parameters provided, and there is an order of priority within the 'IF' statements.
/// </summary>
/// <returns>
/// PaxModel or null if a connection cannot be made to the OLAP DB.
/// </returns>
/// <param name="extractID">
/// The ExtractID to retrieve the PAX' for
/// </param>
/// <param name="PaxID">
/// The PaxID to retrieve the Passengers for
/// </param>
public PaxModel PaxReport(int PaxID = 0, int extractID = 0)
{
PaxModel model = new PaxModel();
model.PaxID = new List<string>();
model.PaxFirst = new List<string>();
model.PaxMiddle = new List<string>();
model.PaxLast = new List<string>();
model.PaxTitle = new List<string>();
model.PaxGender = new List<string>();
model.PaxTitle = new List<string>();
model.PaxDOB = new List<string>();
model.PaxType = new List<string>();
using (AdomdConnection con = new AdomdConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
log.Debug("Opening connection to OLAP to retrieve data");
try
{
con.Open();
String mdxQuery = "";
if (PaxID != 0)
{
//MDX query by PaxID
log.Debug("Get Pax by PaxID");
mdxQuery = "SELECT NONEMPTY (ORDER({[Pax Dim].[Pax ID].[" + PaxID + "].members *[Pax Dim].[Pax First].[Pax First].members *[Pax Dim].[Pax Middle].[Pax Middle].members *[Pax Dim].[Pax Last].[Pax Last].members *[Pax Dim].[Pax Title].[Pax Title].members *[Pax Dim].[Pax Gender].[Pax Gender].members *[Pax Dim].[Pax Type].[Pax Type].members *[Pax Dim].[Pax DOB].[Pax DOB].members },[Pax Dim].[Pax ID], asc) )ON ROWS, {[Measures].[Fact Table Count]} ON COLUMNS FROM [Data Warehouse] ";
}
else if (extractID != 0)
{
//MDX query by extractID
log.Debug("Get Flights by extractID");
mdxQuery = "SELECT NONEMPTY (ORDER({[Pax Dim].[Pax ID].[Pax ID].members *[Pax Dim].[Pax First].[Pax First].members *[Pax Dim].[Pax Middle].[Pax Middle].members *[Pax Dim].[Pax Last].[Pax Last].members *[Pax Dim].[Pax Title].[Pax Title].members *[Pax Dim].[Pax Gender].[Pax Gender].members *[Pax Dim].[Pax Type].[Pax Type].members *[Pax Dim].[Pax DOB].[Pax DOB].members },[Pax Dim].[Pax ID], asc) )ON ROWS, {[Measures].[Fact Table Count]} ON COLUMNS FROM [Data Warehouse] WHERE [Extracts Dim].[Extractid].&[" + extractID + "]";
}
using (AdomdCommand command = new AdomdCommand(mdxQuery, con))
{
try
{
using (AdomdDataReader reader = command.ExecuteReader())
{
int recordNumber = 0;
while (reader.Read())
{
log.Info("Reading Record #: " + recordNumber);
{
for (int i = 0; i < reader.FieldCount; i++)
{
log.Debug("Reading Field #: " + i);
log.Debug("Returned Field: " + reader[i]);
//add the data set returned to the model
switch (i)
{
case 0:
model.PaxID.Add((string)reader[i]);
break;
case 1:
model.PaxFirst.Add((string)reader[i]);
break;
case 2:
model.PaxMiddle.Add((string)reader[i]);
break;
case 3:
model.PaxLast.Add((string)reader[i]);
break;
case 4:
model.PaxTitle.Add((string)reader[i]);
break;
case 5:
model.PaxGender.Add((string)reader[i]);
break;
case 6:
model.PaxType.Add((string)reader[i]);
break;
case 7:
model.PaxDOB.Add((string)reader[i]);
break;
}
}
}
recordNumber++;
}
}
}
catch (AdomdErrorResponseException ex)
{
log.Error("ERROR when trying to read recrods");
log.Error(ex);
return null;
}
}
con.Close();
}
catch (Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException ex)
{
log.Error(ex);
return null;
}
}
return model;
}
/// <summary>
/// Method that is called to retrieve Notifications data from the OLAP DB. Method
/// will put data into the Notifications Report Model. The retrieve criteria depends on the
/// parameters provided, and there is an order of priority within the 'IF' statements.
/// </summary>
/// <returns>
/// NotificationsReportModel or null if a connection cannot be made to the OLAP DB.
/// </returns>
/// <param name="dataSourceID">
/// The DataSourceID to retrieve the Notifications for
/// </param>
/// <param name="extractID">
/// The ExtractID to retrieve the Notifications for
/// </param>
/// <param name="notificationDesc">
/// The Notification Description to retrieve the notifications for
/// </param>
/// <param name="notificationID">
/// The NotificationID to retrieve the Notifications for
/// </param>
/// <param name="recipientID">
/// The RecipientID to retrieve the Notifications for
/// </param>
public NotificationsReportModel NotificationsReport(int notificationID = 0, int dataSourceID = 0, string notificationDesc = "", int recipientID = 0, int extractID = 0)
{
NotificationsReportModel model = new NotificationsReportModel();
log.Info("Retrieving Notifications");
model.NotificationID = new List<string>();
model.NotificationDescription = new List<string>();
model.SendDateTime = new List<string>();
model.RecipientCount = new List<int>();
model.LanguageType = new List<string>();
using (AdomdConnection con = new AdomdConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
log.Debug("Opening connection to OLAP to retrieve data");
try
{
con.Open();
String mdxQuery = "";
if (notificationID != 0)
{
//MDX query by notificationID
log.Debug("Get Notifications by NotificationID");
mdxQuery = "SELECT TOPCOUNT(ORDER({[Notifications Dim].[Notification ID].[" + notificationID + "] *[Notifications Dim].[Notification Desc].[Notification Desc].members * [Notifications Dim].[Send Date].[Send Date].members *[Notifications Dim].[Language Type].[Language Type].members},[Notifications Dim].[Notification ID], desc), 10) ON ROWS, {[Measures].[Recipients Dim Count]}ON COLUMNS FROM [Data Warehouse]";
}
else if (dataSourceID != 0)
{
//MDX query by datasourceID
log.Debug("Get Notifications by DataSourceID");
mdxQuery = "SELECT NONEMPTY(TOPCOUNT(ORDER({[Notifications Dim].[Notification ID].[Notification ID].members *[Notifications Dim].[Notification Desc].[Notification Desc].members * [Notifications Dim].[Send Date].[Send Date].members *[Notifications Dim].[Language Type].[Language Type].members},[Notifications Dim].[Notification ID], desc), 10)) ON ROWS, {[Measures].[Recipients Dim Count]}ON COLUMNS FROM [Data Warehouse] WHERE [Data Source Dim].[Data Source ID].&[" + dataSourceID + "]";
}
else if (recipientID != 0)
{
//MDX query by recipientID
log.Debug("Get Notifications by RecipientID");
mdxQuery = "SELECT NONEMPTY (TOPCOUNT(ORDER({[Notifications Dim].[Notification ID].[Notification ID].members* [Notifications Dim].[Notification Desc].[Notification Desc].members * [Notifications Dim].[Send Date].[Send Date].members *[Notifications Dim].[Language Type].[Language Type].members},[Notifications Dim].[Notification ID], desc), 10) )ON ROWS, {[Measures].[Recipients Dim Count]} ON COLUMNS FROM [Data Warehouse] WHERE [Recipients Dim].[Recipient ID].&[" + recipientID + "]";
}
else if (notificationDesc != "")
{
//MDX query by notification description
log.Debug("Get Notifications by NotificationDesc");
mdxQuery = "SELECT NON EMPTY { [Measures].[Recipients Dim Count] } ON COLUMNS, NON EMPTY FILTER ({[Notifications Dim].[Notification ID].[Notification ID].members * [Notifications Dim].[Notification Desc].[Notification Desc].members *[Notifications Dim].[Send Date].[Send Date].members *[Notifications Dim].[Language Type].[Language Type].members},instr([Notifications Dim].[Notification Desc].currentmember.member_caption,'" + notificationDesc + "')>0) ON ROWS FROM [Data Warehouse]";
}
else if (extractID != 0)
{
//MDX query by extractID
log.Debug("Get Notifications by extractID");
mdxQuery = "SELECT NONEMPTY(TOPCOUNT(ORDER({[Notifications Dim].[Notification ID].[Notification ID].members *[Notifications Dim].[Notification Desc].[Notification Desc].members * [Notifications Dim].[Send Date].[Send Date].members *[Notifications Dim].[Language Type].[Language Type].members},[Notifications Dim].[Notification ID], desc), 10)) ON ROWS, [Measures].[Recipients Dim Count]ON COLUMNS FROM [Data Warehouse]WHERE [Extracts Dim].[Extractid].&[" + extractID + "]";
}
else
{
//MDX query top 10
log.Debug("Get Notifications by top 10");
mdxQuery = "SELECT NONEMPTY(TOPCOUNT(ORDER({[Notifications Dim].[Notification ID].[Notification ID].members* [Notifications Dim].[Notification Desc].[Notification Desc].members * [Notifications Dim].[Send Date].[Send Date].members *[Notifications Dim].[Language Type].[Language Type].members},[Notifications Dim].[Notification ID], desc), 10)) ON ROWS, {[Measures].[Recipients Dim Count]}ON COLUMNS FROM [Data Warehouse]";
}
using (AdomdCommand command = new AdomdCommand(mdxQuery, con))
{
try
{
using (AdomdDataReader reader = command.ExecuteReader())
{
int recordNumber = 0;
while (reader.Read())
{
log.Info("Reading Record #: " + recordNumber);
for (int i = 0; i < reader.FieldCount; i++)
{
log.Debug("Reading Field #: " + i);
log.Debug("Returned Field: " + reader[i]);
//field in records coming back in the following order:
//0 - NotificationID
//1 - NotificationDescription
//2 - SendDateTime
//3 - RecipientCount
//4 - LanguageType
//5 - DataSourceName
//add the data set returned to the model
switch (i)
{
case 0:
model.NotificationID.Add((string)reader[i]);
break;
case 1:
model.NotificationDescription.Add((string)reader[i]);
break;
case 2:
model.SendDateTime.Add((string)reader[i]);
break;
case 3:
model.LanguageType.Add((string)reader[i]);
break;
case 4:
model.RecipientCount.Add((int)reader[i]);
break;
}
}
recordNumber++;
}
}
}
catch (AdomdErrorResponseException ex)
{
log.Error("ERROR when trying to read records");
log.Error(ex);
return null;
}
}
con.Close();
}
catch (Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException ex)
{
log.Error(ex);
return null;
}
}
return model;
}
public ViewWeekofyearGivenDistanceDurationCategories(string DistanceCategoryParm, string DurationCategoryParm)
{
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 )
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOSET(@TripCatTripCategory, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED) ) ON COLUMNS
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))
{
cmd.Parameters.Add(new AdomdParameter("StationPairDistanceMileCategories", DistanceCategoryParm));
cmd.Parameters.Add(new AdomdParameter("TripCatTripCategory", DurationCategoryParm));
AdomdDataAdapter adapter = new AdomdDataAdapter(cmd);
adapter.Fill(ds);
}
conn.Close();
}
DataTable dt = ds.Tables[0];
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)
{
DistanceCategory = dr[colnbr["[Station Pair Distance].[Mile Categories].[Mile Categories].[MEMBER_CAPTION]"]].ToString();
DurationCategory = dr[colnbr["[TripCat].[Trip Category].[Trip Category].[MEMBER_CAPTION]"]].ToString();
Bikes = Convert.ToSingle(dr[colnbr["[Measures].[Bikes]"]]);
}
}