DAX Query Generator for Intel in Ranet OLAP

by | Apr 19, 2016 | Ranet OLAP Blog

One of the most challenging tasks for Galaktikasoft in 2015 was developing a DAX query generator for Intel Corporation, the world's leader in semiconductor chip production. There are hardly any solutions like this on the market, so the project involved not only architecture design and programming, but also a great deal of research, all being mandatory to finalize on a very tight schedule.

Business objective

The client needed a tool that would enable creating reports by combining data from multiple sources, creating a single cube and using it for generating reports. Our job was to develop a solution that would provide such functionality.

General info

The project took us a month from beginning to end, which included 2 weeks of writing the specification and research, and another 2 weeks of programming. The team of two software engineers used such tools and technologies as Microsoft Analysis Services, DAX, .NET, Ranet UI.

Project overview

During the first stage of the project we were faced with a challenge of properly defining the technical specifications for the future DAX query generator. As there were no similar solutions on the market, our team had to conduct a thorough research in order to draw up the detailed specification.

Another challenge was working with DAX itself. Although this language can seem quite simple, writing queries using it is not that easy. DAX (Data Analysis eXpressions) was created by Microsoft in order to automate calculations in Power Pivot application for Excel, Analysis Services and Power BI tabular models. Most of DAX functions are similar to Excel ones, but, unlike Excel, where functions operate with separate cells and ranges, DAX uses tables and columns.

As a result, the team created a DAX query generator that enables user to describe the query using the terms, the query then being converted into DAX query in the DOM (Document Object Module).

Solution

Our team developed a solution that helps users generate DAX queries for data analysis and report creation without deep knowledge of DAX syntax. It is enough to describe the structure of the report layout, and DAX query is generated on the basis of this structure.

DAX query generator allows to:

  • use simple terms (filter, column) saving engineers from necessity to learn DAX syntax in details
  • automatically generate DAX queries for data analysis, thus saving time spent on application development
  • create reports using data from multiple sources

Here is a sample С# code to create query structure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

AttributeColumn year = new AttributeColumn("Date", "Calendar Year");
year.FilterValues.Add("2006");
 
AttributeColumn date = new AttributeColumn("Date", "Date", true);
date.FilterValues.Add("DATE(2006,1,1)");
date.FilterValues.Add("DATE(2006,1,2)");
 
AttributeColumn countryRegion = new AttributeColumn("Geography", "Country Region Name", true);
 
AttributeColumn product = new AttributeColumn("Product", "Product Category Name", true);
product.FilterValues.Add("\"Bikes\"");
product.FilterValues.Add("\"Clothing\"");
 
MeasureColumn totalSales = new MeasureColumn("Sales Territory", "Total Sales");
 
QueryStructure queryStructure = new QueryStructure();
queryStructure.Columns.Add(year);
queryStructure.Columns.Add(date);
queryStructure.Columns.Add(countryRegion);
queryStructure.Columns.Add(product);
queryStructure.Columns.Add(totalSales);
 
Operator exp1 = new Operator(new Column(year), "=", new DaxFragment("2007"));
Operator exp2 = new Operator(new Column(countryRegion), "=", new DaxFragment("\"United States\""));
Operator exp3 = new Operator(exp1, "&&", exp2);
Parentheses exp4 = new Parentheses(exp3);
Operator exp5 = new Operator(new Column(year), "=", new DaxFragment("2008"));
Operator exp6 = new Operator(new Column(countryRegion), "=", new DaxFragment("\"United Kingdom\""));
Operator exp7 = new Operator(exp5, "&&", exp6);
Parentheses exp8 = new Parentheses(exp7);
Operator exp9 = new Operator(exp4, "||", exp8);
Operator exp10 = new Operator(new Column(new MeasureColumn("Internet Sales", "Internet Total Sales")), ">", new DaxFragment("200000"));
Operator exp11 = new Operator(exp9, "||", exp10);
queryStructure.Filters = exp11;
 
string daxQuery = DaxGenerator.Generate(queryStructure);

Here is a sample DAX query generated with the code above:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

EVALUATE
SUMMARIZE(
    SUMMARIZE(
        FILTER(
            CROSSJOIN(
                FILTER(
                    FILTER(
                        SUMMARIZE(
                            'Date',
                            'Date'[Calendar YEAR],
                            'Date'[DATE]
                        ),
                        ('Date'[Calendar YEAR] = 2006)
                        &&
                        ('Date'[DATE] = DATE(2006,1,1) || 'Date'[DATE] = DATE(2006,1,2))
                    ),
                    (NOT ISBLANK('Sales Territory'[Total Sales]))
                ),
                FILTER(
                    VALUES(
                        'Geography'[Country Region Name]
                    ),
                    (NOT ISBLANK('Sales Territory'[Total Sales]))
                ),
                FILTER(
                    FILTER(
                        VALUES(
                            'Product'[Product Category Name]
                        ),
                        ('Product'[Product Category Name] = "Bikes" || 'Product'[Product Category Name] = "Clothing")
                    ),
                    (NOT ISBLANK('Sales Territory'[Total Sales]))
                )
            ),
            (NOT ISBLANK('Sales Territory'[Total Sales]))
            &&
            (
                (
                    'Date'[Calendar YEAR]
                    =
                    2007
                    &&
                    'Geography'[Country Region Name]
                    =
                    "United States"
                )
                ||
                (
                    'Date'[Calendar YEAR]
                    =
                    2008
                    &&
                    'Geography'[Country Region Name]
                    =
                    "United Kingdom"
                )
                ||
                'Internet Sales'[Internet Total Sales]
                >
                200000
            )
        ),
        ROLLUP(
            'Date'[DATE],
            'Geography'[Country Region Name],
            'Product'[Product Category Name]
        ),
        'Date'[Calendar YEAR],
        "Total Sales",
        'Sales Territory'[Total Sales]
    ),
    'Date'[Calendar YEAR],
    'Date'[DATE],
    'Geography'[Country Region Name],
    'Product'[Product Category Name],
    [Total Sales]
)
ORDER BY
    'Date'[Calendar YEAR],
    'Date'[DATE],
    'Geography'[Country Region Name],
    'Product'[Product Category Name]
Share the Link
Write US