PowerPivot DAX: Moving Average

Standard

A quick blog post about sharing PowerPivot DAX formula to calculate moving average. Please use this as building block for your scenario:

Here’s the Data:

DATE AMOUNT
6/1/2013 50
6/2/2013 40
6/3/2013 30
6/4/2013 20
6/5/2013 10
6/6/2013 10
6/7/2013 20
6/8/2013 30
6/9/2013 40
6/10/2013 50

(usually, the date would in a date table. For the purpose of sharing building block of this formula, I kept it this way to keep it simple)

Formula (Calculated Measure):

[code language=”text”]
Moving Average Last 3 days:=CALCULATE(AVERAGE(TableName[Amt]),DATESINPERIOD(TableName[date],PREVIOUSDAY(TableName[date]),-3,day))
[/code]

Results via a Pivot Table: powerpivot dax moving average

Note that the formula is a building block and you’ll have to make changes as per your requirement and data model. Feel free to leave a comment if you need some assistance from my side. And also consider using the PowerPivot forum to reach out to community: MSDN Forum – PowerPivot for Excel

How I installed StreamInsight 2.0 on my demo machine:

Standard

I installed StreamInsight 2.0 on my demo machine today and so I thought I would document the process.

Before we begin, few references to existing documents on the interwebs: Official documentation about Installation is here: http://msdn.microsoft.com/en-us/library/ee378749.aspx and Introduction to concepts of StreamInsight can be accessed via the following resources:

1)      MSDN documentation: http://msdn.microsoft.com/en-us/library/ee391416.aspx

2)      Pluralsight: http://blog.pluralsight.com/2012/01/17/free-streaminsight-training/

3)      SQL server central article: http://www.sqlservercentral.com/articles/StreamInsight/69208/

4)      A channel 9 video pointed out by Johan Ahlen: http://joinsights.com/2011/05/22/great-streaminsight-presentation-by-torsten-grabs/

Now, here are the steps that I took to install StreamInsight on my demo machine:

1)      I located the StreamInsight installer inside the SQL Server 2012 Developer edition setup that I had:

1 Installing streaminsight sql server developer edition

Note that even though StreamInsight is licensed with SQL Server – It is different “software” that solves different technical problem(s). And note that StreamInsight does not have dependencies on SQL Server. It is a separate install.

2. On the Instance Configuration page, I added “StreamInsightInstaller” as the instance name. This is the first instance of StreamInsight that I am installing on my demo machine

3. On next dialog box, I added the product key that I have for SQL Server Developer edition. You also have the option to activate 180 day trial.

4. Then specify the StreamInsight service and group settings

5. Click on Install on next dialog box

6. You would also need to install the SQL compact edition. To do that, I navigated to C:Program FilesMicrosoft StreamInsight 2.0Redist

Note that, if you have chosen the x64 version then you would have to first install the x86 version of SQL Server compact and then the x64 version of SQL Server Compact.2 stream insight install sql serve compact edition

7. Now installation is complete at this point.

8. If the StreamInsight Service is not started, then go to services and start it.

3 install streaminsight service not started

In services: Right click > Start:

4 windows service streaminsight start

9. Now, You can run samples. To access them you can go to Start > all programs > StreamInsight Samples

5. stream insight samples installation

Conclusion

In this blog-post, we saw how to install StreamInsight 2.0 on your machine.

Error in Importing KPI’s to Performance Point 2010 Dashboard designer from SQL Server 2012 Analysis Services MultiDimensional cube

Standard

This blog post is meant to document how I solved an error while trying to import KPI’s to Performance Point 2010 Dashboard Designer from SSAS 2012 Multidimensional cube. The error said “An unexpected error occurred.. details have been logged for your administrator”. Here are the details:

1) few days back, I had solved the error listed here: “An unexpected system error occured…” – While trying to establish a Data Connection in Performance Point 2010 Dashboard Designer –  And after I had solved this error, I got another error and this time it was about importing KPI’s in the Dashboard Designer. Here’s how I was able to solve it:

2) So to view the error I used the SPTraceVIew Tool

3) And via SPTraceVIew, I was able to see the message:

An unexpected error occurred.  Error 47205.
Exception details:
System.IO.FileNotFoundException: Could not load file or assembly ‘Microsoft.AnalysisServices, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.
File name: ‘Microsoft.AnalysisServices, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’
at Microsoft.PerformancePoint.Scorecards.Server.ImportExportHelper.GetImportableAsKpis(DataSource asDataSource)
at Microsoft.PerformancePoint.Scorecards.Server.PmServer.GetImportableAsKpis(DataSource dataSource)

4) I checked that the Microsoft.AnalyisServices dll was present at c:/windows/assembly

5) What I didn’t realize was that the version number was different. The version that I had on my machine was 11 while the message clearly mentioned that it required 10. Thanks to this tip here: Creating scorecard error

6) So I followed the instructions that was on the MSDN forum thread and installed the AMO with version 10 via “Microsoft SQL Serer 2008 feature pack, April 2009”

7) And after installing the version 10 of the AMO, I was able to import KPI’s from SSAS 2012 multidimensional cube!

SQL Azure: Is there a “per transaction” cost for Windows Azue SQL Database?

Standard

Question: Is there a “per transaction” cost for Windows Azue SQL Database (SQL Azure)?

Short Answer: No

I recently answered the question on MSDN forum where the question was about Transactions and the associated cost in SQL Azure. As of now, There is no “per transaction” cost associated with SQL Azure. There are two parameters that affect your SQL Azure Bill: 1) Database Size 2) Outbound Data Transfer and an example of an outbound transfer would be data access by an application hosted outside of your Azure DB’s data-center.

If you want to read more about SQL Azure pricing, here’s the official resource:

https://www.windowsazure.com/en-us/pricing/details/#header-4

And here are some of the blog-posts that I’ve written on the same topic:

SQL Azure got a new pricing model!

SQL Azure: Indexes are very helpful but they cost $Money$

Getting started with SQL Azure – Part 3: Provisioning and Billing Model of SQL Azure << Paras Doshi

For my Archives: Few questions answered on Windows Azure & SQL Azure MSDN forums

Standard

I normally Blog about the answers that I give out on MSDN forums. The answer on MSDN forum is generally brief and to the point and in the blog post – I expand it to cover related areas. Here are the questions for which I didn’t choose to write a blog. So I am just going to archive them for now: