public void BuildWithTest()
{
var builder = TestMdxBuilderFactory.CreateBuilder();
builder
.Cube("Dashboard")
.With(
new MdxMember("hasChild",
new MdxDistinctCount(new MdxHierarchy("[Ent Contacts].[Ent MainManagers - Name]").Children())))
.Columns(new TypedMdxElement(
"Amount",
"Price",
"hasChild"));
var mdxQuery = builder.Build();
var expectedQuery =
@"WITH
MEMBER [Measures].[hasChild] AS
DistinctCount ([Ent Contacts].[Ent MainManagers - Name].children)
SELECT
{
[Measures].[Amount],
[Measures].[Price],
[Measures].[hasChild]
}
ON COLUMNS
FROM Dashboard";
Assert.AreEqual(expectedQuery, mdxQuery, "Запрос по измерениям должен совпадать с образцом");
}
public void SubsetTest()
{
var builder = TestMdxBuilderFactory.CreateBuilder();
builder
.Cube("Dashboard")
.Columns(new TypedMdxElement("Weight"))
.Rows(new MdxSubset(new TypedMdxElement(new MdxHierarchy("Contacts")), 0, 100));
var mdxQuery = builder.Build();
var expectedQuery =
@"SELECT
{
[Measures].[Weight]
}
ON COLUMNS,
SubSet(
{
Contacts
}, 0, 100)
ON ROWS
FROM Dashboard";
Assert.AreEqual(expectedQuery, mdxQuery, "Запрос по измерениям должен совпадать с образцом");
}
public void BuildManyMeasureAndTwoRowsQueryTest()
{
var builder = TestMdxBuilderFactory.CreateBuilder();
builder
.Cube("Dashboard")
.Columns(
new TypedMdxElement("Amount",
"Price",
"Weight"))
.Rows(new MdxHierarchy("[Ent Contacts].[Ent MainManagers - Id]").Children(),
new MdxHierarchy("[Ent Contacts].[Ent MainManagers - Id]"));
var mdxQuery = builder.Build();
var expectedQuery =
@"SELECT
{
[Measures].[Amount],
[Measures].[Price],
[Measures].[Weight]
}
ON COLUMNS,
{
[Ent Contacts].[Ent MainManagers - Id].children,
[Ent Contacts].[Ent MainManagers - Id]
}
ON ROWS
FROM Dashboard";
Assert.AreEqual(expectedQuery, mdxQuery, "Запрос по измерениям должен совпадать с образцом");
}
public void BuildMovAvgWithTest()
{
var builder = TestMdxBuilderFactory.CreateBuilder();
builder
.Cube("Dashboard")
.With(
new MdxMember("AvgMov",
new MdxMovAvg(
new MdxHierarchy("[Dates].[Date]").CurrentMember(),
new MdxMeasureElement("Weight"),
29))
)
.Columns(new TypedMdxElement("AvgMov"));
var mdxQuery = builder.Build();
var expectedQuery =
@"WITH
MEMBER [Measures].[AvgMov] AS
Sum (
[Dates].[Date].CurrentMember.Lag(28) :
[Dates].[Date].CurrentMember.Lag(0),
[Measures].[Weight]
)/29
SELECT
{
[Measures].[AvgMov]
}
ON COLUMNS
FROM Dashboard";
Assert.AreEqual(expectedQuery, mdxQuery, "Запрос по измерениям должен совпадать с образцом");
}
public void BuildPrirostPercentWithTest()
{
var builder = TestMdxBuilderFactory.CreateBuilder();
builder
.Cube("Dashboard")
.With(
new MdxMember("SumPrirostPercent",
new MdxPrirostPercent(
new MdxHierarchy("[Dates].[Date]").CurrentMember(),
new MdxMeasureElement("SumPrice")))
)
.Columns(new TypedMdxElement("SumPrirostPercent"));
var mdxQuery = builder.Build();
var expectedQuery =
@"WITH
MEMBER [Measures].[SumPrirostPercent] AS
Divide ([Measures].[SumPrice] - Sum ([Dates].[Date].CurrentMember.PREVMEMBER, [Measures].[SumPrice]), [Measures].[SumPrice], 0)
SELECT
{
[Measures].[SumPrirostPercent]
}
ON COLUMNS
FROM Dashboard";
Assert.AreEqual(expectedQuery, mdxQuery, "Запрос по измерениям должен совпадать с образцом");
}
public void BuildSumWithTest()
{
var builder = TestMdxBuilderFactory.CreateBuilder();
builder
.Cube("Dashboard")
.With(
new MdxMember("SumReturns",
new MdxSum(
new MdxHierarchy("[DeliveryType].[Name].&[Возврат]"),
"SumPrice"))
)
.Columns(new TypedMdxElement("SumReturns"));
var mdxQuery = builder.Build();
var expectedQuery =
@"WITH
MEMBER [Measures].[SumReturns] AS
Sum ([DeliveryType].[Name].&[Возврат], [Measures].[SumPrice])
SELECT
{
[Measures].[SumReturns]
}
ON COLUMNS
FROM Dashboard";
Assert.AreEqual(expectedQuery, mdxQuery, "Запрос по измерениям должен совпадать с образцом");
}
public void BuildPeriodsToDateWithTest()
{
var builder = TestMdxBuilderFactory.CreateBuilder();
builder
.Cube("Dashboard")
.With(
new MdxMember("PeriodsToDate",
new MdxSum(
new MdxPeriodsToDate(
new MdxHierarchy("[Dates].[Календарная].[Cal Month]"),
new MdxHierarchy("[Dates].[Календарная]").CurrentMember()),
"Weight")))
.Columns(new TypedMdxElement("PeriodsToDate"));
var mdxQuery = builder.Build();
var expectedQuery =
@"WITH
MEMBER [Measures].[PeriodsToDate] AS
Sum (PeriodsToDate ([Dates].[Календарная].[Cal Month], [Dates].[Календарная].CurrentMember), [Measures].[Weight])
SELECT
{
[Measures].[PeriodsToDate]
}
ON COLUMNS
FROM Dashboard";
Assert.AreEqual(expectedQuery, mdxQuery, "Запрос по измерениям должен совпадать с образцом");
}
public void IntegrationTest()
{
var builder =
TestMdxBuilderFactory.CreateBuilder()
.Cube("Dashboard")
.Columns(
new TypedMdxElement("Amount",
"Price",
"Weight"))
.Rows(
new UnionMdxElement(
new MdxHierarchy("[Ent Contacts].[Ent FirstLevels - Id]")
.Value("175"),
new MdxHierarchy("[Ent Contacts].[Ent MainManagers - Id]")
.Children()
.NotEmpty()))
.Where(
new List <IMdxElement>
{
new MdxHierarchy("[Ent Contacts].[Подчинения].[Ent FirstLevels - Name]")
.Value("Мотовилов Андрей Александрович"),
new MdxHierarchy("[Ent Goods].[Ent Group NMK - Name]")
.Value("1. КИСЛОМОЛОЧНАЯ группа"),
new MdxHierarchy("[Ent Goods].[Ent BKG - Name]")
.Value("ММЛ ЗЕЛЕНЫЕ"),
new MdxHierarchy("[Dates].[Date]")
.Range("2014-08-23T00:00:00", "2014-08-31T00:00:00")
});
var mdxQuery = builder.Build();
var expectedQuery =
@"SELECT
{
[Measures].[Amount],
[Measures].[Price],
[Measures].[Weight]
}
ON COLUMNS,
(
[Ent Contacts].[Ent FirstLevels - Id].&[175],
NON EMPTY (
[Ent Contacts].[Ent MainManagers - Id].children )
)
ON ROWS
FROM Dashboard
WHERE
(
[Ent Contacts].[Подчинения].[Ent FirstLevels - Name].&[Мотовилов Андрей Александрович],
[Ent Goods].[Ent Group NMK - Name].&[1. КИСЛОМОЛОЧНАЯ группа],
[Ent Goods].[Ent BKG - Name].&[ММЛ ЗЕЛЕНЫЕ],
[Dates].[Date].&[2014-08-23T00:00:00]:[Dates].[Date].&[2014-08-31T00:00:00]
)";
Assert.AreEqual(expectedQuery, mdxQuery, "Запрос по измерениям должен совпадать с образцом");
}
public void MultyWhereAndRowsTest()
{
var builder =
TestMdxBuilderFactory.CreateBuilder()
.Cube("Dashboard")
.Columns(
new TypedMdxElement(
new MdxMeasureElement("Amount")))
.Rows(
new UnionMdxElement(
new MdxHierarchy("[Ent Goods].[Ent SubGroup NMK - Name]")
.Children(),
new MdxHierarchy("[Ent Goods].[Ent Group NMK - Name]")
.Children()))
.Where(
new List <IMdxElement>
{
new TypedMdxElement(
new MdxHierarchy("[Ent Goods].[Ent Group NMK - Name]")
.Value("1. КИСЛОМОЛОЧНАЯ группа"),
new MdxHierarchy("[Ent Goods].[Ent Group NMK - Name]")
.Value("2. МОЛОЧНАЯ группа")),
new MdxHierarchy("[Ent Goods].[Ent BKG - Name]")
.Value("ММЛ ЗЕЛЕНЫЕ"),
new MdxHierarchy("[Dates].[Date]")
.Range("2014-08-23T00:00:00", "2014-08-31T00:00:00"),
});
var mdxQuery = builder.Build();
var expectedQuery =
@"SELECT
{
[Measures].[Amount]
}
ON COLUMNS,
(
[Ent Goods].[Ent SubGroup NMK - Name].children,
{
[Ent Goods].[Ent Group NMK - Name].&[1. КИСЛОМОЛОЧНАЯ группа],
[Ent Goods].[Ent Group NMK - Name].&[2. МОЛОЧНАЯ группа]
}
)
ON ROWS
FROM Dashboard
WHERE
(
[Ent Goods].[Ent BKG - Name].&[ММЛ ЗЕЛЕНЫЕ],
[Dates].[Date].&[2014-08-23T00:00:00]:[Dates].[Date].&[2014-08-31T00:00:00]
)";
Assert.AreEqual(expectedQuery, mdxQuery, "Запрос по измерениям должен совпадать с образцом");
}
public void TestQuery()
{
var builder =
TestMdxBuilderFactory.CreateBuilder()
.Cube("Dashboard")
//.Columns()
.Rows(
new MdxNonEmptyJoined(
new TypedMdxElement(
new MdxHierarchy("[Ent Contacts].[Ent Regions - Name]").Children()
)));
Debug.WriteLine(builder.Build());
}
public void OrderTest()
{
var builder = TestMdxBuilderFactory.CreateBuilder();
builder
.Cube("Dashboard")
.Rows( //new MdxNonEmpty(
new MdxHierarchy("[Delivery].[Delivery Key]")
.Children()
//)
)
.OrderBy("[Delivery].[Date]", "asc")
;
var columns = new TypedMdxElement("Weight");
builder
.Columns(columns);
var mdxQuery = builder.Build();
Debug.Write(mdxQuery);
var expectedQuery =
@"SELECT
{
[Measures].[Weight]
}
ON COLUMNS,
NON EMPTY(
[Delivery].[Delivery Key].children
,
ORDER
(
([Delivery].[Date].children),
[Delivery].[Date].CurrentMember.Properties(""Key""),
asc
)
)
ON ROWS
FROM Dashboard";
Assert.AreEqual(expectedQuery, mdxQuery, "Запрос по измерениям должен совпадать с образцом");
}
public void BuildManyMeasureQueryTest()
{
var builder = TestMdxBuilderFactory.CreateBuilder();
builder
.Cube("Dashboard")
.Columns(new TypedMdxElement("Amount",
"Price",
"Weight"));
var mdxQuery = builder.Build();
var expectedQuery =
@"SELECT
{
[Measures].[Amount],
[Measures].[Price],
[Measures].[Weight]
}
ON COLUMNS
FROM Dashboard";
Assert.AreEqual(expectedQuery, mdxQuery, "Запрос по измерениям должен совпадать с образцом");
}
public void RemoveLastRow_Test()
{
#region expected
var expectedMdxQuery = new[]
{
@"SELECT
{
[Measures].[Amount]
}
ON COLUMNS,
(
{
[Ent Goods].[Ent Group NMK - Name].&[1. КИСЛОМОЛОЧНАЯ группа],
[Ent Goods].[Ent Group NMK - Name].&[2. МОЛОЧНАЯ группа]
},
[Ent Goods].[Ent SubGroup NMK - Name].children,
{
[Ent Goods].[Ent Brends - Name].&[Коровка],
[Ent Goods].[Ent Brends - Name].&[Бабулины продукты]
},
[Ent Goods].[Товары].children
)
ON ROWS
FROM Dashboard
WHERE
(
[Ent Goods].[Ent BKG - Name].&[ММЛ ЗЕЛЕНЫЕ],
[Dates].[Date].&[2014-08-23T00:00:00]:[Dates].[Date].&[2014-08-31T00:00:00]
)",
@"SELECT
{
[Measures].[Amount]
}
ON COLUMNS,
(
{
[Ent Goods].[Ent Group NMK - Name].&[1. КИСЛОМОЛОЧНАЯ группа],
[Ent Goods].[Ent Group NMK - Name].&[2. МОЛОЧНАЯ группа]
},
[Ent Goods].[Ent SubGroup NMK - Name].children,
{
[Ent Goods].[Ent Brends - Name].&[Коровка],
[Ent Goods].[Ent Brends - Name].&[Бабулины продукты]
}
)
ON ROWS
FROM Dashboard
WHERE
(
[Ent Goods].[Ent BKG - Name].&[ММЛ ЗЕЛЕНЫЕ],
[Dates].[Date].&[2014-08-23T00:00:00]:[Dates].[Date].&[2014-08-31T00:00:00]
)",
@"SELECT
{
[Measures].[Amount]
}
ON COLUMNS,
(
{
[Ent Goods].[Ent Group NMK - Name].&[1. КИСЛОМОЛОЧНАЯ группа],
[Ent Goods].[Ent Group NMK - Name].&[2. МОЛОЧНАЯ группа]
},
[Ent Goods].[Ent SubGroup NMK - Name].children
)
ON ROWS
FROM Dashboard
WHERE
(
[Ent Goods].[Ent BKG - Name].&[ММЛ ЗЕЛЕНЫЕ],
[Dates].[Date].&[2014-08-23T00:00:00]:[Dates].[Date].&[2014-08-31T00:00:00],
{
[Ent Goods].[Ent Brends - Name].&[Коровка],
[Ent Goods].[Ent Brends - Name].&[Бабулины продукты]
}
)",
@"SELECT
{
[Measures].[Amount]
}
ON COLUMNS,
(
{
[Ent Goods].[Ent Group NMK - Name].&[1. КИСЛОМОЛОЧНАЯ группа],
[Ent Goods].[Ent Group NMK - Name].&[2. МОЛОЧНАЯ группа]
}
)
ON ROWS
FROM Dashboard
WHERE
(
[Ent Goods].[Ent BKG - Name].&[ММЛ ЗЕЛЕНЫЕ],
[Dates].[Date].&[2014-08-23T00:00:00]:[Dates].[Date].&[2014-08-31T00:00:00],
{
[Ent Goods].[Ent Brends - Name].&[Коровка],
[Ent Goods].[Ent Brends - Name].&[Бабулины продукты]
}
)",
@"SELECT
{
[Measures].[Amount]
}
ON COLUMNS
FROM Dashboard
WHERE
(
[Ent Goods].[Ent BKG - Name].&[ММЛ ЗЕЛЕНЫЕ],
[Dates].[Date].&[2014-08-23T00:00:00]:[Dates].[Date].&[2014-08-31T00:00:00],
{
[Ent Goods].[Ent Brends - Name].&[Коровка],
[Ent Goods].[Ent Brends - Name].&[Бабулины продукты]
},
{
[Ent Goods].[Ent Group NMK - Name].&[1. КИСЛОМОЛОЧНАЯ группа],
[Ent Goods].[Ent Group NMK - Name].&[2. МОЛОЧНАЯ группа]
}
)"
};
#endregion
var builder =
TestMdxBuilderFactory.CreateBuilder()
.Cube("Dashboard")
.Columns(
new TypedMdxElement(
new MdxMeasureElement("Amount")))
.Rows(
new UnionMdxElement(
new MdxHierarchy("[Ent Goods].[Ent Group NMK - Name]")
.Children(),
new MdxHierarchy("[Ent Goods].[Ent SubGroup NMK - Name]")
.Children(),
new MdxHierarchy("[Ent Goods].[Ent Brends - Name]")
.Children(),
new MdxHierarchy("[Ent Goods].[Товары]")
.Children()
))
.Where(
new List <IMdxElement>
{
new TypedMdxElement(
new MdxHierarchy("[Ent Goods].[Ent Group NMK - Name]")
.Value("1. КИСЛОМОЛОЧНАЯ группа"),
new MdxHierarchy("[Ent Goods].[Ent Group NMK - Name]")
.Value("2. МОЛОЧНАЯ группа")),
new TypedMdxElement(
new MdxHierarchy("[Ent Goods].[Ent Brends - Name]")
.Value("Коровка"),
new MdxHierarchy("[Ent Goods].[Ent Brends - Name]")
.Value("Бабулины продукты")),
new MdxHierarchy("[Ent Goods].[Ent BKG - Name]")
.Value("ММЛ ЗЕЛЕНЫЕ"),
new MdxHierarchy("[Dates].[Date]")
.Range("2014-08-23T00:00:00", "2014-08-31T00:00:00")
});
var querys = MdxBuilderArrayCreator.ToMdxQueriesForExcel(builder).ToList();
Assert.AreEqual(expectedMdxQuery.Length, querys.Count, "Должен возвращаться набор из {0} запросов",
expectedMdxQuery.Length);
for (var i = 0; i < expectedMdxQuery.Length; i++)
{
Assert.AreEqual(expectedMdxQuery[i].Replace("\r", ""), querys[i].Replace("\r", ""), "Запрос {0} должен быть вида\n{1}",
i + 1, expectedMdxQuery[i]);
}
}
public void BuildReportByPeriodsQueryTest()
{
var builder = TestMdxBuilderFactory.CreateBuilder();
const string fieldMember = "field";
const string fieldAllMember = "fieldAll";
const string measure = "Weight";
const string periodType = "[Dates].[Date]";
const string begin = "2016-06-20T00:00:00";
const string end = "2016-06-24T00:00:00";
const string selectGroup = "[Ent Contacts].[Ent FirstLevels - Name]";
builder
.Cube("Dashboard")
.With(
new MdxMember(fieldMember, measure),
new MdxMember(fieldAllMember,
new MdxSum(
new MdxRangeElement(
new MdxHierarchy(periodType).Value(begin),
new MdxHierarchy(periodType).Value(end)),
fieldMember)))
.Columns(
new TypedMdxElement(
new UnionMdxElement(
new MdxHierarchy(periodType),
new MdxMeasureElement(fieldAllMember)),
new UnionMdxElement(
new MdxRangeElement(
new MdxHierarchy(periodType).Value(begin),
new MdxHierarchy(periodType).Value(end)),
new MdxMeasureElement(fieldMember))))
.Rows(
new MdxNonEmpty(
new TypedMdxElement(
new MdxHierarchy(selectGroup).Children(),
new MdxHierarchy(selectGroup)
)));
var mdxQuery = builder.Build();
var expectedQuery =
@"WITH
MEMBER [Measures].[field] AS
[Measures].[Weight]
MEMBER [Measures].[fieldAll] AS
Sum ([Dates].[Date].&[2016-06-20T00:00:00]:[Dates].[Date].&[2016-06-24T00:00:00], [Measures].[field])
SELECT
{
(
[Dates].[Date],
[Measures].[fieldAll]
),
(
[Dates].[Date].&[2016-06-20T00:00:00]:[Dates].[Date].&[2016-06-24T00:00:00],
[Measures].[field]
)
}
ON COLUMNS,
NON EMPTY (
{
[Ent Contacts].[Ent FirstLevels - Name].children,
[Ent Contacts].[Ent FirstLevels - Name]
})
ON ROWS
FROM Dashboard";
Assert.AreEqual(expectedQuery, mdxQuery, "Запрос по измерениям должен совпадать с образцом");
}
public void BuildManyWithTest()
{
var builder = TestMdxBuilderFactory.CreateBuilder();
builder
.Cube("Dashboard")
.With(
new MdxMember("PeriodsToDate",
new MdxSum(
new MdxPeriodsToDate(
new MdxHierarchy("[Dates].[Календарная].[Cal Month]"),
new MdxHierarchy("[Dates].[Календарная]").CurrentMember()),
"Weight")),
new MdxMember("hasChild",
new MdxDistinctCount(
new MdxHierarchy("[Ent Contacts].[Ent MainManagers - Name]").Children())),
new MdxMember("SumReturns",
new MdxSum(
new MdxHierarchy("[DeliveryType].[Name].&[Возврат]"),
"SumPrice")),
new MdxMember("SumPrirost",
new MdxPrirost(
new MdxHierarchy("[Dates].[Date]").CurrentMember(),
new MdxMeasureElement("SumPrice"))),
new MdxMember("SumPrirostPercent",
new MdxPrirostPercent(
new MdxHierarchy("[Dates].[Date]").CurrentMember(),
new MdxMeasureElement("SumPrice"))),
new MdxMember("AvgMov",
new MdxMovAvg(
new MdxHierarchy("[Dates].[Date]").CurrentMember(),
new MdxMeasureElement("Weight"),
29)))
.Columns(new TypedMdxElement("Amount",
"SumPrice",
"SumReturns",
"SumPrirost",
"SumPrirostPercent",
"AvgMov",
"PeriodsToDate",
"hasChild"));
var mdxQuery = builder.Build();
var expectedQuery =
@"WITH
MEMBER [Measures].[PeriodsToDate] AS
Sum (PeriodsToDate ([Dates].[Календарная].[Cal Month], [Dates].[Календарная].CurrentMember), [Measures].[Weight])
MEMBER [Measures].[hasChild] AS
DistinctCount ([Ent Contacts].[Ent MainManagers - Name].children)
MEMBER [Measures].[SumReturns] AS
Sum ([DeliveryType].[Name].&[Возврат], [Measures].[SumPrice])
MEMBER [Measures].[SumPrirost] AS
[Measures].[SumPrice] - Sum ([Dates].[Date].CurrentMember.PREVMEMBER, [Measures].[SumPrice])
MEMBER [Measures].[SumPrirostPercent] AS
Divide ([Measures].[SumPrice] - Sum ([Dates].[Date].CurrentMember.PREVMEMBER, [Measures].[SumPrice]), [Measures].[SumPrice], 0)
MEMBER [Measures].[AvgMov] AS
Sum (
[Dates].[Date].CurrentMember.Lag(28) :
[Dates].[Date].CurrentMember.Lag(0),
[Measures].[Weight]
)/29
SELECT
{
[Measures].[Amount],
[Measures].[SumPrice],
[Measures].[SumReturns],
[Measures].[SumPrirost],
[Measures].[SumPrirostPercent],
[Measures].[AvgMov],
[Measures].[PeriodsToDate],
[Measures].[hasChild]
}
ON COLUMNS
FROM Dashboard";
Assert.AreEqual(expectedQuery, mdxQuery, "Запрос по измерениям должен совпадать с образцом");
}
