PowerPivot & MDS Architecture

Question

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

Answer 1

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.

2. 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.

4. 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

Answer 2

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

Answer 3

Im confused about the processors

Answer 4

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.

3. 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.

4. 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

Answer 5

PowerPivot > If User connects to Storage Views?

One Stage?

Thank You So much