//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 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 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();
}
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 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);
}
//Obtener Nombre de Clientes
public DataTable GetClientsQueryMdx()
{
string dimension = "";
DataTable dataTable = new DataTable();
dimension = "[Dim Cliente].[Dim Cliente Nombre]";
List <ChartDataPie> LstChartDataPie = new List <ChartDataPie>();
var mdxQuery = $@"
SELECT NON EMPTY {{ [Measures].[Fact Ventas Netas] }}
ON COLUMNS, NON EMPTY {{ ( {dimension}.Children) }}
ON ROWS FROM [DWH Northwind]";
using (AdomdConnection cnn = new AdomdConnection($@"Provider=MSOLAP; Data Source=localhost;Catalog=Cubo811; User ID=sa; Password = daniel; Persist Security Info = True; Impersonation Level = Impersonate"))
{
AdomdDataAdapter adomdDataAdapter;
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(mdxQuery, cnn))
{
adomdDataAdapter = new AdomdDataAdapter(cmd);
adomdDataAdapter.Fill(dataTable);
dataTable.Columns[0].ColumnName = "ClientesNombre";
dataTable.Columns[1].ColumnName = "FactVentas";
}
}
return(dataTable);
}
private static string GetDataFromAzureAnalysisService(string query)
{
//Grab the token
//Get servername from Azure Analysis Service (Overview) resource
//Format: asazure://.asazure.windows.net/
var serverName = "asazure://westus.asazure.windows.net/agghactice";
var token = GetAccessToken("https://westus.asazure.windows.net");
var connectionString = $"Provider=MSOLAP;Data Source={serverName};Initial Catalog=databasename;User ID=;Password={token};Persist Security Info=True;Impersonation Level=Impersonate";
DataTable _dt = new DataTable();
string JSONString_result = string.Empty;
try
{
//read data from AAS
AdomdConnection connection = new AdomdConnection(connectionString);
connection.Open();
using (AdomdDataAdapter ad = new AdomdDataAdapter(query, connection))
{
ad.Fill(_dt);
if (_dt.Rows.Count > 0)
{
JSONString_result = JsonConvert.SerializeObject(_dt);
}
}
}
catch (Exception ex)
{
string ss = ex.Message.ToString();
Console.WriteLine(ex.Message.ToString());
}
return(JSONString_result);
}
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;
}
//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);
}
}
//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 void GetData(TripsByDistDurCatRow distdurparm)
{
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", distdurparm.DistanceCategory.MemberUniqueName));
cmd.Parameters.Add(new AdomdParameter("TripCatTripCategory", distdurparm.DurationCategory.MemberUniqueName));
cmd.Parameters.Add(new AdomdParameter("StationPairDistanceQtrMiles", distdurparm.DistanceQtrMi.MemberUniqueName));
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)
{
TripsByWeekdayRow oneresult = new TripsByWeekdayRow(distdurparm, dr, colnbr);
Add(oneresult);
}
}
public virtual DataSet Execute(out float elapsedSec)
{
// Open the connection
using (var connection = new AdomdConnection())
{
var connectionString = command.Connection.ConnectionString;
try
{ connection.ConnectionString = connectionString; }
catch (ArgumentException ex)
{ throw new ConnectionException(ex, connectionString); }
//TODO
//try
// {connection.Open();}
//catch (AdomdException ex)
// {throw new ConnectionException(ex);}
Trace.WriteLineIf(NBiTraceSwitch.TraceVerbose, command.CommandText);
foreach (AdomdParameter param in command.Parameters)
{
Trace.WriteLineIf(NBiTraceSwitch.TraceVerbose, string.Format("{0} => {1}", param.ParameterName, param.Value));
}
// capture time before execution
DateTime timeBefore = DateTime.Now;
command.Connection = connection;
var adapter = new AdomdDataAdapter(command);
var ds = new DataSet();
adapter.SelectCommand.CommandTimeout = 0;
try
{
adapter.Fill(ds);
}
catch (AdomdConnectionException ex)
{
throw new ConnectionException(ex, connectionString);
}
catch (AdomdErrorResponseException ex)
{
if (!ex.Message.StartsWith("Timeout expired."))
{
throw new ConnectionException(ex, connectionString);
}
else
{
throw new CommandTimeoutException(ex, adapter.SelectCommand);
}
}
// capture time after execution
DateTime timeAfter = DateTime.Now;
// setting query runtime
elapsedSec = (float)timeAfter.Subtract(timeBefore).TotalSeconds;
Trace.WriteLineIf(NBiTraceSwitch.TraceInfo, string.Format("Time needed to execute query [ADOMD]: {0}", timeAfter.Subtract(timeBefore).ToString(@"d\d\.hh\h\:mm\m\:ss\s\ \+fff\m\s")));
return(ds);
}
}
/// <summary>
/// Retrieves a dataset from an OLAP databse using the specified MDX query.
/// </summary>
/// <param name="conn">Opened connection to the database</param>
/// <param name="mdxQuery">MDX query</param>
/// <returns></returns>
public static DataSet GetData(AdomdConnection conn, string mdxQuery)
{
using (var ds = new DataSet())
using (AdomdCommand cmd = new AdomdCommand(mdxQuery, conn))
using (AdomdDataAdapter da = new AdomdDataAdapter(cmd))
{
da.Fill(ds);
return(ModifyDataType(ds, typeof(System.Decimal)));
}
}
public static DataTable ExecuteForDataTable(string connectionString, string commandText, CancellationToken cancellationToken = default(CancellationToken))
{
Func <IDbCommand, DataTable> function = (command) =>
{
var table = new DataTable();
using (var adapter = new AdomdDataAdapter(command as AdomdCommand))
adapter.Fill(table);
return(table);
};
return(Execute <DataTable>(connectionString, commandText, cancellationToken, function));
}
public DataTable GetDataTable(string query)
{
using (AdomdConnection con = new AdomdConnection(asConnectionString))
{
con.Open();
using (AdomdDataAdapter adapter = new AdomdDataAdapter(query, con))
{
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
return(dataTable);
}
}
}
public DataTable GetDataTable(string mdx)
{
using (var command = this.prepareCommand(mdx))
{
var table = new DataTable("CubeResults");
using (var daptor = new AdomdDataAdapter(command))
{
daptor.Fill(table);
}
return(table);
}
}
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 DataTable GetDataTable(string queryMdx)
{
DataTable dataTable = new DataTable();
using (AdomdConnection cnn = new AdomdConnection($@"Provider=MSOLAP; Data Source=localhost;Catalog=CuboNorthwindISSC811; User ID=sa; Password = roverto; Persist Security Info = True; Impersonation Level = Impersonate"))
{
AdomdDataAdapter adomdDataAdapter;
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(queryMdx, cnn))
{
adomdDataAdapter = new AdomdDataAdapter(cmd);
adomdDataAdapter.Fill(dataTable);
}
}
return(dataTable);
}
/// <summary>
/// Executes the query string and returns the result in a DataTable object.
/// </summary>
/// <param name="mdxQuery">The string mdx query.</param>
/// <returns></returns>
public DataTable Execute(string mdxQuery)
{
using (var connection = new AdomdConnection(this.ConnectionString))
{
using (var command = new AdomdCommand(mdxQuery, connection))
{
connection.Open();
using (var dapter = new AdomdDataAdapter(command))
{
var table = new DataTable(connection.Database);
dapter.Fill(table);
return(table);
}
}
}
}
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 DataTable ExecuteDaxQueryDataTable(string query)
{
_runningCommand = _adomdConn.CreateCommand();
_runningCommand.CommandType = CommandType.Text;
_runningCommand.CommandText = query;
var da = new AdomdDataAdapter(_runningCommand);
var dt = new DataTable("DAXResult");
if (_adomdConn.State != ConnectionState.Open)
{
_adomdConn.Open();
}
da.Fill(dt);
_runningCommand = null;
return(dt);
}
public static List <object> GetData(string command)
{
DataSet ds = new DataSet();
using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2"))
{
conn.Open();
using (AdomdCommand cmd = new AdomdCommand(command, conn))
{
AdomdDataAdapter adapter = new AdomdDataAdapter(cmd);
adapter.Fill(ds);
}
conn.Close();
}
return(ConvertDataTableToObjectList(ds.Tables[0]));
}
public static 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 DataTable ExecuteDaxQueryAsync(string query)
{
AdomdCommand cmd = _adomdConn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new AdomdDataAdapter(cmd);
var dt = new DataTable("DAXResult");
if (_adomdConn.State != ConnectionState.Open)
{
_adomdConn.Open();
}
da.Fill(dt);
return(dt);
}
public static DataTable ExecuteDataTable(AdomdConnection conn, string CommandText)
{
DataTable dt = null;
try
{
AdomdCommand command = new AdomdCommand();
SetCommand(conn, command, CommandText);
AdomdDataAdapter da = new AdomdDataAdapter(command);
dt = new DataTable();
da.Fill(dt);
}
catch (Exception ex)
{
throw;
}
return dt;
}
public static DataTable ExecuteDataTable(AdomdConnection conn, string CommandText)
{
DataTable dt = null;
try
{
AdomdCommand command = new AdomdCommand();
SetCommand(conn, command, CommandText);
AdomdDataAdapter da = new AdomdDataAdapter(command);
dt = new DataTable();
da.Fill(dt);
}
catch (Exception ex)
{
throw;
}
return(dt);
}
private static DataTable ExecuteMdx(string query, List <AdomdParameter> parameters = null)
{
DataTable dataTable = new DataTable();
using (AdomdConnection sqlCon = new AdomdConnection(mdxConnectionString))
{
sqlCon.Open();
string processedQuery = MdxQueryFormatter.GetQueryWithParameters(parameters, query);
AdomdCommand mdxCmd = new AdomdCommand(processedQuery, sqlCon);
using (AdomdDataAdapter dataAdapter = new AdomdDataAdapter(mdxCmd))
{
dataAdapter.Fill(dataTable);
}
}
return(dataTable);
}
/// <summary>
/// Return MDX query results as a DataSet
/// </summary>
/// <param name="mdx"></param>
/// <returns></returns>
internal DataSet GetData(string mdx)
{
var builder = new SqlConnectionStringBuilder();
builder.DataSource = Server;
builder.InitialCatalog = Catalog;
DataSet ds = null;
using (var connection = new AdomdConnection(builder.ToString()))
{
connection.Open();
ds = new DataSet();
using (var adaptor = new AdomdDataAdapter(mdx, connection))
{
adaptor.Fill(ds);
}
}
return(ds);
}
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]"]]);
}
}
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 void ExecuteCommandDataSet()
{
string sMDX = txtMDX.Text;
try
{
AdomdCommand oCmd = new AdomdCommand(sMDX, moConn);
AdomdDataAdapter da = new AdomdDataAdapter(oCmd);
//DataSet ds = new DataSet();
da.Fill(dataSet1, "olap");
grdResults.LoadData(dataSet1.Tables["olap"]);
//grdResults.DataSource = dataSet1.Tables["olap"];
}
catch (AdomdException ex)
{
MessageBox.Show(ex.Message);
}
}
// 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));
}
public DataTable GetChartDataPieQueryMdx(string[] clients, string[] months, string[] years)
{
string SelectedClients = "", SelectedMonths = "", SelectedYears = "";
if (clients.Length >= 1)
{
foreach (var item in clients)
{
SelectedClients += $@"[Dim Cliente].[Dim Cliente Nombre].&[{item}],";
}
SelectedClients = SelectedClients.Remove(SelectedClients.Length - 1);
}
if (months.Length >= 1)
{
foreach (var item in months)
{
SelectedMonths += $@"[Dim Tiempo].[Dim Tiempo Mes].&[{item}],";
}
SelectedMonths = SelectedMonths.Remove(SelectedMonths.Length - 1);
}
if (years.Length >= 1)
{
foreach (var item in years)
{
SelectedYears += $@"[Dim Tiempo].[Dim Tiempo Año].&[{item}],";
}
SelectedYears = SelectedYears.Remove(SelectedYears.Length - 1);
}
DataTable dataTable = new DataTable();
List <ChartDataPie> LstChartDataPie = new List <ChartDataPie>();
var mdxQuery = $@"
SELECT NON EMPTY {{
(
[Dim Tiempo].[Dim Tiempo Año].[Dim Tiempo Año].ALLMEMBERS *
[Dim Tiempo].[Dim Tiempo Mes].[Dim Tiempo Mes].ALLMEMBERS
)
}}
ON COLUMNS, NON EMPTY {{ (
(
[Measures].[Fact Ventas Netas],
[Dim Cliente].[Dim Cliente Nombre].[Dim Cliente Nombre].ALLMEMBERS
)
)
}} ON ROWS FROM ( SELECT ( {{
{SelectedYears}
}}) ON COLUMNS FROM ( SELECT ( {{
{SelectedMonths}
}} ) ON COLUMNS FROM ( SELECT ( {{
{SelectedClients}
}} ) ON COLUMNS
FROM [DWH Northwind])))
";
using (AdomdConnection cnn = new AdomdConnection($@"Provider=MSOLAP; Data Source=localhost;Catalog=Cubo811; User ID=sa; Password = roverto; Persist Security Info = True; Impersonation Level = Impersonate"))
{
AdomdDataAdapter adomdDataAdapter;
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(mdxQuery, cnn))
{
adomdDataAdapter = new AdomdDataAdapter(cmd);
adomdDataAdapter.Fill(dataTable);
}
dataTable.Columns.RemoveAt(0);
dataTable.Columns[0].ColumnName = "ClientesNombre";
dataTable.Columns.Add("Total", typeof(double));
double r = 0;
double sum = 0;
for (int renglones = 0; renglones < dataTable.Rows.Count; renglones++)
{
for (int columnas = 0; columnas < dataTable.Columns.Count; columnas++)
{
double.TryParse(dataTable.Rows[renglones][columnas].ToString(), out r);
sum = sum + r;
}
dataTable.Rows[renglones][dataTable.Columns.Count - 1] = sum;
sum = 0;
}
List <int> numeros = new List <int>();
for (int i = 1; i < dataTable.Columns.Count - 1; i++)
{
numeros.Add(i);
}
foreach (var item in numeros)
{
if (dataTable.Columns.Count == 2)
{
break;
}
dataTable.Columns.RemoveAt(dataTable.Columns.Count - 2);
}
}
return(dataTable);
}
/// <summary>
/// Execute an MDX command via a AdomdDataAdapter
/// </summary>
private void ExecuteCommandDataSet()
{
string sMDX = GetCommand();
try
{
AdomdCommand oCmd = new AdomdCommand(sMDX, moConn);
AdomdDataAdapter da = new AdomdDataAdapter(oCmd);
DataTable dt = new DataTable();
da.Fill(dt);
grdResults.LoadData(dt);
grdResults.AutoSize();
}
catch (AdomdException ex)
{
MessageBox.Show(ex.Message);
}
catch (InvalidOperationException exo)
{
MessageBox.Show(exo.Message);
}
}
// 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);
}
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;
}
// 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);
}
}
// criar principal
// dat permissão no aas ao principal com o formato app:<aapID>@<tenantID>
static void Main(string[] args)
{
string tenantID = "";
string clientId = "";
string clientSecret = "";
string resourceID = "";
string aasModel = "";
string query = @"";
if (args.Length == 6)
{
tenantID = args[0];
clientId = args[1];
clientSecret = args[2];
resourceID = args[3];
aasModel = args[4];
query = args[5].Replace("\"", "");
}
string tk = "";
bool getToken = true;
if (File.Exists(@".\token"))
{
string[] lines = System.IO.File.ReadAllLines(@".\token");
if (tenantID == lines[0] && clientId == lines[1])
{
DateTime dttk = (new DateTime(long.Parse(lines[2]))) - (new TimeSpan(0, 0, 10));
if (dttk > DateTime.Now)
{
tk = lines[3];
getToken = false;
}
}
}
if (getToken)
{
AuthenticationContext authContext = new AuthenticationContext("https://login.windows.net/" + tenantID);
ClientCredential cc = new ClientCredential(clientId, clientSecret);
AuthenticationResult token = authContext.AcquireTokenAsync("https://" + resourceID, cc).Result;
tk = token.AccessToken;
string[] nf = new string[4];
nf[0] = tenantID;
nf[1] = clientId;
nf[2] = token.ExpiresOn.Ticks.ToString();
nf[3] = tk;
System.IO.File.WriteAllLines(@".\token", nf);
}
var connectionString = $"Provider=MSOLAP;Data Source=asazure://" + resourceID + "/" + aasModel + ";Password="******";Persist Security Info=True; Impersonation Level=Impersonate;";
var ssasConnection = new AdomdConnection(connectionString);
AdomdCommand ccc = new AdomdCommand(query, ssasConnection);
AdomdDataAdapter objDataAdapter = new AdomdDataAdapter(ccc);
System.Data.DataTable dt = new System.Data.DataTable();
DateTime dtN = DateTime.Now;
ssasConnection.Open();
DateTime dtNq = DateTime.Now;
objDataAdapter.Fill(dt);
TimeSpan ts = DateTime.Now - dtN;
string r = (DateTime.Now - dtN).Milliseconds.ToString() + " " + (DateTime.Now - dtNq).Milliseconds.ToString() + " " + dt.Rows.Count.ToString();
ssasConnection.Close();
Console.WriteLine(r);
}
public virtual DataSet Execute(out float elapsedSec)
{
// Open the connection
using (var connection = new AdomdConnection())
{
var connectionString = command.Connection.ConnectionString;
try
{ connection.ConnectionString = connectionString; }
catch (ArgumentException ex)
{ throw new ConnectionException(ex, connectionString); }
//TODO
//try
// {connection.Open();}
//catch (AdomdException ex)
// {throw new ConnectionException(ex);}
Trace.WriteLineIf(NBiTraceSwitch.TraceVerbose, command.CommandText);
foreach (AdomdParameter param in command.Parameters)
Trace.WriteLineIf(NBiTraceSwitch.TraceVerbose, string.Format("{0} => {1}", param.ParameterName, param.Value));
// capture time before execution
DateTime timeBefore = DateTime.Now;
command.Connection = connection;
var adapter = new AdomdDataAdapter(command);
var ds = new DataSet();
adapter.SelectCommand.CommandTimeout = 0;
try
{
adapter.Fill(ds);
}
catch (AdomdConnectionException ex)
{
throw new ConnectionException(ex, connectionString);
}
catch (AdomdErrorResponseException ex)
{
throw new ConnectionException(ex, connectionString);
}
// capture time after execution
DateTime timeAfter = DateTime.Now;
// setting query runtime
elapsedSec = (float) timeAfter.Subtract(timeBefore).TotalSeconds;
Trace.WriteLineIf(NBiTraceSwitch.TraceInfo, string.Format("Time needed to execute query: {0}", timeAfter.Subtract(timeBefore).ToString(@"d\d\.hh\h\:mm\m\:ss\s\ \+fff\m\s")));
return ds;
}
}
// whereOk = only one dimension in rows
public static DataTable GetDataHelper(string dim, string dim2, string rows, List <KeyFigure> keyFigures, Dictionary <Dimension, string[]> restrictions,
bool zeroSkip, bool zeroskipstock, bool whereOk, bool skipRows, bool nonEmpty,
string connectionString, int userID, SqlConnection SQLServer)
{
AdomdConnection con = new AdomdConnection();
con.ConnectionString = connectionString;
Helpers.ETLHelpers.Debug("Using ADOMD String : " + con.ConnectionString);
con.Open();
List <string> kfs = keyFigures.ConvertAll(x => x.dbName);
string columns = "{" + string.Join(", ", kfs.ToArray()) + "}";
string where = "";
string withMember = "";
List <string> whereClauses = new List <string>();
List <string> idsAdded = new List <string>();
if (whereOk)
{
Debug.Assert(dim2 == "");
Dictionary <string, List <string> > values = new Dictionary <string, List <string> >();
bool restrictionOnDimension = false;
foreach (KeyValuePair <Dimension, string[]> r in restrictions)
{
if (r.Key != null)
{
if (dim == r.Key.dbName && r.Value.Length > 0 && !skipRows)
{
if (!restrictionOnDimension)
{
restrictionOnDimension = true;
rows = "{";
}
}
List <string> whereClauseValues = new List <string>();
foreach (string v in r.Value)
{
if (v != null && v != I18n.GetString("latest"))
{
if (dim == r.Key.dbName && !skipRows && nonEmpty)
{
rows += string.Format("NonEmpty({0}.&[{1}]), ", r.Key.dbName, v);
}
else if (dim == r.Key.dbName)
{
rows += string.Format("{0}.&[{1}], ", r.Key.dbName, v);
}
else
{
whereClauseValues.Add(string.Format("{0}.&[{1}]", r.Key.dbName, v));
}
}
}
if (whereClauseValues.Count > 0)
{
whereClauses.Add("{" + String.Join(", ", whereClauseValues.ToArray()) + "}");
}
}
}
if (restrictionOnDimension)
{
rows = rows.Substring(0, rows.Length - 2) + "}";
}
}
else
{
// if there is no restriction on the rows, then we can just use WITH MEMBER...
// if there is restrictions on the rows, then we need to construct all the values we want manually
bool restrictionOnRows = false;
List <string> dimRestrictions = new List <string>();
List <string> dim2Restrictions = new List <string>();
foreach (KeyValuePair <Dimension, string[]> r in restrictions)
{
if (r.Key.dbName == dim || r.Key.dbName == dim2)
{
restrictionOnRows = true;
}
}
// with can be used to restrict on several dimensions, but only ones that are not in rows
foreach (KeyValuePair <Dimension, string[]> r in restrictions)
{
if (restrictionOnRows)
{
if (r.Key.dbName == dim)
{
foreach (string v in r.Value)
{
if (v != I18n.GetString("latest"))
{
dimRestrictions.Add(v);
}
}
}
else if (r.Key.dbName == dim2)
{
foreach (string v in r.Value)
{
if (v != I18n.GetString("latest"))
{
dim2Restrictions.Add(v);
}
}
}
else
{
if (r.Value.Length > 0)
{
if (withMember == "")
{
withMember += "WITH ";
}
withMember += "MEMBER " + r.Key.dbName.Substring(0, r.Key.dbName.LastIndexOf("]", r.Key.dbName.LastIndexOf("]") - 1) + 1) + "." + r.Key.id + " AS Aggregate ({";
bool inforeach = false; //MGA 24092010; week missing in r.
foreach (string v in r.Value)
{
if (v != I18n.GetString("latest"))
{
inforeach = true;
withMember += r.Key.dbName + ".&[" + v + "], ";
}
}
// Helpers.debug("infoeach1 is : " + inforeach);
if (inforeach)
{
withMember = withMember.Substring(0, withMember.Length - 2) + "}) ";
}
else
{
withMember = withMember.Substring(0, withMember.Length - 2) + "}) ";
}
whereClauses.Add(r.Key.id);
}
}
}
else
{
if (r.Value.Length > 0)
{
if (withMember == "")
{
withMember += "WITH ";
}
withMember += "MEMBER " + r.Key.dbName.Substring(0, r.Key.dbName.LastIndexOf("]", r.Key.dbName.LastIndexOf("]") - 1) + 1) + "." + r.Key.id + " AS Aggregate ({";
bool inforeach = false; //MGA 24092010; week missing in r.
foreach (string v in r.Value)
{
//What happens is the only one is latest?
if (v != I18n.GetString("latest"))
{
inforeach = true;
withMember += r.Key.dbName + ".&[" + v + "], ";
}
}
// Helpers.debug("infoeach2 is : " + inforeach);
if (inforeach)
{
withMember = withMember.Substring(0, withMember.Length - 2) + "}) ";
}
else
{
withMember = withMember + "}) ";
}
whereClauses.Add(r.Key.id);
}
}
Helpers.ETLHelpers.Debug("Rows1: " + rows);
if (dimRestrictions.Count > 0 && dim2Restrictions.Count > 0)
{
rows = "{";
foreach (string dimRestriction in dimRestrictions)
{
foreach (string dim2Restriction in dim2Restrictions)
{
if (nonEmpty)
{
rows += string.Format("(NonEmpty({0}.&[{1}]), NonEmpty({2}.&[{3}])), ", dim, dimRestriction,
dim2, dim2Restriction);
}
else
{
rows += string.Format("({0}.&[{1}], {2}.&[{3}]), ", dim, dimRestriction,
dim2, dim2Restriction);
}
}
}
rows = rows.Substring(0, rows.Length - 2) + "}";
Helpers.ETLHelpers.Debug("Rows2: " + rows);
}
else if (dimRestrictions.Count > 0)
{
rows = "{";
foreach (string dimRestriction in dimRestrictions)
{
if (nonEmpty)
{
rows += string.Format("(NonEmpty({0}.&[{1}])), ", dim, dimRestriction);
}
else
{
rows += string.Format("({0}.&[{1}]), ", dim, dimRestriction);
}
}
rows = rows.Substring(0, rows.Length - 2) + "}";
Helpers.ETLHelpers.Debug("Rows3: " + rows);
}
else if (dim2Restrictions.Count > 0)
{
rows = "{";
foreach (string dimRestriction in dim2Restrictions)
{
if (nonEmpty)
{
rows += string.Format("(NonEmpty({0}), NonEmpty({1}.&[{2}])), ", dim, dim2, dimRestriction);
}
else
{
rows += string.Format("({0}, {1}.&[{2}]), ", dim, dim2, dimRestriction);
}
}
rows = rows.Substring(0, rows.Length - 2) + "}";
Helpers.ETLHelpers.Debug("Rows4: " + rows);
}
}
}
if (whereClauses.Count > 0)
{
where = " WHERE (" + string.Join(", ", whereClauses.ToArray()) + ")";
}
if (dim.ToLower().StartsWith("[product]") && String.IsNullOrEmpty(dim2))
{
//problem if we also want to see eg. sold sold qty and this is > 0 but ultimolager = 0 then this does not appear.
//This does not work when filtrering on multiple dimensions (set on axis). Uncommented gives larges values.
//rows = String.Format("Non Empty(FILTER({0}, [Measures].[UltimoLager_Stk] <> 0 or [Measures].[Afsætning] <> 0))", dim);
}
string query;
if (skipRows)
{
query = withMember + "SELECT " + columns + " on columns FROM [" + ConnectionHandler.adomdCubeName(userID) + "]" + where;
}
else
{
query = withMember + "SELECT " + columns + " on columns, " + rows + " ON ROWS FROM [" + ConnectionHandler.adomdCubeName(userID) + "]" + where;
}
Helpers.ETLHelpers.Debug("ADOMD query for execution: " + query);
AdomdDataAdapter adapter = new AdomdDataAdapter(query, con);
DataTable table = new DataTable();
Type type = null;
if (Helpers.ETLHelpers.IsTimeDimension(dim))
{
type = typeof(System.DateTime);
}
else
{
type = typeof(string);
}
table.Columns.Add(new DataColumn(dim + ".[MEMBER_CAPTION]", type));
foreach (string x in kfs)
{
table.Columns.Add(new DataColumn(x, typeof(Double)));
}
if (!String.IsNullOrEmpty(dim2))
{
if (Helpers.ETLHelpers.IsTimeDimension(dim2))
{
type = typeof(System.DateTime);
}
else
{
type = typeof(string);
}
table.Columns.Add(new DataColumn(dim2 + ".[MEMBER_CAPTION]", type));
}
adapter.Fill(table);
con.Close();
// FIXME: exception handling? connection pooling?
DateTime timeNow = DateTime.Now;
// currency
if (User.IsChainUser(userID) && User.UsesCurrency(userID))
{
var conversionRate = Helpers.ETLHelpers.ConversionRate(User.Currency(userID), SQLServer);
foreach (DataRow row in table.Rows)
{
int index = 1;
foreach (KeyFigure k in keyFigures)
{
if (k.type == KeyFigureType.Money)
{
try
{
double val = System.Convert.ToDouble(row[index]);
row[index] = val * conversionRate;
}
catch (InvalidCastException)
{
row[index] = 0;
}
}
++index;
}
}
}
//Helpers.debug("count {0}", table.Rows.Count);
if (zeroSkip)
{
List <int> row_indexes = new List <int>();
int row_index = 0;
var dims = 1;
var dimsAtEnd = 0;
if (!String.IsNullOrEmpty(dim2))
{
dimsAtEnd = 1;
}
Helpers.ETLHelpers.Debug("ADOMD restrictions count:" + dims);
Helpers.ETLHelpers.Debug("Columns count:" + table.Columns.Count);
Helpers.ETLHelpers.Debug("Itemarray[0] count:" + table.Rows[0].ItemArray.Length);
Type stringType = "".GetType();
foreach (DataRow row in table.Rows)
{
bool skip = true;
for (int i = dims; i < row.ItemArray.Length - dimsAtEnd; ++i)
{
// this is much faster than catching exceptions
Helpers.ETLHelpers.Debug("Data type : " + table.Columns[i].DataType + ", value: " + row.ItemArray[i]);
if (row.ItemArray[i] == DBNull.Value || table.Columns[i].DataType == stringType)
{
continue;
}
Helpers.ETLHelpers.Debug("item array value: " + (Double)row.ItemArray[i]);
try
{
if ((Double)row.ItemArray[i] != 0)
{
skip = false;
Helpers.ETLHelpers.Debug(" Zeroskip, skipped = false: " + row.ItemArray[i]);
break;
}
}
catch (System.InvalidCastException)
{
Helpers.ETLHelpers.Debug("Invalid cast with zeroskip: " + row.ItemArray[i]);
skip = false; //MGA NEW 27052013
}
}
if (skip)
{
row_indexes.Add(row_index);
}
++row_index;
}
row_indexes.Reverse(); // otherwise the indexes don't match ;-)
Helpers.ETLHelpers.Debug("tablerows count before : " + table.Rows.Count);
foreach (int index in row_indexes)
{
Helpers.ETLHelpers.Debug("Zeroskip, should remove index : " + index);
table.Rows.RemoveAt(index);
}
Helpers.ETLHelpers.Debug("tablerows count after : " + table.Rows.Count);
}
// Helpers.debug("count {0}", table.Rows.Count);
return(table);
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
/* Obtendo campos do modelo*/
if (comboBox1.SelectedIndex != 0)
{
SqlConnection conSql = new SqlConnection(ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString"].ConnectionString);
SqlCommand comSql = new SqlCommand("select * from modelocampo where idModelo = (select id from modelo where estrutura = '" + comboBox1.Text.Substring(0, comboBox1.Text.Length - 13) + "')", conSql);
conSql.Open();
SqlDataReader read = comSql.ExecuteReader();
if (read.HasRows)
{
int y = 0;
string camp = "";
foreach (var item in read)
{
camp += read["campo"].ToString() + ";";
y++;
}
campos = camp.Split(';');
read.Close();
read.Dispose();
comSql.CommandText = "select algoritmo from modelo where estrutura = '" + comboBox1.Text.Substring(0, comboBox1.Text.Length - 13) + "'";
read = comSql.ExecuteReader();
read.Read();
if (read["algoritmo"].ToString() == "MTS (Microsoft Time Serial)")
{
int x = 0;
AdomdConnection conn = new AdomdConnection(ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString_Analysis"].ConnectionString);
AdomdCommand comand = new AdomdCommand();
conn.Open();
comand.Connection = conn;
comand.CommandText = "SELECT StructureColumn('" + campos[0].Trim() + "'),StructureColumn('" + campos[1].Trim() + "') " +
"FROM " + comboBox1.Text.Substring(0, comboBox1.Text.Length - 13) + ".CASES";
AdomdDataAdapter ad = new AdomdDataAdapter(comand);
table.Clear();
ad.Fill(table);
DataTableReader reader = table.CreateDataReader();
// read.Read();
string valores = "";
foreach (var item in reader)
{
valores += reader[1].ToString() + ",";
}
valores = valores.Substring(0, valores.Length - 1);
try
{
//chartHist.Clear();
chartHist.Charts[0].SeriesCollection.Delete(0);
chartHist.Charts.Delete(0);
chartHist.Refresh();
}
catch
{
}
chartHist.ResetText();
chartHist.Charts.Add(0);
chartHist.Charts[0].SeriesCollection.Add(0);
chartHist.Charts[0].Type = OWC11.ChartChartTypeEnum.chChartTypeLine;
chartHist.Charts[0].SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimSeriesNames, Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral), "Quantidade");
chartHist.Charts[0].SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimValues, Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral), valores);
chartHist.Refresh();
}
}
carregaGrid();
}
}
public List <string> GetChartLabelsDataBarQueryMdx(string[] clients, string[] months, string[] years) //
{
List <dynamic> dlist = new List <dynamic>();
List <string> lstColumNames = new List <string>();
string SelectedClients = "", SelectedMonths = "", SelectedYears = "";
if (clients.Length >= 1)
{
foreach (var item in clients)
{
SelectedClients += $@"[Dim Cliente].[Dim Cliente Nombre].&[{item}],";
}
SelectedClients = SelectedClients.Remove(SelectedClients.Length - 1);
}
if (months.Length >= 1)
{
foreach (var item in months)
{
SelectedMonths += $@"[Dim Tiempo].[Dim Tiempo Mes].&[{item}],";
}
SelectedMonths = SelectedMonths.Remove(SelectedMonths.Length - 1);
}
if (years.Length >= 1)
{
foreach (var item in years)
{
SelectedYears += $@"[Dim Tiempo].[Dim Tiempo Año].&[{item}],";
}
SelectedYears = SelectedYears.Remove(SelectedYears.Length - 1);
}
DataTable dataTable = new DataTable();
List <ChartDataPie> LstChartDataPie = new List <ChartDataPie>();
var mdxQuery = $@"
SELECT NON EMPTY {{
(
[Dim Tiempo].[Dim Tiempo Año].[Dim Tiempo Año].ALLMEMBERS *
[Dim Tiempo].[Dim Tiempo Mes].[Dim Tiempo Mes].ALLMEMBERS
)
}}
ON COLUMNS, NON EMPTY {{ (
(
[Measures].[Fact Ventas Netas],
[Dim Cliente].[Dim Cliente Nombre].[Dim Cliente Nombre].ALLMEMBERS
)
)
}} ON ROWS FROM ( SELECT ( {{
{SelectedYears}
}}) ON COLUMNS FROM ( SELECT ( {{
{SelectedMonths}
}} ) ON COLUMNS FROM ( SELECT ( {{
{SelectedClients}
}} ) ON COLUMNS
FROM [DWH Northwind])))
";
using (AdomdConnection cnn = new AdomdConnection($@"Provider=MSOLAP; Data Source=localhost;Catalog=Cubo811; User ID=sa; Password = roverto; Persist Security Info = True; Impersonation Level = Impersonate"))
{
AdomdDataAdapter adomdDataAdapter;
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(mdxQuery, cnn))
{
adomdDataAdapter = new AdomdDataAdapter(cmd);
adomdDataAdapter.Fill(dataTable);
}
string tmpNombre = "Cliente";
dataTable.Columns.RemoveAt(0);
dataTable.Columns[0].ColumnName = "Cliente";
foreach (DataColumn item in dataTable.Columns)
{
tmpNombre = item.ColumnName;
tmpNombre = tmpNombre.Replace("[Dim Tiempo].[Dim Tiempo Año].&[", " ");
tmpNombre = tmpNombre.Replace("].[Dim Tiempo].[Dim Tiempo Mes].&[", " ");
tmpNombre = tmpNombre.Replace("[", " ");
tmpNombre = tmpNombre.Replace("]", " ");
dataTable.Columns[item.Ordinal].ColumnName = tmpNombre;
lstColumNames.Add(tmpNombre);
tmpNombre = "";
}
List <double> lstDouble = null;
for (int i = 0; i < dataTable.Rows.Count; i++)
{
lstDouble = new List <double>();
string ssss = "";
foreach (var item2 in lstColumNames)
{
double number1 = 0;
var dataToConvert = "";
if (dataTable.Rows[i][item2].ToString().Equals(""))
{
dataToConvert = "0";
}
else
{
dataToConvert = dataTable.Rows[i][item2].ToString();
}
bool canConvert = double.TryParse(dataToConvert, out number1);
if (canConvert == true)
{
lstDouble.Add(number1);
}
else
{
ssss = dataTable.Rows[i][item2].ToString();
}
}
dynamic objTabla = new
{
label = ssss,
data = lstDouble
};
dlist.Add(objTabla);
//lstDouble.Clear();
}
var dhyudsyuyuds = dlist;
}
dynamic Labels = new
{
labelColums = lstColumNames
};
lstColumNames.RemoveAt(0);
var asdasd = lstColumNames;
var gagag = dataTable;
//dlist.Add(Labels);
return(lstColumNames);
}
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]"]]);
}
}
