DAX Query Generator: Galaktikasoft First Project for Intel | Ranet OLAP

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:

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