Management Studio has new DAX Query Window

We have been writing DAX queries using MDX Query Editor whenever we need to do something with Analysis Services Tabular Model databases (Read my old post on this: How to write SELECT for Analysis Services Tabular using DAX). Now, with this month Management Studio release (release number 17.0), we have a new Query Window for DAX.

You can download the latest at https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Once the new release is installed, you should see the icon and menu item for DAX Query Window;

Writing DAX Queries

Here is an example of DAX queries that can be written. As you see, first statement uses SUMMERIZE function to produce a result that shows sales by day of the week and product. Second query creates a measure using DEFINE MEASURE and uses it again with another query.

EVALUATE 
SUMMARIZE(Sales_201501, Sales_201501[CalendarDayOfWeekLabel]
 , Sales_201501[ProductName]
 , "Sales", SUM(Sales_201501[SalesAmount]))

GO

DEFINE MEASURE Sales_201501[NewSales] = SUMX(FILTER(Sales_201501, Sales_201501[IsWorkDay]="WeekEnd")
          , Sales_201501[SalesAmount])
EVALUATE 
SUMMARIZE(Sales_201501, Sales_201501[CalendarDayOfWeekLabel]
 , Sales_201501[ProductName]
 , "Sales", Sales_201501[NewSales])

Here is the result of it;

Analysis Services Tabular Model Workspace Database Retention: Why it is important?

When creating a Tabular Data Model project, it creates a workspace database in Analysis Services Tabular Instance for maintaining all the data and metadata while it is being developed. This database is an in-memory database and with default setting, it gets dropped from the instance when the project is closed. However this default setting can be changed by setting workspace database retention property, adjusting the behavior of the workspace database. That is why it is important.

Be default, workspace retention is set to Unload from memory. When creating a project, it creates a database in the disk (default location of SSAS instance) as well as a database in-memory using the SSAS Tabular instance. Name of the database is created as ProjectName_UserName_GUID. 

Here is the default setting. See the default setting.

Database created in the disk;

Database created in the SSAS Tabular instance;

With this setting, when the project is closed, in-memory database in the instance is removed. But the database in the disk remains and it is used for creating the in-memory database when the project is opening again. Below image shows the instance once the project is close. Note that in-memory database is dropped.

This setting frees up memory allowing system to use memory for other applications but this might slow down re-opening project as it has to build the database again.

If need, you can keep it in the memory without getting it dropped. This is useful if you continuously working with the project. However, be cautious, it does holds the memory consumed without releasing.

If you rarely open the project (usually, after completion of the development), it is better to set workspace retention to Delete workspace as it deletes the database completely. 

Local or remote workspace database?
If possible, developer should use a local instance for the workspace database because it provides the best performance. However remote server can also be used with following limitations;

• Project cannot be created by importing from PowerPivot template.
• No Backup to disk option with Data Backup property.
• Slower performance because of the latency.

Analysis Services Tabular Mode: Counting non-numeric columns using COUNT function

When creating calculated columns or measures in Analysis Services Tabular Mode, we use DAX expression language for adding them. DAX offers number of functions for implementing them and almost all general functions such as SUM, COUNT, AVERAGE are exist too. However some functions behave differently when compare these function behavior with other tools. Here is an example;

Assume that you need to get a count of two columns related to a loaded data table. Let's say we have loaded Internet Sales table from AdventureWorksDW2014, and need to create Count of Promotion Key and Count of Sales Order Number (of course, there is no business logic on these two counts, it is only for demo purposes), then we add two measures like below;

CountSalesOrderNumber:=COUNT([SalesOrderNumber])

CountPromotionKey:=COUNT([ProductKey])

Once added, you will see that CountPromotionKey works fine but CountSalesOrderNumber does not work. The reason for this is, Count function in DAX only works with numeric and dates. For counting non-numeric values, COUNTA has to use instead of COUNT.

CountSalesOrderNumber:=COUNTA([SalesOrderNumber])

Note that both functions do not count blank values. If you want to get the count including blank values, use COUNTROWS function. In addition to these, COUNTX and COUNTAX are given too. They are for getting the count based on a filter.

=COUNTAX(FILTER('Reseller',[Status]="Active"),[Phone])

For getting the count only on blank values, use COUNTBLANK.

SQL Server Brain Basher of the Week #036 - SQL Server

SQL Server 2014 introduced In-Memory OLTP tables and many of you know the benefits you get with it. This feature allows you to create table in memory either as durable or non-durable and it is only for tables. Do we create in-memory databases? Can we create in-memory databases? Here is this week question based on it?

Which SQL Server component create in-memory databases that are visible for our implementation?

• SQL Server Engine
• SQL Server Analysis Services
• SQL Server Integration Services
• SQL Server Reporting Services

This question can be answered quickly if you have worked on Business Intelligence solutions. Yes, it is related to Analysis Services but for a specific type only. Analysis Services allows us to create two types of models; Multidimensional and Tabular. When working with a Tabular data model project, as you have experienced, it needs a workspace database which is created in Analysis Services tabular instance. This database is in-memory database that keep all data and metadata for the project while it is being developed. When the project is closed, it removes from the memory by default, however it can be controlled by setting Workspace Retention property.