13

I have a requirement in our 8.1 update 2 solution to provide a sorting mechanism for pages based on the number of views. This data lives in the analytics database and I'm looking for a way to retrieve the data without querying the database directly. Is there an API in place out of the box that I can use to get an item's view count?

I've verified that the data I need exists in the [Sitecore_Analytics].[dbo].[Fact_PageViews] table. From a best practices standpoint, what is the best way to retrieve this data and use it in our components?

19

Here is how you can do this:

1. Create reporting query item

Create somewhere an item of template /sitecore/templates/System/Analytics/ReportQuery

Example fields configuration:

Write down an item ID somewhere, we will need it later.

enter image description here

2. Create item based reporting query class

Example code:

Note: This Constants.ReportingQueries.ItemVisits is an item ID of your reporting query item, put this into constant or pull from configuration.

public class ExampleVisitsQuery : ItemBasedReportingQuery
{
    public ExampleVisitsQuery(ReportDataProviderBase reportProvider = null)
        : base(Constants.ReportingQueries.ItemVisits, reportProvider)
    {
    }

    public ID ItemId { get; set; }

    public long Visits { get; protected set; }

    public override void Execute()
    {
        var parameters = new Dictionary<string, object>
        {
            {"@ItemId", ItemId}
        };

        var dt = this.ExecuteQuery(parameters);

        if (dt != null && dt.Rows.Count > 0)
        {
            var result = dt.Rows[0]["Visits"];
            if (result != null && result != DBNull.Value)
                Visits = (long)dt.Rows[0]["Visits"];
        }
    }
}

3. Get your data

This is where we get the data.

var query = new ExampleVisitsQuery (this.ReportDataProvider)
{
    ItemId = id
};

query.Execute();

long itemViews = query.Visits;

Note: this.ReportDataProvider is an instance of ReportDataProviderBase type.

In this can be null

As a output you get number of views.

4. Sort your data

Once you get Visits for all items you can easily sort them.

Assuming you have got a list of items ids as an input here is what you can do:

if (entryIds.Any())
{
    var views = new Dictionary<ID, long>();

    foreach (var id in entryIds)
    {
        var query = new ExampleVisitsQuery(this.ReportDataProvider)
        {
            ItemId = id
        };

        query.Execute();

        var itemViews = query.Visits;

        if (itemViews > 0)
            views.Add(id, itemViews);
    }

    var ids = views.OrderByDescending(x => x.Value).Take(maxCount).Select(x => x.Key).ToArray();
}

source: WeBlog

| improve this answer | |
  • is there a way to read from another datasource? for example, i want to read from table interactions in the xdb_collection.Shard0 database. – AmiraElEwady Oct 2 '18 at 11:18
3

I'm not sure if that's the best solution, but yes, you can get the information you're looking for using Fact_PageViews table.

You can use query like that:

SELECT TOP 10 ItemId, count(*) as c FROM Fact_PageViews GROUP BY ItemId ORDER BY c DESC

or with some more options:

string query = string.Format("SELECT TOP {0} ItemId, count(*) as c FROM Fact_PageViews WHERE Date > DATEADD(DAY, -{1}, GETDATE()) GROUP BY ItemId ORDER BY c DESC", itemsCount, timespan);
| improve this answer | |
  • I did rough out a similar query that can provide the results needed. Ideally though I'd like to avoid querying the db directly, even Sitecore recommends against it from what I've seen. Not sure if it's possible though, which is why I reached out here. – BillCacy Oct 12 '16 at 20:20
  • I haven't seen any better solution. I've used this approach once or twice before. – Marek Musielak Oct 12 '16 at 20:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.