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
