News from PASS Summit’14 for Business Analytics Professionals: #sqlpass #summit14

This post is a quick summary for all Business Analytics related updates that I saw at PASS Summit’14:

1. Theme of the Keynote(s)/Session(s) seemed to be around educating the community about the benefits of the NEW(er) tools. I saw demos/material for cloud-based tools like SQL databases, Azure stream analytics, Azure DocumentDB, AzureHDInsight & Azure Machine learning. The core message was pretty clear: A data professional does two things – 1) Guards data OR 2) helps to generate Insights from Data – And they will need to keep up-to-date on the new tools to future-proof their career.

Read more about this here: http://blogs.technet.com/b/dataplatforminsider/archive/2014/11/05/microsoft-announces-major-update-to-azure-sql-database-adds-free-tier-to-azure-machine-learning.aspx

2. Coming soon: Power BI will be able to connect to on-premise SSAS data sources (multi-dim & tabular).

3. Coming soon: A better experience to create Power BI dashboards.

Read more about Power BI updates here: http://www.jenunderwood.com/2014/11/05/pass-summit-2014-bi-news/

4. Azure Machine Learning adds a free-tier! You won’t need a credit-card/subscription to sign up for this.

5. I also saw sessions proposing new way of thinking about an architecture for “Self Service BI” and “Big Data” which might be worth following because since these are newer tools, it’s definitely worth considering an architecture that’s designed to make the most of the investments in these new tools. That’s it & I’ll leave you with a quote from James Phillips from Day 1’s keynote:

Quick note on evolution of Business Intelligence & Microsoft’s vision for BI space:

I attended “Enabling Familiar, Powerful Business Intelligence hosted by PASS BA VC last week & I got to listen to Microsoft where they shared their vision for the BI space, so I thought of posting this quick note about it:

“Corporate BI” has been around for may years. This space has established players like Microsoft, SAP, IBM, Oracle. But in recent times, “Self Service BI” space has been gaining momentum. Players like Qlikview & Tableau that lead the Self Service BI space have been ranked as leaders in the Gartner 2014 magic quadrant. Microsoft has also been making serious advancements in this space since last few years & with their latest offering called “Power BI” they have shown that they putting their bets on Self Service BI space. So, as Microsoft said in the event, they view themselves as the only player that offers a full suite of Corporate BI as well as Self Service BI:

Evolution of BI

you can watch the recorded session here: http://www.youtube.com/watch?v=0yKhxSPlykg

Questions Power Users Ask about Excel: #2 of N

In this series, I intend to document common questions asked by Power users about Excel connected to SSAS cubes (or data warehouse) after they go beyond the basic stage of understanding & using Row labels, column labels, report filter & values in Pivot Tables. This post is #2 of N:

How to add calculated measures (Excel 2010)?

Situation:

Power users in your organization/company are developing Excel 2010 reports against OLAP Cubes. They want to add their own calculations for analysis.

BUT Excel 2010 does not allow end-users to add their own private MDX calculations.

Solution:

A Free Community Excel add-in helps in this case. It’s called “OLAP Pivot Table Extensions”. Here are the steps to download, Install and use it:

1) Check Excel Version (32-bit/64-Bit)

Open Excel > File > Help> About Microsoft Excel

excel version 32 bit or 64 bit

2) Download OLAP Pivot Table Extensions and Install it

Download URL: http://olappivottableextend.codeplex.com/ > Navigate to Downloads Tab & install the right version based on your local excel version.

In my case, I had 64-bit excel so I’ll download the 64-bit version of OLAP Pivot Table extensions.

Excel Pivot Table Extensions

Close Excel and Install add-in

3) Add simple measure

Open Excel.

Connect to Cube.

Let’s add a simple measure that calculates the difference between two measures.

So I created a simple Pivot Table that looks like:

Excel Simple Pivot Table OLAP

Now let’s add a calculated measure:

Select the Pivot Table > Right Click > OLAP Pivot Table Extensions

OLAP PIVOT TABLE EXCEL EXTENTSIONS

On the next screen, please enter the name Difference. and the simple formula:

[Measures].[Meausre1]-[Measures].[Measure2]

(please replace measure1 & measure2 with the measure names from your cube)

And click on Add to Pivot Table

Simple Formula OLAP excel calculated member

You can now see that the calculated measure Difference got added to the Pivot Table!

 

Excel olap pivot table with calculated member

Note

1) OLAP Pivot Table extensions is not supported by Microsoft. It’s a community software.

2) To maintain the single version of truth (after all that’s why you create cubes/Data-warehouse’s!), it’s recommended that calculated measures that end users want in the cube.

Conclusion

In this post we saw a very common question “How to add calculated columns” asked by Power users about excel while they analyze data from SSAS cube.

Previous Article: http://parasdoshi.com/2013/12/10/questions-power-users-ask-about-excel-1-of-n/

How to embed or integrate Power View reports into SharePoint pages?

Why do you want to do that?

One of the common tactic that you can consider to drive adoption of a Business Intelligence system is to integrate/embed the BI reports to the APPS/SITE that the users are already using. Don’t make your users come to you, go to them! As a part of that, I figured out a way to integrate/embed Power View in a Site that was used by existing user base.

You can integrate/embed Power View reports in SharePoint web Parts. Here’s How:

Power View sharepoint integrate embedImage Credit & For step by step tutorial, please refer to: Integrate Power View with SharePoint using web parts

Note:

Environment: SQL Server 2012 in SharePoint Integrated mode w/ Power View Activated for the site. Also, SharePoint Enterprise 2010.

 

PASS BA VC EVENT: Integrating SSRS with SharePoint on Nov 25th, Monday:

Topic: Integrating SSRS with SharePoint

Register TODAY! URL: bit.ly/PASSBAVC

Speaker: Kevin Goff

Topic Abstract:

Have you ever seen a SharePoint site that integrates reports from SSRS, and wonder how all the pieces fit? If so, this session is for you. I’ll cover the necessary integration/configuration steps for integrating SSRS 2008R2/SSRS 2012 with SharePoint 2010/2013, as well as deploying reports to a SharePoint location. Because different versions of SSRS integrate differently with the available versions of SharePoint, I’ll provide a feature matrix for specific version integrations. I’ll also show how to integrate SSRS reports into SharePoint/PerformancePoint Services dashboard pages, as well as how to schedule reports for delivery to SharePoint document libraries. You’ll also see the improvements from Microsoft that make reporting against SharePoint lists much easier than before. At the end, I’ll show 2 new features in SSRS: Data Alerts in SharePoint, along with the new SSRS data visualization tool, Power View

I hope to see you there!
Paras | VP of Marketing | PASS BA VC

 

Power Pivot DAX: Difference between two DATE values

Requirement:

Take difference between two data values.

Example:

EndDate: 11/20/2013

StartDate: 11/14/2013

DateDifference: 6

Let’s start writing some DAX!

Ok, seems simple, right? Try creating a measure DateDifference:=[EndDate]-[StartDate]

Did that work? NO? Does it return date?

Don’t worry, Here’s the solution. Try creating following DAX Measure:

DateDifference:=1.0*([EndDate]-[StartDate])

That’s about it for this post. Here are some related Posts:
Calculate the difference between two dates in DAX
Q: How can I calculate difference between two dates in DAX (seconds, minutes, hours, days and months)
NETWORKDAYS() Equivalent in PowerPivot?

Power Pivot: Casting DateTime to Date in SQL Server source query

DateTime columns can be tricky for analysis purposes. They don’t work well with Pivot Tables because of the time part, each value seems unique to the Pivot Table & it also creates problems while creating relationships with Date Dimensions. And so, It’s a common need to convert them to just Date before analyzing data & also a common need to create a relationship between the Date (and not DateTime) with Date Dimension Table.

So if it’s possible, I try to do the data type conversion in the source system query. If your source system is SQL Server, you could use this piece of code:

 select [your-fields],cast([DateTime_Col] as date) as Date_Col from TableName
 

Doing the data type conversion upfront in the source system query is a good thing to do. And I hope this is helpful.

Related Posts:
Strange date relationships with #PowerPivot
Date and Date/Time – Sneaky Data Types!

 

PASS Business Analytics VC’s Online Event: “Power BI Info Management and Data Stewardship”

Power BI is an exciting new technology in the business analytics space from Microsoft. I’ve played with its current preview version & attended couple of sessions on Power BI at PASS Summit 2013. Based on my first impression, I noted down Problems that Power BI solves. Note that as of today, it’s in preview & so information around cost is not availale yet but I try to learn and understand as much as I can Today about how Power BI is going to help business users & power users in the future. As a part of that, I’m attending Business Analytics VC’s session on “Power BI Info Management and Data Stewardship” by Matthew Roche & Ofer Ashkenazi on Nov 7th 12 PM EST.

Topic: Power BI Info Management and Data Stewardship

Date & Time: Nov 7th 12 PM EST

Here’s the Link to register: http://bit.ly/PASSBAVC

Topic Abstract:

“Business intelligence tools continue to improve, letting users shorten their time to insight and take that insight to more devices in more places. But this evolution of BI doesn’t change one fundamental fact of information management: You can’t gain insight from data you can’t access.

In this session, Matthew Roche and Ofer Ashkenazi will introduce the role of the data steward and the self-service information management capabilities included in Power Query and Power BI for Office 365, focusing on how Power BI empowers business users to add value to the organization.”

Closing note:

I recently volunteered at Business Analytics VC as VP of Marketing, so it’s in my interest to spread word about the event but I would not spread word about something unless it gets me personally excited about it! :)

I hope to see you at the session and for some reason if you can not make it, we usually record sessions & so you can check out the meeting archives section of the PASS BA VC site after the event.

How to understand Business Logic from Excel 2010 Macros?

There’s one thing common between Excel Macro and a Data warehouse: They help an analyst automate tedious tasks. Macro helps automate some of the common excel tasks. Data Warehouse helps analyst automate their “data cobbling/gathering” process. So recently I worked on a task to extract business logic from an Excel 2010 macro. Here’s what I did:

1) Open the File w/ the Macro, Enable Macro.

2) Toolbar > View > Macros > View Macros > Select the Macro > Edit

Open Macro Excel 20103)  Now once the Microsoft Visual Basic for Applications Dialog box opens up, you should see a macro code. Now do NOT press F5 to run the macro! Instead, go to your first line of code and press F8, this will run the macro one line at a time. Open up your excel sheet that had this macro (in second monitor) and see what happens! [Productivity Tip: You don’t need secondary monitor for this but if you do then it will boost your productivity]

The key as you can imagine is to execute the macro one line at a time & visualize it the second monitor. it gives you a good sense of what’s happening (even if you don’t know how to write macro) and you should be able to understand the macro code or document it for data modeller or BI Dev’s so that they can encapsulate the logic in Data Warehouse/Cubes. Or you might be an excel pro trying to understand someone’s macro, then this trick works there too!

Runnig Macro Line by Line

Note:

1) I’ve shown the steps that I took in Excel 2010. I haven’t tested if it works with other versions

2) I am not an Excel Macro Expert so If there is a better way, I’d be happy to learn about it. I just happened to figure this out & it helped me understand the logic hidden in a 1300+ lines of macro code.

That’s about it for this post. your feedback is most welcome!

[Notes] Atlanta Business Intelligence User Group – Mapping Data in Power View & Tableau

Here are my notes from the Atlanta BI user group that I attended today:

Topic: Head-to-Head on Maps: Mapping Twitter in Tableau and Power View

Power View

Tableau

Setup/Installation? Office 2013 [Professional Plus editions], SharePoint 2010 onwards [Enterprise Editions] Public, Online, Desktop, Server
Licensing Get applicable office/SharePoint license Tableau Public is Free.Tableau Online is $500 per user/year.

Tableau Server has Per-User or Per-Server-Core model

*Note that if you’re using Tableau server’s per-user model the cost is $1000 per named user/consumer (min. 10 users) and $2000 seat for 1 developer.

**Contact Tableau Sales. Please don’t evaluate the product based on the information provided here as the information might change in future

Data Sources [SharePoint] Power Pivot Model, Analysis Service Cubes[Excel 2013] You can connect to data sources that excel supports and then create power view report on top of it. Tableau Public Supports Excel, Access & Text filesOther version of Tableau can connect to a variety of data sources.
Product Category Ad-hoc reporting a.k.a Self Service Business Intelligence Ad-hoc reporting
Ease of use easy easy
Visually Compelling? Yes Yes
Custom Shape Files No Limited
Motion effect No Yes
Product maturity Power View is new to the game Tableau is a leader in data visualization space
Additional Notes Power Map is an add-in by Microsoft that focuses on “Mapping”. It’s part of the newly announced Power BI suite. -