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:
Image Credit & For step by step tutorial, please refer to: Integrate Power View with SharePoint using web parts
Environment: SQL Server 2012 in SharePoint Integrated mode w/ Power View Activated for the site. Also, SharePoint Enterprise 2010.
Topic: Integrating SSRS with SharePoint
Speaker: Kevin Goff
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
Take difference between two data values.
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:
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?
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.
Strange date relationships with #PowerPivot
Date and Date/Time – Sneaky Data Types!
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
“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.”
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.
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
3) 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!
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!
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
||Office 2013 [Professional Plus editions], SharePoint 2010 onwards [Enterprise Editions]
||Public, Online, Desktop, Server
||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
||[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.
||Ad-hoc reporting a.k.a Self Service Business Intelligence
|Ease of use
|Custom Shape Files
||Power View is new to the game
||Tableau is a leader in data visualization space
||Power Map is an add-in by Microsoft that focuses on “Mapping”. It’s part of the newly announced Power BI suite.
I’m at PASS Summit 13 this week and I’m seeing nice amount of excitement among Business Intelligence Pros about “Power BI” so I thought I would post a brief post about problems that Power BI addresses:
#1: Mobile BI:
- The Visualizations that you’ll publish to Power BI sites would use the HTML 5 rendering & hence the support for Mobile BI.
- There’s also a native Microsoft Power BI app for Windows 8 so you can use surface tablets for Mobile BI. IOs (apple) or Android native apps have NOT been announced yet.
#2: An end-to-end self-service suite of tools for Power Users:
- Users will be enabled to search, analyze and visualize data using Power Query, Power Pivot & Power View. Plus it allows them a way to collaborate with each other.
#3: Easier way to search for data that’s available inside & outside for organization:
- One of the key themes of “Power BI” has been easier discovery of data that’s available to you to analyze.
- This is really important from an adoption standpoint because with the technologies that we have today, we can’t enable power users to search for “data-sets”. Power BI enables IT to publish Data Catalogs which I imagine would make it easier for power users to search & connect to data sets & start analyzing!
#4: cool tools that people *want* to use it.
- Power BI has rich user experience.
- Users can build cool visualizations & create some business value
- Since this is a “self-service” suite, it seems to be designed as a user-friendly set of tools. This is important because if a user is “confused” or “over whelmed” then they are not going to use the tool & find something else.
#5: Gateway to the future:
- The Human-computer interaction is evolving. Over the past couple of years, we’ve seen tools like Siri (apple audio powered personal assistant) which allows users to use “Natural Language” to interact with computers
- Power BI has a tools called “Q&A” that allows users to do business analysis using “Natural Language”. I don’t know the maturity of the current offering but I’m excited about the possibilities that this could offer in future!
- Imagine a computer (in some amazing futuristic form) and you say to it “sales trend in north america region during past 12 months” and it gives a you nice trend chart that you can use to start analysis.
Cost-benefit analysis of this cloud-powered suite of tools*
- (Book Mark for future editing: The cost of the tool & its general availability is not announced, so I didn’t talk about the cost-benefits that we might see so I’ll defer this analysis until after the details are announced)
What do you think? What are the problems and pain-points that Power BI is trying to solve?
There’s a SSAS cube.
User’s use more than one end-user tool to access the cube.
How do you track the usage by End-User Tool?
By Default the SSAS Trace does not capture all End-User Tools like Excel, SSRS, Third-Party Tools.
So what do you do?
There’s a connection string property called Application Name that you can use to capture this information. Once you set it, the application name property will start showing up in the SSAS Trace:
you’ll have to modify ALL connection strings used to point to the cube though.
Connection String Properties (Analysis Services)
Here’s a SSRS expressions code that I’ve using lately to show current month name & year in SSRS reports:
="REPORT NAME"&" - "&MonthName(Month(Today()))&"'"&Year(Today())
This is a nice little code that you can add to your expressions which will show current name & year. Small little things that you can do to make your business users happy! I hope this helps someone out there.