One can convert a seven day performance model to a 26 week model without a lot of trouble. I chose 182 days, because it won't impact the Power View reports I created very much. I will change the Perf query to use daily aggregated data instead of hourly aggregated data. As of this writing, the line charts in Power View support 1,000 data points. A line for one week of hourly data equates to 168 data points. A line for 26 weeks of daily data equates to 182 data points. For the chart of statistics with five lines both time periods stay below 1,000 data points.

The seven day model is the one I built then modified in my previous two posts:


I find long term data provides insights into trends and patterns which may not plainly appear within a week. One might identify behavior tied to a fiscal calendar or seasonal events. Also, the visualizations in Power View may help build a case for capacity changes. Overall, the reports help with data-driven analysis.

I should note that configuration changes become more apparent over time, too. That's why I used to store snapshots of server configuration in an operational database. Sudden changes in performance provoke questions about what happened and why.



A. Modify SQL Queries in the Performance Data Model

B. Refresh the Data and Review Reports


Step by step

A. Modify SQL Queries in the Performance Data Model
  1. Open Perf 7d Model in Excel
  2. Save the workbook as Perf 182d Model
  3. Select Manage from the Data Model section of the POWERPIVOT menu 

  4. Select the Perf dataset tab
  5. Select Table Properties from the ribbon in the Design menu 

  6. Enter a new query (Perf.vPerfHourly à Perf.vPerfDaily; 7 à 182) 


    SELECT     PERF.ManagedEntityRowId







    FROM Perf.vPerfDaily PERF

    INNER JOIN vPerformanceRuleInstance PRI ON PRI.PerformanceRuleInstanceRowId = PERF.PerformanceRuleInstanceRowId

    INNER JOIN vPerformanceRule PR ON PR.RuleRowId = PRI.RuleRowId

    WHERE PERF.DateTime > (GETUTCDATE() - 182)

    ORDER BY PERF.DateTime


  7. Click Save
  8. Select the Entity dataset tab
  9. Select Table Properties from the ribbon in the Design menu
  10. Enter a new query (7 à 182)


    select distinct


    ,MET.ManagedEntityTypeDefaultName as METype

    ,TLMET.ManagedEntityTypeDefaultName as TLMEType







    from Perf.vPerfDaily PERF

    inner join vManagedEntity ME on ME.ManagedEntityRowId = PERF.ManagedEntityRowId

    inner join vManagedEntityType MET on MET.ManagedEntityTypeRowId = ME.ManagedEntityTypeRowId

    inner join vManagedEntity TLME on TLME.ManagedEntityRowId = ME.TopLevelHostManagedEntityRowId

    inner join vManagedEntityType TLMET on TLMET.ManagedEntityTypeRowId = TLME.ManagedEntityTypeRowId

    where PERF.DateTime > (GETUTCDATE() - 182)

    order by ME.ManagedEntityTypeRowId


  11. Click Save


B. Refresh the Data and Review Reports
  1. Select Refresh All from the Refresh pulldown in the ribbon in the Home menu 

  2. Click Close when the refresh is complete
  3. Close the Power Pivot window
  4. Click OK in the Power View dialog window 

  5. Select Austin from the Themes pulldown in the Themes section of the ribbon of the POWER VIEW menu to distinguish the reports from the seven day model 

  6. Review the reports to make sure they are still presentable 

  7. Save the workbook