public static double GetDadNumber(Dictionary <string, object> kvp, DeviceType deviceType)
{
var algtelPassword = KeyVaultUtil.GetSecretInPlaintext(KeyVaultUtil.SharedAccountName);
string userId = KeyVaultUtil.SharedAccountName + "@microsoft.com";
string ssasServer = "asazure://centralus.asazure.windows.net/datapipelinesaas";
string ConnectionString = $"Password={algtelPassword};Persist Security Info=True;User ID={userId};Data Source = " + ssasServer + ";";
double dad = 0;
using (Microsoft.AnalysisServices.Tabular.Server server = new Microsoft.AnalysisServices.Tabular.Server())
{
server.Connect(ConnectionString);
string databaseName = deviceType == DeviceType.Hololens ? "HoloLensHackathon" : "OasisHackathon";
Microsoft.AnalysisServices.Tabular.Database tabularDatabase = null;
tabularDatabase = server.Databases.FindByName(databaseName);
string tableName = deviceType == DeviceType.Hololens ? "HoloLens Product Engagement" : "Oasis Product Engagement";
var oasisProductEngagamentTable = tabularDatabase.Model.Tables.Find(tableName);
string measureName = deviceType == DeviceType.Hololens ? "DAD (R28)" : "PC DAD (R28)";
var dadMeasure = oasisProductEngagamentTable.Measures.Where(e => e.Name == measureName).FirstOrDefault();
Console.WriteLine(dadMeasure.Expression);
dad = ExecuteQuery(dadMeasure.Expression, userId, algtelPassword, oasisProductEngagamentTable.Name, ssasServer, kvp, databaseName);
dad = Math.Round(dad, 0);
}
return(dad);
}
public static SortedDictionary <string, double> ExecuteGroupByDad(List <string> slicerList, Dictionary <string, object> filterList, DeviceType deviceType)
{
SortedDictionary <string, double> dadDictionary = new SortedDictionary <string, double>();
var algtelPassword = KeyVaultUtil.GetSecretInPlaintext(KeyVaultUtil.SharedAccountName);
string userId = KeyVaultUtil.SharedAccountName + "@microsoft.com";
string ssasServer = "asazure://centralus.asazure.windows.net/datapipelinesaas";
string ConnectionString = $"Password={algtelPassword};Persist Security Info=True;User ID={userId};Data Source = " + ssasServer + ";";
using (Microsoft.AnalysisServices.Tabular.Server server = new Microsoft.AnalysisServices.Tabular.Server())
{
server.Connect(ConnectionString);
string databaseName = deviceType == DeviceType.Hololens ? "HoloLensHackathon" : "OasisHackathon";
Microsoft.AnalysisServices.Tabular.Database tabularDatabase = null;
tabularDatabase = server.Databases.FindByName(databaseName);
string tableName = deviceType == DeviceType.Hololens ? "HoloLens Product Engagement" : "Oasis Product Engagement";
var oasisProductEngagamentTable = tabularDatabase.Model.Tables.Find(tableName);
string measureName = deviceType == DeviceType.Hololens ? "DAD (R28)" : "PC DAD (R28)";
var dadMeasure = oasisProductEngagamentTable.Measures.Where(e => e.Name == measureName).FirstOrDefault();
StringBuilder stbr = new StringBuilder();
foreach (var slicer in slicerList)
{
stbr.Append($"'{tableName}'[" + slicer + "]");
stbr.Append(" , ");
}
string str = stbr.ToString();
str = str.TrimEnd();
str = str.Remove(str.LastIndexOf(","));
string newTableName = (filterList.Count > 0) ? GetStringForGroupBy(tableName, filterList) : $"'{tableName}'";
string queryString = $"EVALUATE SUMMARIZE({newTableName}, {str}, \"Group by measure\"," + dadMeasure.Expression + ")";
string msolapConnectionString = $"Provider=MSOLAP;Data Source={ssasServer};Initial Catalog={databaseName};User ID = {userId};Password = {algtelPassword};Persist Security Info=True; Impersonation Level=Impersonate;";
using (var connection = new OleDbConnection(msolapConnectionString))
{
connection.Open();
using (var command = new OleDbCommand(queryString, connection))
{
using (var reader = command.ExecuteReader())
{
int columns = reader.FieldCount;
while (reader.Read())
{
string keyString = string.Empty;
for (int i = 0; i < columns - 1; i++)
{
keyString += reader[i].ToString() + ", ";
}
keyString = keyString.TrimEnd();
keyString = keyString.Remove(keyString.LastIndexOf(","));
double value = double.Parse(reader[columns - 1].ToString());
value = Math.Round(value, 0);
dadDictionary.Add(keyString, value);
}
}
}
}
}
return(dadDictionary);
}
public static void CreateNewDadMeasure(string measureName, string measureDescription, Dictionary <string, object> filterKeyValuePairs, DeviceType deviceType)
{
var algtelPassword = KeyVaultUtil.GetSecretInPlaintext(KeyVaultUtil.SharedAccountName);
string userId = KeyVaultUtil.SharedAccountName + "@microsoft.com";
string ssasServer = "asazure://centralus.asazure.windows.net/datapipelinesaas";
string ConnectionString = $"Password={algtelPassword};Persist Security Info=True;User ID={userId};Data Source = " + ssasServer + ";";
using (Microsoft.AnalysisServices.Tabular.Server server = new Microsoft.AnalysisServices.Tabular.Server())
{
server.Connect(ConnectionString);
string databaseName = deviceType == DeviceType.Hololens ? "HoloLensHackathon" : "OasisHackathon";
Microsoft.AnalysisServices.Tabular.Database tabularDatabase = null;
tabularDatabase = server.Databases.FindByName(databaseName);
string tableName = deviceType == DeviceType.Hololens ? "HoloLens Product Engagement" : "Oasis Product Engagement";
var oasisProductEngagementTable = tabularDatabase.Model.Tables.Find(tableName);
var newDadMeasure = oasisProductEngagementTable.Measures.Where(e => e.Name == measureName).FirstOrDefault();
if (newDadMeasure != null)
{
oasisProductEngagementTable.Measures.Remove(newDadMeasure);
}
StringBuilder stbr = new StringBuilder();
foreach (var k in filterKeyValuePairs)
{
if (k.Key == "Is Mainstream")
{
stbr.Append($"'{tableName}'[" + k.Key + "] = " + k.Value);
}
else
{
stbr.Append($"'{tableName}'[" + k.Key + "] = \"" + k.Value + "\"");
}
stbr.Append(" , ");
}
string str = stbr.ToString();
str = str.TrimEnd();
str = str.Remove(str.LastIndexOf(","));
string oldDadMeasureName = deviceType == DeviceType.Hololens ? "DAD (R28)" : "PC DAD (R28)";
var oldDadMeasure = oasisProductEngagementTable.Measures.Where(e => e.Name == oldDadMeasureName).FirstOrDefault();
string newDadMeasureExpression = string.Empty;
if (deviceType == DeviceType.Hololens)
{
newDadMeasureExpression = GetLatestDadExpression(oldDadMeasure.Expression, filterKeyValuePairs, tableName);
}
else
{
string oldDadMeasureExpression = oldDadMeasure.Expression;
oldDadMeasureExpression = oldDadMeasureExpression.Substring(oldDadMeasureExpression.IndexOf("AVERAGEX"));
oldDadMeasureExpression = oldDadMeasureExpression.Remove(oldDadMeasureExpression.LastIndexOf(")"));
newDadMeasureExpression = GetLatestDadExpression(oldDadMeasureExpression, filterKeyValuePairs, tableName);
}
string displayFolder = deviceType == DeviceType.Hololens ? @"[Measures]\Device Count" : @"[Measures]\PC Counting";
oasisProductEngagementTable.Measures.Add(CreateMeasure(measureName, newDadMeasureExpression, measureDescription, displayFolder));
tabularDatabase.Model.SaveChanges();
}
}