SQL Server: Understanding DAX for BI Semantic Model

Who is online?  126 guests and 0 members
home  »  blogs  »  Raunak Jhawar  »  SQL Server: Understanding DAX for BI Semantic Model

Training on Microsoft Products & Technologies

SQL Server: Understanding DAX for BI Semantic Model

change text size: A A A

posted  11/29/2011 9:17:20 AM  by Raunak Jhawar -  Views: [5581] 

DAX Query Part 1
Hello!
We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”.
This post comes as a supplementary addition to the earlier post which describes the COUNT Function.
In this post let us write out first DAX query. Fairly easy!
Please note that I am using the AdventureWorksDWDenali as my database.
EVALUATE() : Evaluate the context which is passed as an argument and return a table
evaluate
(
    FILTER(DimCustomer, DimCustomer[MaritalStatus]="S")
)
This DAX Query returns all customers who are not married. Here the current context to evaluate is the FILTER
SUMMARIZE(): As the same suggests, this function is used to summarize the data. Perform aggregation and any grouping as required. So the result set in this case a table is not “flat or detailed” but grouped data set which is determined by the group expression passed as the argument. 
DAX Query
Notes:
  1. The header names are fully qualified, so the header names of all columns, either from base table or the calculated columns should be named while building the tabular model.
  2. *In SSMS, you cannot create calculated columns and use them in subsequent queries.
  3. All calculated columns thus should be created while building the tabular project and later used for analysis.
  4. Apparently, CONCATENATE() function does not works in SSMS for DAX Queries. But it yields correct result when used in Excel or Visual Studio for BIDS. So one cannot perform a concatenation operation on columns in SSMS.
Suppose, you were to try
evaluate
(
  Addcolumns
    (
      DimCustomer
     ,"Full Name",([FirstName] & " " & [LastName])
    )
) 
This DAX query will create a new column in the DimCustomer Table
*But this column cannot be referenced in the subsequent queries within SSMS. 
Please Note: * means I am still evaluating this feature
That’s all. Happy Learning!
If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks
tags : BISM, DAX, DAX Query, Denali New Features, SQL Server Denali, BI Semantic Model Reporting
  Rate This Blog Entry:  register  or  login

Author

raunak.jhawar Raunak Jhawar (Member since: 7/12/2011 6:42:24 AM)
Specilaist for Intensive Data Computing.

View Raunak Jhawar 's profile

Comments (no comments yet)

Leave a comment


 
Type the characters:
 *
 
   

Training on Microsoft Products & Technologies

Training on Microsoft Products & Technologies

Email Subscriptions

   Get the Most Recent Blogs in your inbox
Blogs RSS Feed

Raunak Jhawar's latest blog posts

Blogs RSS Feed

Latest community blog posts

  • SQL Server Day in Gurgaon in May 05’ 2012
    4/30/2012 5:05:54 AM by Admin Admin  - Views: 280  - Comments: 0
    Dear SQL Geek, SQLServerGeeks.com cordially invites you to SQL Server Day, the monthly dose of SQL Server knowledge & learning. And after successful events in Bangalore, Kolkata & Mumbai, we a...
  • SQL Server 2012 Feature Pack
    4/23/2012 9:24:41 AM by Quick Tip  - Views: 241  - Comments: 0
    Hi SQL Geeks, The Microsoft® SQL Server® 2012 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft® SQL Server® 2012. You can download the ...
  • SQL Server QUOTENAME function
    4/23/2012 9:09:51 AM by Amit Bansal  - Views: 1160  - Comments: 4
    Hi Friends, Here is an interesting function: QUOTENAME() which returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier. (from Books Online) ...
  • SQL Server 2008 R2 Express Database size limit
    4/12/2012 8:20:10 AM by Ravi Kumar  - Views: 926  - Comments: 0
    Hi All, It is a great news to all those who use SQL Server Express editions. Microsoft has increased the database size limit of SQL Server 2008 R2 Express edition from 4GB to 10GB. Previous versions o...
  • SQL Server – What is Scalar Function?
    4/11/2012 7:04:48 AM by Piyush Bajaj  - Views: 3239  - Comments: 2
    Hello Friends, It’s been a while that I have posted any blog. So here it is- Well with the arrival of SQL Server 2000, Microsoft has introduced the concept of User Defined Function (UDF). So the...
  • SQL Server 2012 Released to Manufacturing
    4/9/2012 11:41:31 AM by SQLnews SQLnews  - Views: 325  - Comments: 0
    Exciting news! SQL Server 2012 has released to manufacturing. Customers and partners can download an evaluation of the product today and can expect general availability to begin on April 1. Microsoft ...