Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 MDX query

Author  Topic 

nici
Starting Member

9 Posts

Posted - 2013-06-23 : 16:29:33
Hi all,

does anyone know who to solve this with MDX?


I'm trying to get the average sale form each Saturday in year 2005 and in the end the average from all Saturdays in 2005.

This is how it's supposed to look like

OrderDate AverageSale
01.01.2005 1857,12
08.01.2005 1754,25
10.01.2005 1539,86
... ...
... ...
17.12.2005 1754,57
24.12.2005 2076,16
30.12.2005 1829,79
ALL 1947,64

Thanks for any advice!!
anna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 00:48:16
do you've only date field in your table? is there a calendar table with month year etc fields corresponding to date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

nici
Starting Member

9 Posts

Posted - 2013-06-24 : 07:13:21
Hi visakh 16,

I do have a date dimension with year, month, day.

This is a try with some lacking expressions in green in my logic :

WITH
MEMBER MEARSURES.AverageSales AS
/*function for: average of (MEASURES.ProductPrice * MEASURES.OrderQuantity) */
SELECT {OrderDate, MEASURES.AverageSales}
ON COLUMNS
DESCENDANTS(
TimeByDay.Calendar.[2005],
TimeByDay.Calendar.TDay[/*all Dates of Saturdays in 2005*/] SELF)
ON ROWS
FROM SalesCube

Thanks for help! :))
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-24 : 12:55:45
for average use AVG() function

is Tday field indicator for Saturdays?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-24 : 15:59:33
/*function for: average of (MEASURES.ProductPrice * MEASURES.OrderQuantity) */

This should not be done in the cube,It should have already been done in sql.
What this will do is multiply the aggregation of product price by the aggregation of order quantity, whereas you should be aggregating (productprice * orderquantity). So create a new column in your fact table containing a value representing (ProductPrice * orderquantity) and add this column to your measure group with an aggregation type of sum, that would be a start. After that we canlook at the mdx query.

Duane.
http://ditchiecubeblog.wordpress.com/
Go to Top of Page

nici
Starting Member

9 Posts

Posted - 2013-06-25 : 17:41:44
Thank you so far!!!
Now I have a new column of sales in Measures representing Price*Quantity.
Although I still don't know how to separate the Saturdays given that I only have Date, Year, Quarter, Month, Monthname, Day in my Time Dimension.
Maybe with Filter? Or do you know how to restrict dates similar to SQL??
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 03:12:26
if you've Day in your time dimension you can use a filter on it

some thing like

WITH MEMBER Measures.AverageSales
AS
AVG(FILTER(DESCENDANTS([Date].[Month].CURRENTMEMBER,[Date].[Day]),[Date].[Day].&[Saturday]),[Measures].[Sales])
SELECT Measures.AverageSales ON 0,
NONEMPTY([Date].[Month].CHILDREN) ON 1
FROM [CubeName]


Here Sales will be field containing Price*Quantity and I'm assuming Date dimension will have Month, day etc as fields in natural Hierarchy

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

nici
Starting Member

9 Posts

Posted - 2013-06-26 : 13:07:58
And what if I only have Date Dimension Members from 1 to 31?? And no Day Names.
Is MDX able to link a weekday to a date like SQL can?
If not, do I have to create a new table?

like
WHERE DATENAME(WEEKDAY, DATE) = 'Saturday' AND
Year(DATE) = 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 13:13:25
you best bet is to do it in sql and get day name also in dimension. Doing it in MDX would be a bit ugly.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

nici
Starting Member

9 Posts

Posted - 2013-06-26 : 13:31:53
Or is DATEID helpful?

With format 20081231........
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 13:34:23
nope...how will you get day still?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

nici
Starting Member

9 Posts

Posted - 2013-06-26 : 14:59:41
The problem is I cant modify the original tables in SQL.
Any idea how to create my needed weekday thing in SSaS as a 'new named calculation' or query... in order to create a new column with all weekdays pulled from Date or DateID?!
Go to Top of Page

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-26 : 15:49:15
nici, you are going to have to find a way to identify the days uniquely in SQL.
Can you create a view off the existing table and replace that table with the view in the dsv of the cube?


Duane.
http://ditchiecubeblog.wordpress.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 00:49:00
quote:
Originally posted by nici

The problem is I cant modify the original tables in SQL.
Any idea how to create my needed weekday thing in SSaS as a 'new named calculation' or query... in order to create a new column with all weekdays pulled from Date or DateID?!


you can use a named query in SSAS data source view (or add a view in sqlserver itself). My recommended way is latter one where i'll include in view a computed column based on date field to get day equivalent

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-27 : 02:26:48
Hi Nici, there is also another way which will yield better performance. In the view or the table for the fact table create an extra column for Saturday sales where you populate it with the normal sales value if its a Saturday and populate it with a zero if it's not. This way Saturday sales will be pre aggregated in the cube and determining Saturday sales in a MDX query will be less resource hungry.


So there are definitely a few options.


Duane.
http://ditchiecubeblog.wordpress.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 03:04:46
quote:
Originally posted by ditch

Hi Nici, there is also another way which will yield better performance. In the view or the table for the fact table create an extra column for Saturday sales where you populate it with the normal sales value if its a Saturday and populate it with a zero if it's not. This way Saturday sales will be pre aggregated in the cube and determining Saturday sales in a MDX query will be less resource hungry.


So there are definitely a few options.


Duane.
http://ditchiecubeblog.wordpress.com/


Not something i prefer as its not scalable enough. Tomorrow if they want Monday sales it will cause addition of another columns etc which means metadata changes too often. I would prefer to keep just day as an attribute in date dimension for all these and based on requirement just tweak the MDX to filter on correponding day attribute. Even if they want an interactive analysis based on day parameter they can utilize the field and write a MDX expression based STRTOSET() etc functions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-27 : 03:38:49
quote:
Originally posted by visakh16

quote:
Originally posted by ditch

Hi Nici, there is also another way which will yield better performance. In the view or the table for the fact table create an extra column for Saturday sales where you populate it with the normal sales value if its a Saturday and populate it with a zero if it's not. This way Saturday sales will be pre aggregated in the cube and determining Saturday sales in a MDX query will be less resource hungry.


So there are definitely a few options.


Duane.
http://ditchiecubeblog.wordpress.com/


Not something i prefer as its not scalable enough. Tomorrow if they want Monday sales it will cause addition of another columns etc which means metadata changes too often. I would prefer to keep just day as an attribute in date dimension for all these and based on requirement just tweak the MDX to filter on correponding day attribute. Even if they want an interactive analysis based on day parameter they can utilize the field and write a MDX expression based STRTOSET() etc functions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Yes agreed Visakh, but like I said, it can also be a decision based on performance. STRTOSET is an expensive on query resources and you not going to get better performance than a measure that is already pre aggregated.

Duane.
http://ditchiecubeblog.wordpress.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 05:08:03
quote:
Originally posted by ditch

quote:
Originally posted by visakh16

quote:
Originally posted by ditch

Hi Nici, there is also another way which will yield better performance. In the view or the table for the fact table create an extra column for Saturday sales where you populate it with the normal sales value if its a Saturday and populate it with a zero if it's not. This way Saturday sales will be pre aggregated in the cube and determining Saturday sales in a MDX query will be less resource hungry.


So there are definitely a few options.


Duane.
http://ditchiecubeblog.wordpress.com/


Not something i prefer as its not scalable enough. Tomorrow if they want Monday sales it will cause addition of another columns etc which means metadata changes too often. I would prefer to keep just day as an attribute in date dimension for all these and based on requirement just tweak the MDX to filter on correponding day attribute. Even if they want an interactive analysis based on day parameter they can utilize the field and write a MDX expression based STRTOSET() etc functions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Yes agreed Visakh, but like I said, it can also be a decision based on performance. STRTOSET is an expensive on query resources and you not going to get better performance than a measure that is already pre aggregated.

Duane.
http://ditchiecubeblog.wordpress.com/


yep...thats true
But i would prefer to keep changes at the data model level a bare minimum and provide flexibility at front end.
Especially if front end being a reporting tool definitely you might have to use query based on methods like STRTOSET etc to cater to interactive user inputs.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -