November 25, 2009 at 3:51 pm
Hi,
I have a question, if you can push me in the right direction I would appreciate it. I have been doing sql for many years now and decided to try SSAS and create some Pilot reports at my job. The one report I am trying to duplicate is stumping me. Basically the user would input two dates. From there I would need to compare the dates and List the Numbers of days sequentially down the side. The report would look like the sample below. I was able figure out the rolling averages and other calculated measure. In fact things that take a lil work in t_sql seem easy in MDX. However, I cannot figure out 2 things.
1. How to get the day difference and list the days down the side.
2. Down Loads and Paid Clicks are in seperate fact tables(FactPaidClicks, and FactDownLoads). The FactPaidClick Has a DownLoad Date, and a Click Date. The FactDownLoads just has DownLoaDdate. All three dates are sharing the same Dimension. Is there a way in MDX to join These two on DownLoad date to Click date?
Downloads
PaidClicks7 DAY
Day586495W_AVGW_AVG FactoredRoll_AVG
1116888 0.1993 0.2212 0.19930
2153371 0.2617 0.2904 0.23048
3124202 0.2141 0.2376 0.22500
4110020 0.1926 0.2138 0.21691
597259 0.1735 0.1926 0.20823
688880 0.1708 0.1896 0.20199
788855 0.1775 0.1970 0.19848
887868 0.1798 0.1996 0.19570
977309 0.1625 0.1803 0.18153
1070225 0.1526 0.1694 0.17276
1166685 0.1485 0.1648 0.16645
1263292 0.1444 0.1603 0.16229
1360238 0.1410 0.1565 0.15803
1461852 0.1484 0.1647 0.15388
1563309 0.1548 0.1719 0.15031
1656620 0.1408 0.1563 0.14722
December 3, 2009 at 2:47 pm
From my experience so far in MDX is limited.
I think you need to bring those other FACT tables into your Data Source View for starters. Next, you can bring those different measures into your OLAP cube as New Measure Groups. Next, you can do you MDX calculations on the Calculations Tab of your OLAP cube. Make sure after each step you do a Process, b/c if you don't you won't see current information. What version of SQL Server are you using? Version 2008, you should be fine.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply