Named Query in SSAS DSV

Friends,

Here we are gonna discuss about Named Queries. This is one of the important property in SSAS DSV. A Named Query is nothing but a simple VALID SQL expression.  In a named query, you can specify an SQL expression to select rows and columns returned from one or more tables in one or more data sources. A named query is like any other table in a data source view with rows and relationships, except that the named query is based on an expression. The main advantage of this Named Query is that it gives you power to change the structure of tables and views that are present in data source but with out modifying the same in data source i.e the changes you are making to any object of the Database will be at DSV level only. The underlying objects in database will remain in the same condition.

Lets see a  simple example. Let’s say there are three tables table1,table2 and table3 then you can use a SQL statement which joins all the three tables and pulls only required columns and create a Named Query with it. The output of this one will become a brand new table with required columns. Lets see the steps involved in creating a named query.

1) Create a DS.

2) Create an empty DSV.

3) Right click on DSV and select New Named Query option as shown below.

4) Give some meaningful name and Add tables required or directly copy and past the query in the bottom last pane then click on OK.

That’s it. A new named query is created. Remember that You can use complex queries  as  well as simple queries but it should be a valid SQL expression. PFB the example for a simple Expression.

Select Getdate().

Yup, the above expression is valid to create a named query and it returns Server System current date. Hope you understood the concepts and Happy Coding Guys !!

Regards,

Roopesh Babu V