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:
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
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:
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:
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.
7. Now installation is complete at this point.
8. If the StreamInsight Service is not started, then go to services and start it.
In services: Right click > Start:
9. Now, You can run samples. To access them you can go to Start > all programs > StreamInsight Samples
Conclusion
In this blog-post, we saw how to install StreamInsight 2.0 on your machine.
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:
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!
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:
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: