Creating Named calculation in the Cube SSAS

Question

Hi Friends,

I would like to create Named calculation for the fact table.

this is my Query based on this I would like to create named caculation in the fact table

SELECT [Item No_],SUM(Quantity) AS Inventory
FROM [dbo].[Fact_ItemLedgerEntry]
WHERE [Location Code] IN ('WHOLESALE', 'RETAIL', 'CORPORATE')
GROUP BY [Item No_]

how to do that could any one tell me.

any idea appreciated

Thanks,

Rama

Answer 1

Hi,

In case the type of object you want to create (Named calculation) is not mandatory for you, consider, with respect of your goal, defining you fact  table as a named query. 

this is more appropriate when the condition elements expands out of the boundary of one table.  Futher more, you can define named queries directly in SQL language.

Philip,

Answer 2

can you give an idea of your warehouse .dimension and fact and attribute name for location code ?

Answer 3

Hi Thanks for your information but created named query that brings down the performance.

Why can't we create named named calculation ?

coudl you please tell me

Thanks,

Rama

Answer 4

Hi Rama

Insted of creating Named calculation you can write below MDX in report OR create Calculated Member as below

Calculated member

MEMBER [Measures].[New Quantity] AS 
SUM({
[Location Code].[Location Code].[WHOLESALE]
,[Location Code].[Location Code].[RETAIL]
,[Location Code].[Location Code].[CORPORATE]
},[Measures].[Quantity])

MDX Query

WITH 
MEMBER [Measures].[New Quantity] AS 
SUM({
[Location Code].[Location Code].[WHOLESALE]
,[Location Code].[Location Code].[RETAIL]
,[Location Code].[Location Code].[CORPORATE]
},[Measures].[Quantity])
SELECT 
NON EMPTY
	{
	[Measures].[New Quantity]
	} ON 0,
NON EMPTY
	{
	[Item No].[Item No].[Item No].ALLMEMBERS
	} ON 1
FROM [Cube]

Suhas Kudekar

---

Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

My Blog

Follow @SuhasKudekar

Answer 5

Thats great point

below is my original so I could create calculate member for each of them then finally

I would create another calculate member then subtract from first one if this is right

I would proceed further.

SELECT 
		Inv.[Item No_]
		,Inv.Inventory
		,ISNULL(NonPickBins.RecSmrQty,0) AS RecSmrQty
		,ISNULL(Alloc.AllocQty,0) AS AllocQty
		,ISNULL(Tr.TransQty, 0) AS TransQty
		,Inv.Inventory 
				- ISNULL(NonPickBins.RecSmrQty,0) 
				- ISNULL(Alloc.AllocQty,0) 
				- ISNULL(Tr.TransQty, 0) 
				AS QtyAvToSell
FROM
-------------------------- Inventory ---------- (do I need a filter for warehouse = DCUK01)
(
SELECT [Item No_],SUM(Quantity) AS Inventory
FROM [dbo].[Fact_ItemLedgerEntry]
WHERE [Location Code] IN ('WHOLESALE', 'RETAIL', 'CORPORATE')
GROUP BY [Item No_]
) Inv
LEFT OUTER JOIN
---------------------------- Receive and SMR ---------- 
(
SELECT [Item No_], SUM([Quantity]) AS RecSmrQty
FROM [dbo].[Fact_WarehouseEntry]
WHERE [Location Code] = 'DCUK01' AND [Variant Code] = '' AND [Bin Type Code] <> 'PUTPICK'
GROUP BY [Item No_]
) NonPickBins
ON Inv.[Item No_] = NonPickBins.[Item No_]
LEFT OUTER JOIN
-------------------------Allocated Quantity-------------
(
SELECT [No_], SUM([Allocated Quantity]) AS AllocQty
FROM [Links of London].[dbo].[Links UK$Sales Line]
WHERE [Location Code] IN ('WHOLESALE', 'RETAIL', 'CORPORATE') AND [Document Type] = 1
GROUP BY [No_]
) Alloc
ON Inv.[Item No_] = Alloc.[No_]
LEFT OUTER JOIN
--------------------Outstanding Quantity---------------
(
SELECT [Item no_],SUM([Outstanding Quantity]) AS TransQty
FROM [dbo].[Fact_TransferLine]
WHERE [Transfer-from Code] IN ('WHOLESALE', 'RETAIL', 'CORPORATE') 
GROUP BY [Item no_]
) Tr
ON Inv.[Item No_] = Tr.[Item No_]

Thanks,

Rama

Answer 6

You will have to write 4 different MDX script for thew last 4 SELECT clauses

Where are you stuck?

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 7

Hi,

I guess yr exploration of the use of named calculation is motivated by flexibility of it use in MDX queries. This would indeed avoid you to have to write the same members in your queries all over again. 

DSV get its source tables from SQL queries anyway.  If performance is an issue, you should indeed weight  the performance cost of the SQL named queries versus normal tables at process time, against the graeter flexibility and ease in writing mdx queries.

Philip,

Answer 8

Thanks for your information.

I have created Calculated members for all of them except for this Query.

I dont understand how to apply this  [Bin Type Code] <>'PUTPICK' in the Calculated members

 

SELECT [Item No_], SUM([Quantity]) AS RecSmrQty
FROM [Links of London].[dbo].[Links UK$Warehouse Entry]
WHERE [Location Code] = 'DCUK01' AND [Variant Code] = '' AND [Bin Type Code] <> 'PUTPICK'
and [Bin Code]<>'R1'
GROUP BY [Item No_]

MEMBER [Measures].[SMR] AS 
SUM([Location].[Code].&[DCUK01]
,[Measures].[Quantity])

Could anyone tell me how to do restrict  that in the calculated member

 

Thanks,

Rama

Answer 9

Use EXCEPT() http://msdn.microsoft.com/en-us/library/ms144900.aspx

OR

a sub select statement like

SELECT(-{Name of set to exclude}) ON COLUMNS FROM

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog