Post reply

Help with MDX

  • November 25, 2009 at 3:51 pm

    #139597

    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

    PaidClicks 7 DAY

    Day 586495 W_AVG W_AVG Factored Roll_AVG

    1 116888 0.1993 0.2212 0.19930

    2 153371 0.2617 0.2904 0.23048

    3 124202 0.2141 0.2376 0.22500

    4 110020 0.1926 0.2138 0.21691

    5 97259 0.1735 0.1926 0.20823

    6 88880 0.1708 0.1896 0.20199

    7 88855 0.1775 0.1970 0.19848

    8 87868 0.1798 0.1996 0.19570

    9 77309 0.1625 0.1803 0.18153

    10 70225 0.1526 0.1694 0.17276

    11 66685 0.1485 0.1648 0.16645

    12 63292 0.1444 0.1603 0.16229

    13 60238 0.1410 0.1565 0.15803

    14 61852 0.1484 0.1647 0.15388

    15 63309 0.1548 0.1719 0.15031

    16 56620 0.1408 0.1563 0.14722

  • December 3, 2009 at 2:47 pm

    #1087389

    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