locked
Way to query date last accessed? RRS feed

  • Question

  • Greetings. In the DB that houses PowerPivot information, is there a way to query the last time a PowerPivot workbook was last accessed? I believe this info is stored in the Management Dashboard, but ours isn't working properly. Note that I am NOT looking for last refresh date.


    Thanks in advance! ChrisRDBA

    Friday, January 4, 2019 10:43 PM

Answers

  • I'm pretty sure that @ChrisDBA is referring to PowerPivot workbooks hosted in Sharepoint due to the mention of the Management Dashboard. So this information should be in the DB that houses the metadata for the Sharepoint PowerPivot feature. However I don't currently have access to a Sharepoint instance to be able to tell you which specific tables you can check. PowerPivot in Sharepoint has a feature where it can remove workbooks that have not been accessed recently from Memory if there is memory pressure, so it has to keep track of this information internally. 

    My guess is that the Management Dashboard is supposed to be the officially supported way of accessing information like this as Microsoft does not officially support people querying the Sharepoint databases.

    And I know that Sharepoint does keep metadata against an xlsx file to indicate if it contains a PowerPivot model, but I'm not sure if it also tracks the last accessed date. This would be a question to ask in one of the Sharepoint forums. But if you could write a small C# or Powershell utility to query the supported Sharepoint APIs that might be another option. You could then maybe push this information into a custom database that you could then report off.

    But if you can't get this information from the supported Sharepoint APIs and you can't fix your Management Dashboard or it does not display the information in the format you need - you should be able to get this information from the PowerPivot DB. However, be aware that querying this DB directly is not a supported by Microsoft. So if it causes instability in your Sharepoint environment you will not be able to get help from Microsoft Support, they will just tell you to stop doing this.

    So to this end, if you go down this route, I would suggest keeping your queries short and simple using NOLOCK hints, ideally extracting this information to another database that you can then do your reporting out of. 

    The other complication you are going to face is that I think the PowerPivot DB stores references to Sharepoint Item Ids which are just GUIDs, so to get the workbook names you need to look this up in the content DBs and depending on the size and complexity of your Sharepoint environment. The best thing to do here would be to write a small C# or powershell utility that uses the Sharepoint object model API to get the workbook name. I would not risk querying the core Sharepoint content databases directly. Even with NOLOCK queries there is still a risk of causing unintended issues as every query needs to take out a schema lock. Sharepoint can be a bit of a beast to deal with at the best of times, you really want to try to use supported APIs where ever you can.


    http://darren.gosbell.com - please mark correct answers

    Sunday, January 6, 2019 1:45 AM

All replies

  • This article explains how to use a top value query to find the most recent or earliest dates in a set of records. You can use the information that this type of query returns to answer a variety of business questions, such as when a customer last placed an order.

    What do you want to do?
    Understand how top value queries work with dates
    Find the most recent or earliest date
    Find the latest or earliest dates for records in categories or groups
    Find the most recent and the earliest dates

    Understand how top value queries work with dates
    You use a top value query when you need to find records that contain the latest or earliest dates in a table or group of records. You can then use the data to answer several types of business questions, such as:

    When did an employee last make a sale? The answer can help you identify a most-productive or least-productive employee.
    When did a customer last place an order? If a customer has not placed an order for a given period of time, you may want to move the customer to an inactive list.


    Who has the next birthday, or the next n birthdays?


    Rules for creating and using top values queries
    You create a top value query by first creating a select query. Depending on the results that you want, you apply either a sort order to the query, or you convert the query into a totals query. If you convert the query, you then use an aggregate function, such as Max or Min to return the highest or lowest value, or First or Last to return the earliest or latest date. You use totals queries and aggregate functions only when you need to find data that falls into a set of groups or categories. For example, suppose that you need to find the sales numbers for a given date for each city in which your company operates. In that case, the cities become categories (you need to find the data per city), so you use a totals query.


    As you proceed, remember that, regardless of query type, your queries must use fields that contain descriptive data, such as customer names, and also a field that contains the date values that you want to find. In addition, the date values must reside in a field set to the Date/Time data type. The queries that this article describes fail if you try to run them against date values in a Text field. In addition, if you want to use a totals query, your data fields must include category information, such as a city or country/region field.


    Choosing between a top values query and a filter
    To determine whether you should create a top values query or apply a filter, choose one of the following:


    If you want to return the records with the most recent or latest dates in a field, and you do not know the exact date values, or they don't matter, you create a top values query.


    If you want to return all the records where the date matches, is prior to, or later than a specific date, you use a filter. For example, to see the dates for sales between April and July, you apply a filter. A complete discussion of filters is beyond this topic.


    For more information about creating and using them, see the article Filter: Limit the number of records in a view.


    Top of Page


    Find the most recent or earliest date
    The steps in this section explain how to create a basic top values query that uses a sort order, plus a more advanced query that uses expressions and other criteria. The first section demonstrates the basic steps in creating a top values query, and the second section explains how to find the next few employee birthdays by adding criteria. The steps use the data in the following sample table.
    Saturday, January 5, 2019 8:31 AM
  • DB that houses PowerPivot information, is there a way to query the last time a PowerPivot workbook was last accessed? 

    Not clear what you mean? Do you mean a DB is the data source for your PowerPivot model and you load the data from it?

    PowerPivot stores the data internal in the Excel file, once loaded it don't access the DB until you refresh the data. So you could only see the last date the PP file was accessed if the user saves the Excel until on close; then you can see it on file system level on the last file update date.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, January 5, 2019 8:34 AM
  • I'm pretty sure that @ChrisDBA is referring to PowerPivot workbooks hosted in Sharepoint due to the mention of the Management Dashboard. So this information should be in the DB that houses the metadata for the Sharepoint PowerPivot feature. However I don't currently have access to a Sharepoint instance to be able to tell you which specific tables you can check. PowerPivot in Sharepoint has a feature where it can remove workbooks that have not been accessed recently from Memory if there is memory pressure, so it has to keep track of this information internally. 

    My guess is that the Management Dashboard is supposed to be the officially supported way of accessing information like this as Microsoft does not officially support people querying the Sharepoint databases.

    And I know that Sharepoint does keep metadata against an xlsx file to indicate if it contains a PowerPivot model, but I'm not sure if it also tracks the last accessed date. This would be a question to ask in one of the Sharepoint forums. But if you could write a small C# or Powershell utility to query the supported Sharepoint APIs that might be another option. You could then maybe push this information into a custom database that you could then report off.

    But if you can't get this information from the supported Sharepoint APIs and you can't fix your Management Dashboard or it does not display the information in the format you need - you should be able to get this information from the PowerPivot DB. However, be aware that querying this DB directly is not a supported by Microsoft. So if it causes instability in your Sharepoint environment you will not be able to get help from Microsoft Support, they will just tell you to stop doing this.

    So to this end, if you go down this route, I would suggest keeping your queries short and simple using NOLOCK hints, ideally extracting this information to another database that you can then do your reporting out of. 

    The other complication you are going to face is that I think the PowerPivot DB stores references to Sharepoint Item Ids which are just GUIDs, so to get the workbook names you need to look this up in the content DBs and depending on the size and complexity of your Sharepoint environment. The best thing to do here would be to write a small C# or powershell utility that uses the Sharepoint object model API to get the workbook name. I would not risk querying the core Sharepoint content databases directly. Even with NOLOCK queries there is still a risk of causing unintended issues as every query needs to take out a schema lock. Sharepoint can be a bit of a beast to deal with at the best of times, you really want to try to use supported APIs where ever you can.


    http://darren.gosbell.com - please mark correct answers

    Sunday, January 6, 2019 1:45 AM