static void ExecuteDaxQuery()
{
// DAX query to be submitted totabuar database engine
String query = @"
EVALUATE
SUMMARIZECOLUMNS(
//GROUP BY
Customers[State],
//FILTER BY
TREATAS( {""Western Region""} , 'Customers'[Sales Region] ) ,
// MEASURES
""Sales Revenue"" , SUM(Sales[SalesAmount]) ,
""Units Sold"" , SUM(Sales[Quantity])
)
";
AdomdConnection adomdConnection = new AdomdConnection(connectString);
adomdConnection.Open();
AdomdCommand adomdCommand = new AdomdCommand(query, adomdConnection);
AdomdDataReader reader = adomdCommand.ExecuteReader();
ConvertReaderToCsv(reader);
reader.Dispose();
adomdConnection.Close();
}
static void AddSalesRegionMeasures()
{
// DAX query to be submitted totabuar database engine
String query = "EVALUATE( VALUES(Customers[Sales Region]) )";
AdomdConnection adomdConnection = new AdomdConnection(connectString);
adomdConnection.Open();
AdomdCommand adomdCommand = new AdomdCommand(query, adomdConnection);
AdomdDataReader reader = adomdCommand.ExecuteReader();
// open connection use TOM to create new measures
TOM.Server server = new TOM.Server();
server.Connect(connectString);
TOM.Model model = server.Databases[0].Model;
TOM.Table salesTable = model.Tables["Sales"];
String measureDescription = "Auto Measures";
// delete any previously created "Auto" measures
foreach (TOM.Measure m in salesTable.Measures)
{
if (m.Description == measureDescription)
{
salesTable.Measures.Remove(m);
model.SaveChanges();
}
}
// Create the new measures
while (reader.Read())
{
String SalesRegion = reader.GetValue(0).ToString();
String measureName = $"{SalesRegion} Sales";
TOM.Measure measure = new TOM.Measure()
{
Name = measureName,
Description = measureDescription,
DisplayFolder = "Auto Measures",
FormatString = "$#,##0",
Expression = $@"CALCULATE( SUM(Sales[SalesAmount]), Customers[Sales Region] = ""{SalesRegion}"" )"
};
salesTable.Measures.Add(measure);
}
model.SaveChanges();
reader.Dispose();
adomdConnection.Close();
}
public static void ConvertDaxQueryToCsv(string DaxQuery, string FileName)
{
string connectString = GetConnectStringForUser();
AdomdConnection adomdConnection = new AdomdConnection(connectString);
adomdConnection.Open();
AdomdCommand adomdCommand = new AdomdCommand(DaxQuery, adomdConnection);
AdomdDataReader reader = adomdCommand.ExecuteReader();
ConvertReaderToCsv(FileName, reader);
reader.Dispose();
adomdConnection.Close();
}
