Time series

SQL Server Analysis Services MDX: How to display last/rolling 12 months trend in MDX?

Standard

Problem Statement:

One of the common business requirement for Dashboards is to be able to see last/rolling 12 month trend on a chart. To meet this requirement, you’ll need to get a data set from the data source and if it’s a SSAS cube, How do you get last/rolling 12 months using MDX?

Note: It’s not a rolling 12 month sum. If you’re looking to do that, please refer: Sum of Last N Years, Quarters, Months, Periods.

Solution:

Here’s the pseudo code, I hope it gives you a good starting point:

[code language=”sql” gator=”false”]
SELECT NON EMPTY
{
[Measures].[MeasureName1],
[Measures].[MeasureName2]
}
ON COLUMNS,
{
[Fiscal Period].[Fiscal Period]
}
ON ROWS
FROM
(SELECT STRTOMEMBER(@FiscalPeriod).Lag(12):STRTOMEMBER(@FiscalPeriod) on 0 FROM [CubeName])

[/code]

Note the use of sub select & query parametrization. You’ll need to pass in current month value in the fiscal period parameter. I hope this helps!

Adding a TrendLine to a Time Series Line Chart in Excel 2010:

Standard

I was playing w/ a time series data set in Excel 2010 and learned how to add a Trend-line and in this blog post, I’ll share how I added it:

First up, How is Trend-line useful? Here are few answers:
– It helps us see how data is changing over time, in other words, it helps us find “trends”
– It helps us forecast future.

With that, here is the chart without Trend-line:on time flight arrivals excel without trendline

Now let’s add the trend-line and you’ll be able to compare on your own how Trend-line makes it easier to spot “trends”. Here are the steps:

1. select the line > right-click > add trend line

add trendline time series

2. configure the trend-line options

trend line configuration options excel

3. I also changed the line style

4. And Here’s the chart w/ trend-line

american airlines on time flight arrivals excel with trendline

Conclusion:

In this post, we saw how to add trend-line in the time series chart in excel 2010

Related articles

Let’s Install R & RStudio on Windows Machine!

Standard

I was recently searching for a way to do some text mining on Twitter Data. I was interested in a tool that has some “library” that helps to fetch twitter data & later, I wanted to create visualization like say word cloud, time series. etc. Turns out that “R” perfectly suited my needs because of libraries/packages such as TwitteR and ggplot2 – And so, I downloaded and installed R and RStudio on my windows machine. Here are the steps (I am using Windows Server 2008 R2 machine 64 bit):

1. Download R for Windows:

Install R for windows twitter analytics

2. After downloading it > Install it by leaving all options to default.

3. Download RStudio Desktop for windows:

install R studio for windows desktop

4. Install RStudio > leave all options to default.

5. Open RStudio > In the Bottom Right Pane, switch to Packages Tab > Click on Install Packages > In the packages box, type in ggplot2 and > click on Install.

ggplot2 package R Rstudio

5. Check that ggplot2 successfully unpacked and installed > Now similarly install the package: twitteR > make sure it is successfully unpacked and installed.

twitteR package R Rstudio windows analytics6. And I quickly created a chart of Twitter UserName vs Number of Tweets for #sqlpass:

we can do much mire but just wanted to show how you can do social media analytics with R!

Twitter Analytics with R Studio windows Bar Plot

Conclusion:

In this blog post, we saw a step by step process to download and install R and R studio on a windows machine.