SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 MDX query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nici
Starting Member

9 Posts

Posted - 06/23/2013 :  16:29:33  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/24/2013 :  00:48:16  Show Profile  Reply with Quote
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 - 06/24/2013 :  07:13:21  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/24/2013 :  12:55:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/24/2013 :  15:59:33  Show Profile  Visit ditch's Homepage  Reply with Quote
/*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 - 06/25/2013 :  17:41:44  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/26/2013 :  03:12:26  Show Profile  Reply with Quote
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 - 06/26/2013 :  13:07:58  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/26/2013 :  13:13:25  Show Profile  Reply with Quote
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 - 06/26/2013 :  13:31:53  Show Profile  Reply with Quote
Or is DATEID helpful?

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

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/26/2013 :  13:34:23  Show Profile  Reply with Quote
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 - 06/26/2013 :  14:59:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/26/2013 :  15:49:15  Show Profile  Visit ditch's Homepage  Reply with Quote
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

India
52309 Posts

Posted - 06/27/2013 :  00:49:00  Show Profile  Reply with Quote
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

Edited by - visakh16 on 06/27/2013 00:51:54
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/27/2013 :  02:26:48  Show Profile  Visit ditch's Homepage  Reply with Quote
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

India
52309 Posts

Posted - 06/27/2013 :  03:04:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/27/2013 :  03:38:49  Show Profile  Visit ditch's Homepage  Reply with Quote
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

India
52309 Posts

Posted - 06/27/2013 :  05:08:03  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000