Get most popular pages in Sitecore

Assume you have installed the DMS package and it is working. In other words, you can see some data generating in the dbo.Pages table from the your analytics database.
(How to make sure DMS is installed correctly:
http://www.sitecore.net/deutsch/Community/Technical-Blogs/John-West-Sitecore-Blog/Posts/2011/08/Troubleshooting-Analytics-is-Disabled-with-the-Sitecore-Customer-Engagement-Platform.aspx)

Now we need to get the most popular pages (i.e., most viewed pages) or its statistics in Sitecore. Of course, it is easier to expose the relavant tables in the database in your solution using entity framework. However it is a really bad idea; because you don't really want to allow any other operations except retrieval.

So it is better to create a view in the database, fast and readonly ensured. In my case, I actually created a view outside any Sitecore default database. The query is something like:

SELECT     TOP (100) PERCENT pa.ItemId, it.TemplateID, pa.PageViewCount
FROM         (SELECT     ItemId, COUNT(*) AS PageViewCount
                       FROM          My_Sitecore_Analytics.dbo.Pages
                       GROUP BY ItemId) AS pa INNER JOIN
                      My_Sitecore_Master.dbo.Items AS it ON pa.ItemId = it.ID
ORDER BY pa.PageViewCount DESC

Then include this view in your solution using entity framework. You can freely query the view using Linq.

Extracting Twitter Posts and Twitter Mentions

    public class Twitter
    {
        private static XDocument xDoc = null;
 
        public Int32 TweetCount { get; set; }
        public String TwitterProfileName { get; set; }
 
        public List<Tweet> Tweets
        {
            get
            {
                XDocument xml = LoadTweetXML(false);
 
                if (xml == null)
                    throw new Exception(String.Format("Failed to get tweets from {0}", TwitterProfileName));
 
                var query = from e in xml.Descendants("item")
                            select new Tweet
                            {
                                Id = e.Element("guid").Value,
                                Title = e.Element("title").Value
                                        .Replace(String.Format("{0}: ", TwitterProfileName), String.Empty),
                                Link = e.Element("link").Value,
                                Content = e.Element("description").Value,
                                Published = Convert.ToDateTime((e.Descendants("pubDate").First().Value)),
                            };
 
                return query.ToList();
            }
        }
 
        public List<Tweet> TweetMentions
        {
            get
            {
                XDocument xml = LoadTweetXML(true);
 
                if (xml == null)
                    throw new Exception(String.Format("Failed to get tweets mentions from {0}", TwitterProfileName));
 
                XNamespace ns = "http://www.w3.org/2005/Atom";
 
                var query = from item in xml.Descendants(ns + "entry").Take(TweetCount)
                            select new Tweet
                            {
                                Id = item.Element(ns + "id").Value,
                                Published = DateTime.Parse(item.Element(ns + "published").Value),
                                Title = item.Element(ns + "title").Value,
                                Content = item.Element(ns + "content").Value,
                                Link = (from XElement x in item.Descendants(ns + "link")
                                        where x.Attribute("type").Value == "text/html"
                                        select x.Attribute("href").Value).First(),
                                Image = (from XElement x in item.Descendants(ns + "link")
                                         where x.Attribute("type").Value == "image/png"
                                         select x.Attribute("href").Value).First(),
                                Author = new Author()
                                {
                                    Name = item.Element(ns + "author").Element(ns + "name").Value,
                                    Uri = item.Element(ns + "author").Element(ns + "uri").Value
                                }
                            };
 
                return query.ToList();
            }
        }
 
        public Twitter(string screenName, int numTweets = 10)
        {
            TwitterProfileName = screenName;
            TweetCount = numTweets;
        }
 
        private XDocument LoadTweetXML(bool isMentions)
        {
            try
            {
                string url;
                if (isMentions)
                    url = String.Format(
                        "http://search.twitter.com/search.atom?lang=en&q={0}",
                        System.Web.HttpUtility.UrlEncode(TwitterProfileName));
                else
                    url = String.Format(
                        "http://api.twitter.com/statuses/user_timeline.rss?screen_name={0}&count={1}",
                        TwitterProfileName, TweetCount);
                xDoc = XDocument.Load(url);
                return xDoc;
            }
            catch
            {
                return null;
            }
        }
 
        public class Tweet
        {
            public string Id { get; set; }
            public DateTime Published { get; set; }
            public string Title { get; set; }
            public string Content { get; set; }
            public string Link { get; set; }
            public Author Author { get; set; }
            public string Image { get; set; }
        }
 
        public class Author
        {
            public string Name { get; set; }
            public string Uri { get; set; }
        }
    }