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 likeOrderDate AverageSale01.01.2005 1857,1208.01.2005 1754,2510.01.2005 1539,86... ...... ...17.12.2005 1754,5724.12.2005 2076,1630.12.2005 1829,79ALL 1947,64Thanks 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 :WITHMEMBER MEARSURES.AverageSales AS/*function for: average of (MEASURES.ProductPrice * MEASURES.OrderQuantity) */SELECT {OrderDate, MEASURES.AverageSales}ON COLUMNSDESCENDANTS(TimeByDay.Calendar.[2005],TimeByDay.Calendar.TDay[/*all Dates of Saturdays in 2005*/] SELF)ON ROWSFROM SalesCubeThanks for help! :)) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-24 : 12:55:45
|
for average use AVG() functionis Tday field indicator for Saturdays?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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/ |
|
|
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?? |
|
|
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 itsome thing likeWITH MEMBER Measures.AverageSalesASAVG(FILTER(DESCENDANTS([Date].[Month].CURRENTMEMBER,[Date].[Day]),[Date].[Day].&[Saturday]),[Measures].[Sales])SELECT Measures.AverageSales ON 0,NONEMPTY([Date].[Month].CHILDREN) ON 1FROM [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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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?likeWHERE DATENAME(WEEKDAY, DATE) = 'Saturday' ANDYear(DATE) = 2008 |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
nici
Starting Member
9 Posts |
Posted - 2013-06-26 : 13:31:53
|
Or is DATEID helpful?With format 20081231........ |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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?! |
|
|
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/ |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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/ |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 MVPhttp://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/ |
|
|
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 MVPhttp://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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|