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.
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
Stock of each product and each day. I want to only include the total balance of
Stock of the last day of the month. All other stock values are redundant.
For instance, browsing my
DimDate dimension table with the measurements
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