What are named queries and how does they function inside a SSAS Cube.

Problem: What are named queries and how does they function inside a SSAS Cube.

Solution: Define Named Queries in a Data Source View

In some edge cases we might need to build custom tables from the existing tables to bring collective information just like views, that's where named queries are used.

A named query is a SQL expression represented as a table. 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 (DSV) with rows and relationships, except that the named query is based on an expression.

A named query lets you extend the relational schema of existing tables in DSV without modifying the underlying data source. For example, a series of named queries can be used to split up a complex dimension table into smaller, simpler dimension tables for use in database dimensions. A named query can also be used to join multiple database tables from one or more data sources into a single data source view table.

Example:

In this example, I would like to show we can create a named query to join two tables.

Step 1: launch visual studio and open the data source view tab, for more information go through this article How to build a cube..

Step 2: Now go to the data source view and right click on the empty area to add a new named query.

Step 3: There will be a dialog box for adding query and name of the table.

Step 4: to browse the data from the named query hit the green play button and you would see the results in the bottom.

Step 5: After adding the named query the dsv should look like this and the icon will be little different if you observe.

Results: