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.

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.

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.

SQL Server Analysis Services Webinar

Join me tomorrow, April 2. 2013, at 11:00 PM EST for a free webinar which is part of an entire month of Back to Basics themed free training.  This webinar is for anyone trying to understand the basics behind Analysis Services and how to get started design a cube.  With the release of SQL Server 2012 there are now two forms of Analysis Services so we’ll also spend time discussing Multidimensional and Tabular and why you would choose one over the other.

Also, during this webinar I reference a Decision Matrix file to help you with making the decision on which Analysis Services is right for you.  You can find that file here.  If you would like the PowerPoint slides for this session you can download them here.

Register for the webinar here and if you won’t be able to make it at this time all of the webinars are recorded and can be watched at a later date by using the same link.

Presenting at PASS BA Conference

I’m excited to announce that I will be presenting in Chicago for the PASS Business Analytics conference in April.  This is a really exciting community-oriented gathering for business analytics professionals.  I will be presenting on creating an End to End Power View solution.  I hope you can join me by registering here!

Creating an End-to-End Power View Reporting Solution

Speaker(s):  Devin Knight

Duration: 60 minutes

Track: Data Analytics and Visualization

Power View lets you create eye-popping visualizations and provides intuitive ad-hoc reporting that a variety of business users can use to make critical decisions. But before you can begin building these incredible reports, you must have your data properly prepared. This session will give you a beginning-to-end view of what is needed to create Power View reports, from organizing a PowerPivot model source to preparing the actual Power View report.

PASS Summit 2012 Recordings Available

Did you attend PASS Summit and miss out on some sessions because there were so many or you were busy networking?  Good news!  The recordings are now available for free to watch streaming for all those who attended the conference now here.

If you weren’t an attendee you can still watch the sessions for a small price here.  Enjoy the great content!

PASS Summit 2012 Review

Life is finally returning to normal after a great week in Seattle for PASS Summit 2012.  The entire year is basically other smaller events (still great events!) that occur to pass the time until Summit arrives.  Like Ted Kummert said in the keynote on Wednesday PASS Summit is like a big family reunion. 

Here’s a summary of how my week went:

Friday

I did my duty of Early Voting in Florida (I didn’t know then it would take Florida 5 days to announce it’s winner) before taking for an afternoon flight to Seattle.  After landing in Seattle I drove with Bradley Schacht to meet several other Pragmatic Works employees for our companies annual hike up Mt. Rainier.

Saturday

We woke up early Saturday morning and began our hike up Mt Rainier.  The weather was less than ideal for our plans, but we wanted to see how close we could make it to our goal of Camp Muir, which was 4.1 miles up from our starting point.  Unfortunately, we did not reach our goal because of the weather but it was a blast anyways. Hopefully next time I’m in Seattle we can go for it again.

You can tell this photo was taken pretty early on because I’m still smiling and not wearing my hood yet.

Sunday

We took our time leaving the cabin by Mt. Rainier and drove to Bellevue for a visit to the Microsoft store where a couple of the Pragmatic Works guys bought the new Surface.  Then we all returned our rental cars and made the light rail trip back into Seattle.  After checking into our hotel we all ran up to the convention center and registered ourselves.  I stayed with Brian this night to prepare for our precon the next day, while most of the others went to the penthouse they rented for the week, I would join them later. 

Here’s a view from the penthouse.

Monday

Monday was the first day for Pre-Conference seminar, Brian and I were delivering ours on Implementing a BI Solution using SQL Server 2012.  There was a lot of material to cover but we managed to do it and I think everyone really enjoyed the seminar.  We presented using election data which was very topical seeing that the presidential election was on Tuesday. 

Picture of our packed house during the seminar.

Tuesday

Tuesday I was free to be irresponsible so I woke up late (7:00 AM) and took my time heading to the Boeing facility in Everett, WA with Bradley Schacht and Chris Price.  No picture taking allowed at the Boeing Factory, but I did get some nice souvenirs for my kids here.

Wednesday

Wednesday I had a very busy day.  I went to the keynote, presented my session, worked our booth during the exhibitor expo and partied hard at the Hard Rock karaoke event.

The keynote had many great announcements including one I’ve been waiting to hear about: Multidimensional cubes working with Power View.  There were other big announcements like Hekaton and Polybase.

After lunch I presenting my sessions on Adapting your ETL Solution to use SSIS 2012.  I think the session went great.  I had a lot of great questions that kept me pretty occupied after the session was over.

At night was the Exhibitor Expo where the Pragmatic Works booth held a book signing and gave away passes to our SQL Karaoke party that night.  The Karaoke event at the Hard Rock Cafe was amazing with a live band and plenty of space to just chat and hang out if Karaoke isn’t your thing.

Here’s the line of people waiting to get a signed book.

SQL Karaoke at Hard Rock Cafe with a live band.

Thursday

This was my last day of presenting sessions.  Unfortunately, I was the last session of the day and others had presented similar topics several times throughout the week so I only has about 30 or 40 attend.  The session was on creating a Power View solution, where I showed how to create a model and Power View report as an end to end solution. 

Friday

Friday I worked the Pragmatic Works booth all morning and headed down to Pike’s Place Market for some last minute sight seeing before our red-eye flight back. 

It was a great week and I can’t wait for the BI Conference in Chicago.

Building Dynamic SSRS Reports with Analysis Services Recording and Code

Thanks to all who attended my webinar last Thursday on Building Dynamic Cube Reports.  You can find my code

Recording:

http://pragmaticworks.com/LearningCenter/FreeTrainingWebinars/WebinarDetails.aspx?ResourceId=470

Code:

http://sdrv.ms/RqxbcF

There are several reports in here that step you through the changes I made throughout the webinar.

Also, look for my upcoming SSRS Master Classes that will take you Reporting Services skills to the next level!

Upcoming Webinar: Building Dynamic SSRS Reports with Analysis Services

Join me next week for a really fun presentation on building Dynamic SSRS reports using Analysis Services cubes as a data source.  This webinar will demonstrate how you can use one of the underutilized features of Analysis Services called Dynamic Management Views.

Session details:

Designing Dynamic SSRS Reports using an Analysis Services Data Source

Instructor: Devin Knight

Date/Time: 10/25/2012 11:00 AM

Creating Reporting Services reports that use Analysis Services as a data source can frustrating. You designed a cube so it can be more flexible for users but Reporting Services is static by nature. This session will walk you through building dynamic datasets that will allow users to change the fields represented in a SSRS report. No longer will Reporting Services be a static reporting tool because your users will have the flexibility to change report metadata dynamically using parameters and Dynamic Management Views (DMV).

24 Hours of PASS Recordings Available

If you missed any of the great 24 hour of PASS sessions from September you can now watch the recordings. 

http://www.sqlpass.org/UserLogin.aspx?returnurl=%2fLearningCenter%2fSessionRecordings%2f24HoursFall2012.aspx

Details on my session can be found below:

Session 01 – Choosing the Right Reporting Platform
Presenters: Brian Knight, Devin Knight
Download presentation slides (PDF)

Why do you attend sessions? – Survey Results

Several weeks ago I posted a quick survey on my blog that asked how you choose a session to attend when you have so many options at conferences.  Thought I’d share the results of the survey today. 

I don’t think there’s anything surprising about it.  Most people overwhelmingly responded that the number one reason they choose to attend a session is simply because they needed to learn the topic.