How to create a Named Query?

When we need to do more than add a column to a table we can use a Named Query instead of a Named Calculation.

The difference is that with a Named Query we have complete control over the SELECT statement that returns data.

It’s just like creating a view in a relational database.

When we use SQL Server as a source we have access to a Graphical query builder interface to help us design the query.

We can also test the query inside the Named Query Editor. Basically we use Named Queries to simplify a large table

by reducing the number of columns that we want to expose to the development environment or we can apply filters using a WHERE clause or joins to other tables that when we want to restrict the number of rows that we want to load into our Analysis Services Database.

It’s also a way to create a new table in the logical layer when we have read-only access to the data source.

Steps to create a Named Query.

Step1. I have a table called product in my SSAS Data Source view. Right click on the table and select replace Table à With New Named Query.

Named Query

Named Query

Step2. Now the Query builder dialog will open. Close all other windows except the Query definition window. Remove all unwanted fields from the script. Test the script and press OK button.

Named Query

Named Query

Named Query

Named Query

Step3:  Now you can see the Table icon changed and you can identify the difference between a normal table and a Named Query.

Named Query

Named Query

Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *