Upcoming Webinar – Creating Power Map Reporting Solutions

image

Come join me for this webinar on 4/17/2014 at 11:00 AM EST

Creating Power Map Reporting Solutions

Excel 2013 with Office 365 brings many new features to the realm of Self-Service BI. There are now new ways to extract, model and present data all within Excel. One of these newest ways to present data is called Power Map. Power Map allows you to create engaging 3D map visualizations to help understand your geographical data better. This session will walk you through the beginning to end process of creating Power Map tours. Register now here.

Upcoming Webinar – Bringing Power BI Q&A to your Organization

image

Come join me for this webinar on 4/15/2014 at 11:00 AM EST

Bringing Power BI Q&A to your Organization

Q&A is an amazing new feature of Power BI that allows you to ask you data question. No longer do you have to write complex SQL queries to get results. Simply type traditional English sentences to return interesting results about your data. This session will walk through how to get a Power Pivot data model ready to use and also the end user experience of Q&A from the Power BI site. Register now here.

Introduction to Power BI Recording and Q&A

I hope you were able to attend my free webinar on Understanding Microsoft Self-Service BI on March 13, 2014. If you weren’t you can now download the recording and slides.

I usually like to post follow up questions and answer that I wasn’t able to address during the webinar. Here’s several that may help you!

Q: Can you explain quickly how to use Power View on SharePoint 2010?

To use these together you must have SharePoint 2010 SP1 Enterprise and SQL Server 2012 Enterprise or BI Edition. Unfortunately, there’s not a quick way to describe the rest of the configuration so here’s a post that may help.

http://blogs.msdn.com/b/karang/archive/2012/04/13/step-by-step-guide-of-installing-power-view-with-powerpivot-2012.aspx

Q: What devices are supported by mobile BI

The Mobile BI will be available on Windows 8 and Windows RT devices. There is also a native Mobile BI app for iPad under development.

Q: Are MS planning to remove the dependency on Silverlight for Power View (etc.) thus making it possible to render on iPad?

Yes! There are actually some public betas of Power View working on HTML 5 instead of Silverlight. This will be the major enhancement that allows support for the mobile BI app on IPads

Upcoming Webinar – Introduction to Power BI

image

Come join me for this webinar on 3/13/2014 at 11:00 AM EST

Introduction to Power BI 

The future of Business Intelligence has many new and exciting features. This world that was once reserved for only experts in SQL Server technologies has expanded to serve the masses with more and more self-service innovations. In this session you will gain an understanding of what Power BI is and get an overview of each of the components. This includes Power Pivot, Power View, Power Query and Power Map, Mobile Power BI and Q&A.

PASS Summit 2013 and SQL Saturday Charlotte in Review

I love reading the many blogs posts that pour in following a PASS Summit.  I think it is fun to read everyone’s experience.  There’s not really a wrong way to attend the conference unless you just hide our in your hotel the whole week.  Here’s how my week went.

Sunday

This was a travel day for me.  I drove with Brad Schacht and Gareth Swanepoel up to Charlotte from Jacksonville.  This was a fun 5.5 hour drive as Gareth live tweeted our trip with the hashtag #sqlwolfpack.  We arrived Sunday night with just enough time to go register for the conference before registration closed for the night.

Monday

Monday Brian Knight and I delivered a full day precon on Growing up with Analysis Service: Power Pivot to MOLAP.  We had about 100 in the audience for an exciting day of learning.  We took the approach of telling a story of an Analyst that created a Power Pivot model but as requirements changed there was a need to find a more scalable solution through the other forms of Analysis Services.

Tuesday

This was a lighter day for me but I did stop by and visit the networking party hosted by Andy at Buffalo Wild Wings. I also spent much of this day setting up the Pragmatic Works’ booth prior to the opening of the event on Wednesday.

Wednesday

The opening of summit started with the keynote describing a hybrid approach to SQL Server (On Premise and Cloud).  I’m seeing more and more momentum Microsoft cloud.  A lot of the crowd that wasn’t buying into is years ago are now starting to see the benefits clearly now too so that’s helping. 

After lunch I delivered a presentation on Creating an End to End Power View Reporting Solution.  In this session I show what is needed to go from basic data source to full reporting solution using Power View.

After all the sessions were complete the Exhibiter receptions started and the Pragmatic Works’ booth hosted a book signing.

I also attended a Karaoke party hosted by Pragmatic Works that night with a live band.  You can check out about 200 pictures from that event here.

Thursday

On Thursday I presented a session with Dustin Ryan on Choosing the Right Analysis Services: Tabular vs MOLAP.  This was the first time I had ever presented with Dustin and I think it went really well.  I especially enjoyed how we worked off each other to make our points together.  Demos were of course planned but the banter was all thought of on the spot.

I also attended a couple sessions on Thursday.  One on Real Time Data Warehousing on PDW and then also the BI Power Hour.

Friday

I worked the Pragmatic Works booth much of this day and then went to the SQL Saturday Charlotte Speaker dinner at night. 

Saturday

I presented a session at SQL Saturday Charlotte on Power Query.  I had a lot of great questions in this session, which happens to be one of my favorite topics right now.  The event was incredibly organized.  I tip my hat to the organizers who obviously put a lot of work into getting the event together.

Understanding Microsoft Self-Service BI Recording and Q&A

I hope you were able to attend my free webinar on Understanding Microsoft Self-Service BI on September 26, 2013.  If you weren’t you can now download the recording here.

Because I covered new material all the way to the end of the webinar i thought I’d also answer some of the top questions I didn’t have time to answer here.

Q: Can an Excel Power Pivot 2013 model be read by Excel 2010 or Excel 2007? What about Excel versions and their compatibility with SharePoint Versions?  I ask because we are upgrading our SharePoint environment to 2013 but most of our users have Excel 2010 (a few have 2007).

You can open an Excel 2013 workbook that has a Power Pivot model in older versions but you cannot modify them.  You can however deploy Excel 2010 and Excel 2013 workbooks to you SharePoint 2013 environment and it they interact the same.

Q: Is there a way for me in us my own map?  ex. wards/districts in my county

I assume this one is referring to Power Map and the ability to map geopolitical regions.  It doesn’t currently have the ability to your own regions yet.

Q: Where do we download power map?

http://www.microsoft.com/en-us/download/details.aspx?id=38395

Q: Can you change to use google maps instead of bing maps?

No, Microsoft tool = Microsoft maps! :)

Q: Could I use relationships using more than one field?

No, Power Pivot only has the ability to create relationships on one field at a time.  If you need to join on more than one field, which in the real world is often, then you have to get creative.  In the quick example I showed we merged two columns together to make a single join column that could be used for our relationship.

Q: Is Power Query available for Excel 2010?

It sure is!  This is one of the great things about Power Query is that it is compatible with both Excel 2010 and 2013.

Q: How can you share Power View reports? Do you have to share the excel file or can you display in SharePoint?

You have a couple options.  You can share the excel file like you’ve suggested or you can deploy it to SharePoint.  If you develop the Power View reports in SharePoint you can also export via PowerPoint.

Q: Will these slides be available

Sure thing!  Download them here.

Q: The maps features is very interesting.  Can it do counties within a state .. and neighboring states?

Yes, Power Map has the ability to regionally map counties and zip codes as well.

Ohio Business Intelligence Road Trip 2013

imageNext month Mike Davis and myself will be making a epic road trip.  We will be flying into Columbus, OH to deliver a two day (7/16 – 7/17) workshop on advanced Reporting Services techniques.  We will then pack up and drive to Cleveland, OH for another two day (7/18-7/19) workshop on advance Integration Services techniques. 

Come join us for what should be a fun week!  I’ve already talked to a few folks that will be making the drive with us and attending both sessions.  Hope to see you there!

Columbus Master SSRS Workshop

Duration: 2 Days

Schedule: 7/16/2013 – 7/17/2013 8:30 AM – 5:00 PM

Price: $299

Location:

Microsoft Office
Polaris Center
800 Lyra Dr.
Suite 400
Columbus, OH 43240

Master Report Developers must hold unique traits to set themselves and their work apart from others. This two day class is designed to take you to the next level in your understanding of SQL Server Reporting Services (SSRS). We use a variety of lab exercises to ensure lecture sessions are backed up by hands on experience. With the hands-on labs, you will learn how to create complex reports that use Analysis Services as a data source and custom code assemblies. This course will cover all the advanced topics of Reporting Services like scaled-out deployment, .Net integration and monitoring Reporting Services usage.

Agenda

Cleveland Master SSIS Workshop

Duration: 2 Days

Schedule: 7/18/2013 – 7/19/2013 8:30 AM – 5:00 PM

Price: $299

Location:

Microsoft Office
Park Center III
6050 Oak Tree Blvd. S., Third Floor
Independence, OH 44131

During this 2 day advanced SSIS training course, you will learn how to use SSIS in your enterprise to solve common extract, transform and load (ETL) challenges. This class assumes you know the basics of SSIS and takes you from the novice level to a more effective, advanced ETL developer. Much of this class focuses on implementing common real-world SSIS patterns taught from the experts of SSIS. You’ll also learn how to performance tune SSIS. Be prepared for a very in-depth day of SSIS that gets advanced in many areas.

Agenda

Creating Real World PowerPivot Models Recording and Q&A

Thanks everyone for attending my free webinar on Creating Real World PowerPivot Models on June, 18, 2013.  If you were not able join for the live event you can now watch the recording here.

In this webinar we built out a live solution with the Pragmatic Works Marketing Director, Rachel, to prove PowerPivot is a very capable End User tool.  Using the marketing data she collects we built a Self-Service BI solution entirely in PowerPivot and finished with a quick Power View map report with Excel 2013.

This was part of an ongoing webinar series by Pragmatic Works offers every Tuesday and Thursday at 11:00 AM EST.

I’ve started to write follow up posts to these webinars because we always get great questions but there’s no way I could answer them all during the time allotted.  Here’s some of the questions I wasn’t able to get to and answers for them.

Q: Is it possible to add a table once you’ve already completed the first import?

Yes, fortunately it’s very easy to add new tables as requirements change into PowerPivot.  This is done the same way we demonstrated the initial import.

Q: If I create a hierarchy in the Date Dim, can it be re-used in my next project?

The only thing that is close to this is you can use one PowerPivot workbook as the data source in another PowerPivot workbook but unfortunately any hierarchies that are created will not carry over to the new model.  They would have to be recreated but luckily that is a very quick process with PowerPivot.

Q: I love it.  We have lots of SQL views we have created for reporting.  Do we really want the information consumer to define this or should we be using our views as our PowerPivot definition?

The information consumer or really a Power User would be the one to define all of what we showed during the webinar.  I say Power User because it requires a little bit of knowledge about table relationships.  Once that Power User defines the model he/she can share it will the information consumers for simply reporting on with either Excel PivotTables or Power View.

Q: How is the PowerPivot model you just created different from building a Tabular Model in SQL 2012 SSAS?

The way they are designed is very similar.  In fact, when you create a new Tabular model you can import the design from a PowerPivot workbook.  The big differences have to do with scalability.  PowerPivot relies on the resource of your machine or desktop to pull in and process data.  Tabular uses the resources of a server instance which hopefully is a beefier machine then your laptop.  Scalability of PowerPivot can also be done with PowerPivot for SharePoint.  Tabular also give you the ability to create partitions to help performance, apply row level security and query down to the underlying data source using DirectQuery.

Q: Is Power View possible on SharePoint 2010 and SQL Server 2012 and Office 2013 ?

Yes, there’s two ways you can do Power View now.  The easiest way is Excel 2013, which has Power View built into it and doesn’t require any additional install.  The other way to do Power View is SharePoint 2010 SP1 or higher (SharePoint 2013) and SQL Server 2012.

Again, thanks for joining me for the webinar and hope to see you in a future one!

Ad-hoc Reporting with SharePoint Recording and Q&A

I hope you were able to attend my free webinar on Ad-hoc Reporting with SharePoint on May 21, 2013.  If you weren’t you can now watch the recording here.

As usual there were many great questions that I wasn’t able to answer and I thought I’d follow up on those now.

Q: In the organization that I work in, SharePoint 2013 is stood up but is somewhat barren, just some team pages. Where would I be able to locate the ReportBuilder environment in a 2013 implementation? Is it apparent on a default 2013 installation? If not is it something I have to :activate” or have the architect activate?

First do the Reporting Services install detailed here.  http://msdn.microsoft.com/en-us/library/jj219068.aspx

Then add the SSRS content types to your library, which is detailed here. http://msdn.microsoft.com/en-us/library/bb326289.aspx

Q: Can we create linked reports in a SharePoint integrated Report Server?

No, unfortunately this is one of the few features that you can do with the Native Report Server but doesn’t carry over to SharePoint integrated servers.  Follow this path to find other features not supported http://msdn.microsoft.com/en-us/library/bb326290(v=sql.105).aspx.

Q: Can you schedule PowerPivot refreshed in SP 2010?  If so, can you recommend links to the information?

Yes, and sure thing!  http://msdn.microsoft.com/en-us/library/ee210595.aspx

Q: Once you have consumed that report part and updated it, can you undo the change and go back to the previous (old) report part?

Once you accept a Report Part change you cannot go back to the old version of that report part other than the ol’ Ctrl + Z.

Q: Is this a way to edit the Power View template that opens when you click on the Create New Power View report.  Can you change fonts or add a company logo that would always be available for users starting a new Power View report on your SharePoint site?

Interesting question.  There’s not really a way to create templates like you can with traditional SSRS in Visual Studio.  What you could do is create a Power View report and making it read only.  Then the user could do a ‘save as’ of the read only copy and create their own reports on top of the template.

Also, you can change font as well on the Style tab that I didn’t have time to show.

image

Q: What tool is he using for drawing?

Its a presenter’s best friend called ZoomIt.  You can download it free at www.sysinternals.com

Q: Can SharePoint not be set to refresh Excel workbooks that are not PowerPivot? You mentioned that was a PowerPivot feature, but what if the Excel points to SSAS directly and you want the data refreshed in the workbook that would be stored in SharePoint? We are just getting started with SharePoint and trying to understand how that works.

It can’t be scheduled like PowerPivot but you could auto refresh an Excel Services report by modifying the connection file used for the report to automatically refresh the data upon opening the file.  Unfortunately that would mean every time it’s opened it would have to rerun the query for the entire report.

Q: How do we get the SharePoint site and/or PowerPivot gallery to show up in the Excel Save As screen?

If you tell it to Save As to a location on your local machine and instead type in a SharePoint URL you can save to your PowerPivot Gallery.  After you do this once anytime you go to the Save As menu you will save a SharePoint listing available.

Q: Is it possible to adjust the PowerPivot refreshing rate within SharePoint to be more than once a day

Yes you can.  In the Data Refresh section you can create a schedule that’s similar to a SQL Agent job schedule which give you lots of flexibility on frequency.

Q: Does the end user need Excel 2013 to use Excel Services when it is loaded to SharePoint?

Love this question because this is a big selling point for latest changes to Excel Services.  The answer is No, as long as they can connect to SharePoint they can use the web interface for Excel Services without needing the client tools installed locally.

Q: Could you and would you use a Cube as a data source for PowerPivot or does that defeat the purpose of using Power Pivot?

Yes it is possible but if you’re considering this the right solution would probably be go back to the data warehouse as a data source rather than the cube.

Analysis Services Webinar Recording and Q&A

I hope you were able to attend my free webinar on Introduction to Analysis Services on April 2, 2013.  If you weren’t you can now download the recording here

Because I covered new material all the way to the end of the webinar i thought I’d also answer some of the top questions I didn’t have time to answer here.

Q: Is it true that not all versions of Excel 2013 support PoverPivot.  From what I read you need Office 2013 "Pro Plus".

Here is a great post by Rob Collie that answers this question http://www.powerpivotpro.com/2013/02/hey-who-moved-my-powerpivot-2013-cheese/.

Q: Does Analysis services has write back capability?

The only version that currently supports writeback is Multidimensional Analysis Services.

Q: Could you give a link to the VS 2012 update for theses tools to develop against sql 2012

Here’s the link to the download for Visual Studio 2012 SSDT  http://www.microsoft.com/en-us/download/details.aspx?id=36843

Q: Can Analysis Services Tabular be run within SSRS or do we need to use SharePoint?

This ones is kind of a two part answer.  Yes, you can use Analysis Services Tabular as a data source in an SSRS report and you can also use Reporting Services reports as a data source in PowerPivot and Tabular.

image

Q: We have a number of cubes that users access via Excel. They would like to be able to add simple custom calculations.  Can they add there own calculations when connected to a SSAS cube via power pivot?

Yes, a user can create their own calculations in PowerPivot call Calculated Measures using DAX.  This can be done against any data source including SSAS Multidimensional.

Q: Do relationships have to be defined in the source database?

No but it certainly helps.  If relationships are defined in the source they can usually (depending on the type of data source) be carried over to the model you create.  If there are no relationships then you must define them in your model.

Follow

Get every new post delivered to your Inbox.

Join 36 other followers