PowerPivot & MDS Architecture

Ismail Cassiem 226 Reputation points
2020-11-22T09:25:50.63+00:00

Hi,

I would to rollout PowerPivot to key users, im not too sure whats the best setup
I am setting up 2 VM (Storage DB Engine, Analytics Presentation)

  1. How do i go about the architecture
    A. PPivot > On Storage VM create Views, affect ETL etc?
    OR B. PPIvot > on OLAP Cubes only?
    OR C. PPivot > have DB engine setup on Presentation VM to store views for PPivot Only?

2. Does the Presentation VM also be 120/64 processors?

3. The Goal is to process cubes on Strage VM and robocopy the databases over to presentation once completed, will this not lock when cube active or better methods used?

4. Is MDS also on Storage VM, I want to share the WEB for users > best practise?

Server does not do paralism well, so im not sure where ppivot connects and processes data?

Thank You

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,242 questions
0 comments No comments
{count} votes

11 answers

Sort by: Most helpful
  1. Lukas Yu -MSFT 5,816 Reputation points
    2020-11-23T02:33:26.777+00:00

    Hi,

    Regarding to your questions, first we need to mention is that PowerPivot itself is not doing much heavy lifting working in the data process procedure. It is mainly a client tool that shows the data in Excel and enable to interact with it.

    So it is not very crucial where it is installed.

    1. Does the Presentation VM also be 120/64 processors?

    Not sure what 120/64 processors is, but the presentation VM should not require too much hardware resource.

    1. Is MDS also on Storage VM, I want to share the WEB for users > best practise?

    What is this MDS referring to ?

    Regards,
    Lukas


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    What can I do if my transaction log is full?--- Hot issues November
    How to convert Profiler trace into a SQL Server table -- Hot issues November

    0 comments No comments

  2. Ismail Cassiem 226 Reputation points
    2020-11-23T07:52:58.06+00:00

    Thank You,

    when user connects ppivot linking to Storage VM, based on the load would it affect server or user pc?
    as i dont want a user heavy load affecting production near realtime DW ETL refreshes

    MDS, Master Data Services i think is on storage. I want to make the web active gfor users

    0 comments No comments

  3. IC IC 236 Reputation points
    2020-11-23T07:55:10.743+00:00

    Im confused about the processors

    0 comments No comments

  4. Lukas Yu -MSFT 5,816 Reputation points
    2020-11-23T08:31:26.707+00:00

    Hi,

    when user connects ppivot linking to Storage VM, based on the load would it affect server or user pc?
    as i dont want a user heavy load affecting production near realtime DW ETL refreshes

    When PowerPivot is connecting to the SSAS Tabular cube, the query is passed to the SSAS server. The load will not affect user PC, user PC mainly waits for query result.

    1. The Goal is to process cubes on Stage VM and robocopy the databases over to presentation once completed, will this not lock when cube active or better methods used?

    The key here is that SSAS and SQL Server are suggested to set on different machine. The tabular cube query is passed to SSAS server, and SSAS server is processing them.
    It depends on your data size, if your company data is huge , we could set one VM for SQL Server->data storage, one stage SSAS VM server -> data processing, one sync SSAS server -> For client to query the data. As mentioned in this : Synchronize Analysis Services Databases But , if you company data is moderated size, like 32GB. This is not quite necessary.

    1. Is MDS also on Storage VM, I want to share the WEB for users > best practise?

    Master Data Service could be placed on SQL Server/Storage VM, it is relatively light weighted application.

    Hope this is helpful.
    Regards,
    Lukas

    0 comments No comments

  5. IC IC 236 Reputation points
    2020-11-23T09:05:03.487+00:00

    PowerPivot > If User connects to Storage Views?

    One Stage?

    Thank You So much

    0 comments No comments