Post reply

Stairway to SSAS Tabular Level 1: Why use Analysis Services

  • July 11, 2018 at 12:00 am

    #104471

    Comments posted to this topic are about the item Stairway to SSAS Tabular Level 1: Why use Analysis Services

    Thomas LeBlanc, MVP Data Platform Consultant

  • July 11, 2018 at 4:47 am

    #1997106

    Great article. Thank you. Looking forward to the next ones.

  • July 11, 2018 at 7:23 am

    #1997122

    Unbelievably well written article.  Class.

  • July 11, 2018 at 7:33 am

    #1997125

    I really like the article, the compelling arguments as to why SSAS is needed instead of using the production database were written succinctly. That being said, you may want to correct the title for Figure 4 to read: Star Schema Sales data mart (instead of Start)

  • July 11, 2018 at 2:54 pm

    #1997220

    This is a good intro.  I hope you will be able to go deep into model design, such as what to do about loops.  I have such a hard time with the date dimension joined to both a fact and a dimension throwing me back.  Every existing example uses the same tired example of the three dates in the fact.

  • July 11, 2018 at 7:32 pm

    #1997246

    Thanks for the comments. 

    rsmart42717, I would need more information about the "loops" in data modeling in order to include in a future article.I understand the 3 dates in the fact table, just not the date dimension relationship to a Fact AND a Dimension. What is the other dimension?

    Thomas LeBlanc, MVP Data Platform Consultant

  • July 12, 2018 at 10:05 am

    #1997338

    Sure, here's an example.  My fact is a snapshot fact - Balances, status changes, days past due,  etc.  There are some dates, like LastPaymentDate, LastPartialPaymentDate, StatmentDate, and the SnapshotDate.  We limit the model to month-end snapshots up to the previous month, and daily for the current month.  The Dimension has the static info about the account - the AccountNumber, OpenDate, CloseDate, ChargeOffDate, rates and terms, etc.
    I have joins between Date dimension and the fact with the SnapshotDate being the active join, so you can see daily balance, stauts, delinquency etc.  I have measures with =USERELATIONSHIP() for some of the other dates. 
    The business has a need to know how many accounts opened, closed, charged off on a day, so I want to join the date dimension to those dates too, but I am prevented from doing so by the existing join.  "There are ambiguous paths between LoanFact and Date: LoanFact->Loan->Date [This is the one I'm trying to add] and LoanFact->Date."  [This is the existing join]
    In the existing reporting, we use SSRS so the reports are static and there is no issue.  But with PowerBI we want them to be able to make YOY or MOM comparisons by using the Date dimension at whatever granularity they want, and I have not found any best practices on how to resolve this. I am accessing my underlying star schema by views with some restrictions. 

    Thanks for thinking about it!

  • July 12, 2018 at 4:35 pm

    #1997415

    rsmart 42717 - Thursday, July 12, 2018 10:05 AM

    Sure, here's an example.  My fact is a snapshot fact - Balances, status changes, days past due,  etc.  There are some dates, like LastPaymentDate, LastPartialPaymentDate, StatmentDate, and the SnapshotDate.  We limit the model to month-end snapshots up to the previous month, and daily for the current month.  The Dimension has the static info about the account - the AccountNumber, OpenDate, CloseDate, ChargeOffDate, rates and terms, etc.
    I have joins between Date dimension and the fact with the SnapshotDate being the active join, so you can see daily balance, stauts, delinquency etc.  I have measures with =USERELATIONSHIP() for some of the other dates. 
    The business has a need to know how many accounts opened, closed, charged off on a day, so I want to join the date dimension to those dates too, but I am prevented from doing so by the existing join.  "There are ambiguous paths between LoanFact and Date: LoanFact->Loan->Date [This is the one I'm trying to add] and LoanFact->Date."  [This is the existing join]
    In the existing reporting, we use SSRS so the reports are static and there is no issue.  But with PowerBI we want them to be able to make YOY or MOM comparisons by using the Date dimension at whatever granularity they want, and I have not found any best practices on how to resolve this. I am accessing my underlying star schema by views with some restrictions. 

    Thanks for thinking about it!

    I will do an article on this subject showing 2 different options, but it is probably be around the 5th to 8th article.

    Thomas

    Thomas LeBlanc, MVP Data Platform Consultant

  • October 25, 2019 at 9:00 am

    #3691882

    Hi Thomas,

    I'm setting up a test-server for my first Tabular Model to demonstrate it to the analysts at our company.

    The source DB is small (2.8GB, 4 virtualised processors and 4GB RAM). SSAS Tabular Model uses xVertiPaq and In-Memory, which are both RAM- and processor-intensive. I don't expect more than 20 users and maybe 5 concurrent users.

    Do you have any recommendations for hardware specifications? It will be running in a virtualised environment. My guess that 4 virtualised processors and 8GB RAM should be enough. Am I forgetting any other factors? It will all be sitting in a SAN.

    Thanks,

    Sean.

    • This reply was modified 4 years, 5 months ago by  sean redmond.

  • October 25, 2019 at 4:26 pm

    #3691997

    Hey Sean,

    Thanks for reading the article.

    It really depends on the size of the data, but as much CPU and Memory as possible. Remember, this is an in-memory technology. Here is a 2012 Whitepaper from MS someone on Twitter gave me - https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/jj874401(v=msdn.10)?redirectedfrom=MSDN

    Thomas

     

     

    Thomas LeBlanc, MVP Data Platform Consultant

  • October 6, 2021 at 11:11 am

    #3938030

    Sean

    Thanks for writing the series! Now that we have a Date data type that is only 3 bytes is there any reason not to use that instead of an integer, 4 bytes, as a date foreign key field in a fact table?

  • October 7, 2021 at 11:31 am

    #3938412

    Gmwmn,

    You do not need to switch to an integer if the current structure for the date dimension is working.

    Thanks for comments!!!

    Thomas

    Thomas LeBlanc, MVP Data Platform Consultant

Viewing 12 posts - 1 through 11 (of 11 total)

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