A great way to monitor and manage large SQL Server 2012 enterprise environments is with the Microsoft System Center Suite, particularly monitoring the servers with System Center Operations Manager (SCOM). One of the really nice benefits of using SCOM to collect SQL Server counters is that you can keep the history of alerts, events, and perf counters in the System Center Data Warehouse. (OperationsManagerDW Schema).
You will need to read through that document and understand the database schema to use BI tools like Power View and PowerPivot effectively with this data. The DW schema includes a series of views along with base tables. The views are intended to be used with operational reporting tools like Report Builder in SSRS. But to provide faster, more analytical access into this important server history for performance baseline & trending, you’ll probably want to build a semantic model off the base tables. By doing that, you can get at the raw counters and build an in-memory semantic model with PowerPivot to build reports that will be super-fast and more intuitive to end-users. The end consumer of this data will be SQL Server architects, DBAs and server admins.
I created a very simple model in PowerPivot from the SCOM DW schema that brings in events, perf counters and alerts that were collected over a month into my SCOM system.
The relationships between the tables, IDs and intermediary tables can be engineered from the view that are included in the DW database as well as the documentation online for SCOM including the general reporting guideline.
Now I can use slicers and analytical charts in Excel to observe my SQL Server health and performance over time.
I can also manage that semantic model in Visual Studio and deploy to my SSAS tabular server so that Report Builder can be utilized or the new Power View tool. Note that the SCOM that I was using shipped with an old version of Report Builder using Report Models. Since that functionality is deprecated in SSRS, creating semantic models and using Tabular SSAS is a very good replacement for that technique.
