Issues excluding data in SSAS cube (different output in SSMS and SSAS using EOMONTH())

Question

I'm creating a cube whose stock values should only conclude the last day each month's balance and value.

Hence, I've created the following Query in SSMS:

 Create table #theStockTable(
 Stock int,
 StockValue INT,
 DateKey int
 )

 INSERT INTO #theStockTable
 VALUES(3,5, 20170211),
 (3,5,20170228),
 (1,4,20170331),
 (1,4,20170330)

SELECT        CAST(CONVERT(varchar, DateKey, 112) AS numeric(8, 0)) AS DateKey, SUM(Stock) AS [CL Stock], SUM(StockValue) AS [CL Stock Value]
FROM            #theStockTable
WHERE        CONVERT(date, CONVERT(varchar(10), DateKey)) = eomonth(CONVERT(date, CONVERT(varchar(10), DateKey)))
GROUP BY DateKey

In SSMS this returns the correct values:

DateKey CL Stock    CL Stock Value
20170228    3   5
20170331    1   4

However, when I create an OLAP cube using SSAS, and use the Query above as the Named Query for my fact table #theStockTable and the same Query as my only partition of the same fact table and deploy and execute the cube, I have a situation where I get different values on each day of every month, but I only want to have the values for each month's last day.

I have used New Project.. -> Import from Server (multidimensional model or data mining model) in SSAS. It is important that the users must be able to browse the cube as they presently do.

The cube whose meta data I have copied contains every day's values on the stock table. May there be some metadata change I need to make in addition to the Query modification I have done in Edit named Query.. in Data Source View and replacing the old Query in the partition with my new Query?

Hopefully someone can shed some light into this.

EDIT

To clarify my request, some users of the cube has explained that it is rather slow to browse in for instance Excel, mainly because my Stock measure is much bigger than it is required to be. As it is now, it returns every StockValue and Stock of each product and each day. I want to only include the total balance of StockValue and Stock of the last day of the month. All other stock values are redundant.

For instance, browsing my DimDate dimension table with the measurements Stock and StockValue should have this return set:

DateKey     Stock   StockValue
20170131    0       0

rather than the whole return set which is returned now:

DateKey     Stock   StockValue
20170101    3       5
20170102    4       6
20170103    1       1
20170131    0       0

Tom Huang Tom Huang · Accepted Answer · 2017-05-25T15:31:45

I think you already had a date dimension in your cube, if yes, then follow these steps:

Add an additional attribute [IsLastDay] with value 0/1 in the date dimension to indicate if the current date record is the last day of that month or not.

2.Add a calculate measure [CalStock] with this formular:

([Measures].[StockValue],[Date].[IsLastDay].&[1])

3.Fire this query to return the expected result:

select {[CalStock]} on 0,
non empty{[Date].[Date].[Date]} on 1
from [YourCube]

Issues excluding data in SSAS cube (different output in SSMS and SSAS using EOMONTH())

1 Answers