Calculate the No. of Days for the Date period selected using MDX

  • vineet_dubey1975 (9/30/2014)


    Here did you mean this needed a row for each date in the new fact table ?

    Vineet D.

    Hi Vineet,

    Not quitely , since the Fact we have created mainly focuses on the Payments and rooms requiremtn came up nextly , so we have modified the script and added the columsn related to the rooms into the Fact and taking the distinct count of that and this works fine in daily day basis and doesnt work on Monthly and yearly basis

  • Guitar_player (10/21/2014)


    PB_BI (9/26/2014)


    So you just need a count of the occupied rooms over the course of months, quarters and years?

    I still don't think you need to distinctly count anything. Why not have a separate fact for occupied rooms? Something simple like:

    (FactRoomID INT IDENTITY,

    Date_FK INT,

    any other FKs)

    Then just use a measure group count in the cube and you're done.

    Sorry for the delay response..as i went on Long Holiday plan 🙂

    Yes.. i think thats the only solution. Well i am thinking of creating a new table for that , but how can i join that Rooms Fact table with the main Fact table ?

    If they share dimensionality then you don't need to join them directly, the shared dimensions are the "join"


    I'm on LinkedIn

  • Yes , thats correct , but what if i have got the required columns in the main fact too which i need to use them in my reporting with the rooms fact table columns ?

  • Guitar_player (10/21/2014)


    Yes , thats correct , but what if i have got the required columns in the main fact too which i need to use them in my reporting with the rooms fact table columns ?

    Then what I said before rings true. Consider the following result set:

    Rooms Occupied
    Room Count
    October 2014
    500
    1000
    November 2014
    450
    1200

    Forget about the accuracy of the data for a second. Imagine that Rooms Occupied and Room Count are from two separate fact tables. Imagine that both of these fact tables are related to a Date dimension, which is where those month values are coming from. Because they are both related to the Date dimension you can use the facts side by side, so there is no need to join them directly as they share dimensionality.


    I'm on LinkedIn

  • Then i hope this should work out !!!

    Thanks and Hats off PB_BI for your commitment !!!:-)

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply