This article addresses the importance and benefits of Query Designer Support in SQL Server 2017, while also addressing the proper way to use it.
Query Designer Support was recently launched in SQL Server 2017, which allows users to use DAX to generate the query which can be used in both tools for dropping or dragging the fields which are required for displaying your query, instead of writing it manually. Query Designer Support uses DAX to generate queries instead, eliminating the manual work induced by the user for writing the query. This helps in increasing proficiency and work speed while managing a database.
Note: This ability to generate queries through DAX query designer can only be used on tabular SSAS data sources built in or after 2016.
For some of you who may not be familiar with DAX, it basically stands for Data Analysis Expressions which is a much simpler form of query language than the commonly used MDX. As opposed to MDX which was typically used for multidimensional databases, DAX deals with tabular databases that have gained popular credence since the release of SQL Server 2008 iteration R2. It is also interesting to note that DAX has humble origins and initially started off as a functional language.
Steps to get started
First and foremost users need to download a 2016 or a more recent version of SQL Server. Once downloaded, users will be able to see latest tools and functionalities available on their SQL server while using it.
- Step 1: Open the Query Designer. Go to the Dropdown menu. This menu allows users to select whether they want to generate a query in MDX or DAX against the user’s data source.
- Step 2: If the user selects DAX, a DEX query will generate automatically behind the scenes, when he/she will drop or drag any field in the query designer from the Metadata tab.
- Step 3: Users can see the DAX Query Text by changing the design mode to Text from graphical.
- Step 4: Users can verify and analyze the query and use the information however they need.
Note: Users can switch between MDX and DAX query generation design, simply by changing its option on the drop-down menu. The user will also be warned by the SQL server, while switching that they have to regenerate the query, using whichever method they are choosing.
In Case You are Using a 2014 or 2012 SSAS Tabular Model
In case the user has an older data source, SSAS tabular model, they won’t be able to use the above mentioned features in their graphical designer. However, these users can use text editor for writing and editing DAX queries, against their 2014 or 2014 data sources. Users just need to select DAX in the drop-down menu and write their query accordingly.
Note: In both these scenarios, users will be able to save and use any datasets or reports that they build using these functionalities on the SQL Server 2016 and later released editions provided they support the Power BI reports.
Users can try this Query Designer Support system and experience a whole new level of hassle-free query generation using DAX or MDX.
While the 2017 edition of SQL Server breaks new grounds for a host of features, it still remains uniquely vulnerable to data corruption. Hence if you are running a SQL instance in your company, do consider keeping a repair SQL Server tool handy.
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Access fix and sql recovery software products. For more information visit https://www.datanumen.com/