Introduction to Named Query in Sql Server Analysis Services

Introduction

A Named Query is a SQL expression represented as a table, What this mean is you can define your Custom SQL expression to select columns from one or more tables, which belongs to single or multiple data source. We can compare Named Query with View in SQL Server.

While Designing Data Source View (DSV) in your Analysis Services Project using Business Intelligent Development Studio (BIDS), You can see option to create new Named Query in DSV or You can Convert Existing table to Named Query. Named Query allows us to extend our table schema without modifying underlying base tables. We can say Named query is similar to other tables in Data Source View with rows, columns and relationship.
Named query Can be used to divide large and complex dimension table to smaller and simple dimensions, it can also help us to unite columns from multiple tables to single table. You can also define calculated columns or Buckets on Time ,Age ,Salary as per customer requirement in Named query without modifying Original Table in Database.

Background

If you are new to OLAP Cube creation in SQL Server Analysis Services using BIDS. Please refer my Article to clear your basic idea, In this Article You can view Entire procedure of Creating OLAP Cube Step by Step in SQL Server Analysis Services.

Create First OLAP Cube in SQL Server Analysis Services

Scenario 1

You are asked to Combine Product Category and Sub Category Tables of Adventure works 2008 database and using these two table You need to created single dimension table in your Sales Data warehouse.

Creating New Table in DSV with Named Query

Letus First Create New Named Query In our Data Source View:

Step 1 :

In Solution Explorer, Double Click On SalesDW DSV.dsv

Step 2:

Right Click in Empty Area of DSV Design View --> Click On New Named Query

Step 3:

Assign Name --> Select Data Source

You can note on one thing here we have connection of Sales DW, and we are using Tables from AdeventureWorks2008R2 database, Now you can Enter your SQL Query as shown in below figure Part No.6 which is Join Query combines data from Two tables, Product Sub Category and Product Category.

Step 4:

Here Your Table is Ready Which is got created using Named Query, You can see difference in Icon of Normal Table and Table Created using Named Query.

Scenario 2

You are asked to set City Code for X mart Stores within city .

Store Code	Store Location
X-AHMSPR	S.P. RingRoad, Ahmedabad
X-AHMMNGR	Maninagar, Ahmedabad
X-AHMSVRNJ	Sivranjani, Ahmedabad

Converting Existing Table to Named Query

Step 1:

Right Click On DimStores -->Select Replace Table--> Click With Named Query

Step 2:

Modify Your T-SQL query of DimStore Dimension table.--> Click OK

SELECT StoreID, StoreAltID, StoreName, StoreLocation, City, State, Country,

(Case when StoreLocation='S.P. RingRoad' and City='Ahmedabad' then 'X-AHMSPR' 

when StoreLocation='Maninagar' and City='Ahmedabad' then 'X-AHMMNGR' 

when StoreLocation='Sivranjani' and City='Ahmedabad' then 'X-AHMSVRNJ' 

end )

as StoreCode

FROM dbo.DimStores

Step 3:

Here you can see the New Column Added in Your Table and it is converted to Named Query You can also see the change in icon of table.

If you feel This article is Good and Helped You to Learn something then Do Not forget to Vote For Me.

Enjoy Business Intelligence.

History

You Can also View Article Which Can help You To Learn Creation of Data Warehouse .

Learn to Create Data WareHouse Step by Step