none
How do we design Single SSAS Date Dimension (which has Financial & Fiscal Year Details) for different countries?

    Question

  • Hi All,

    In My current Date Dimension (Which is designed for Australia) I can see I have details for Calendar Year, Australia Financial Year & Fiscal Year. So Here we have 3 sets of Columns 1st Set Contains Calendar Year Details (Calendar Date, Year, Month, Week, Day etc) and then 2nd set contains Financial Year details (FinancialYear, FinancialSemester, FinancialQuarter, FinancialMonth, FinancialWeek) and 3rd Fiscal Year details (FiscalYear, FiscalQuarter, FiscalPeriod, FiscalWeek).

    Now Same Date Dimension we have to use for USA and their Financial and Fiscal Year is different from Australia. I think I need to add other columns for USA Financial and Fiscal Year.

    I am curious to know how other people has handle this? Date Dimension with Fiscal and Financial details by region?


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Friday, May 18, 2018 7:21 AM

All replies

  • Hi S Kumar Dubey,

    Thanks for your question.

    >>>I am curious to know how other people has handle this? Date Dimension with Fiscal and Financial details by region?
    You just need to add the columns for USA and their Financial and Fiscal Year to the existing date table and update the date dimension in Analysis Services with addition attributes for their Financial and Fiscal Year.

    You can look at the AdventureWorksDW database and see the Calendar attributes as well as Fiscal attributes as an example


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 21, 2018 2:37 AM
    Moderator
  • Thanks for your reply. I agree with your answer but I keep this as last option. 

    What happens If I have a centralized database which contains data for 20 or more countries and I want single Date Dimension? 

    If we go with above option then we may end-up with 20-40 columns and also Each time a new country comes in we need to update the Date Dimension.

    Is this ideal way? There is nothing else we can do?


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Monday, May 21, 2018 11:30 PM
  • Hi S Kumar Dubey,

    Thanks for your response.

    >>>If we go with above option then we may end-up with 20-40 columns and also Each time a new country comes in we need to update the Date Dimension.
    Generally speaking, one company will only have one Financial Year and calendar year. If you have met this problem, then you might want to communicate with your clients to use one Financial Year and calendar year.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 22, 2018 5:33 AM
    Moderator
  • How about If one company located in one region and is managing Services and data of 20 companies located in 20 different countries? Consider that cube has the client name. One client is from USA, one is from Australia and so on..?

    Note: I am talking about real time scenario and Hope I am not the one going through it.


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Tuesday, May 22, 2018 11:48 PM
  • 1) different solution for different customers.

    2) As already mentioned above by you and Willson - multiple attribute sets.

    3) RLS on SQL level for ROLAP dimension (in case if SQL Server security handles what kind of rowset to output depending on user) won't work for MD model because ImpersonateCurrentUser mode is not supported.

    4) Security on dimension (SSAS side, not SQL as in {3}) theoretically/potentially can be redesigned to fit this purpose - so attribute will be the same (even might have different parent on which security is defined), but underlying data sets will be limited by security model to only current user. Implications - much heavier dimension (rowcount).
    Wednesday, May 23, 2018 12:57 AM