Click here to Skip to main content
Click here to Skip to main content

Introduction to Named Query in Sql Server Analysis Services

, 29 Sep 2013
Rate this:
Please Sign up or sign in to vote.
Create Named Query in Data Source View While Designing OLAP Cube in SSAS

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mubin M. Shaikh
Team Leader
India India
Microsoft® Certified Professional (Microsoft Certification ID: 8918672).

Design and Develop Business Intelligence Solutions using Microsoft BI.
(SQL Integration Services - SSIS, SQL Analysis Services - SSAS, Reporting Services - SSRS,SQL-Server,Dimension Modelling,Data Warehouse,Power Pivot, Power View, Power Map, Power query,.Net,C#,WCF)

Linked In Profile:
 
Click Here to View Linked In Profile
 
Change Will Not Come If We Wait for Some Other Person,or Wait for Some Other Time, We are the One We are Waiting For,We are the Change That we Seek.
Follow on   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 29 Sep 2013
Article Copyright 2013 by Mubin M. Shaikh
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid