Stairway to SSAS Tabular Level 1: Why use Analysis Services

  • Thomas LeBlanc

    SSCertifiable

    Points: 7974

    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

  • squareoff

    Mr or Mrs. 500

    Points: 572

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

  • PatrickIndex

    SSC Enthusiast

    Points: 129

    Unbelievably well written article.  Class.

  • EliG

    SSC Veteran

    Points: 264

    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)

  • rsmart 42717

    SSC Enthusiast

    Points: 113

    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.

  • Thomas LeBlanc

    SSCertifiable

    Points: 7974

    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

  • rsmart 42717

    SSC Enthusiast

    Points: 113

    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!

  • Thomas LeBlanc

    SSCertifiable

    Points: 7974

    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

  • sean redmond

    SSCertifiable

    Points: 5751

    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 8 months, 1 week ago by  sean redmond.
  • Thomas LeBlanc

    SSCertifiable

    Points: 7974

    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

Viewing 10 posts - 1 through 10 (of 10 total)

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