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.

Consuming News Through Social Media and Read It Later With Pocket

These days the fastest way, not always the most accurate, to get news is through social media.  Technology, sports, politics, and breaking news will always be found first on social media outlets like Twitter.

For example, I’m a huge fan of American Football or most known as the NFL.  Every year the NFL teams draft players from college teams to play professionally. This is done through a big televised spectacle called the NFL Draft and is held across multiple days once a year generally in April.  The highly anticipated moment is always when the commissioner walks up to the podium and announces which player each team has picked.  Well that moment that is so exciting to watch (most casual fans would not call the draft exciting) on live TV is now very anticlimactic because of Twitter and other social media outlets that told me 5 minutes earlier which player was picked.

It sounds like I’m complaining right?  No, in actuality I love it and embrace it.  Obviously if i didn’t want to know this kind of information early I wouldn’t look at Twitter during the live draft.  Can i guarantee this information is accurate?  Absolutely not, there’s not any news outlets that can make that guarantee.  Sports news is one thing, if I make a mistake repeating what I read through social media it isn’t a big deal (because my career is not in sports) but technology, world news, politics, etc… you should always consider the source of the information and do your own research.  Don’t blindly believe everything you read through social media.  I think most people understand this but those that are new to consuming news through social media will learn it fast.

So you get my point, social media is great a providing news extremely fast.  Now if you’re like me you’re probably an extremely busy person.  I keep a very busy schedule at work and with three young children at home time is limited to catch up on the latest news.  That’s why Twitter is my favorite social media outlet.  Tweets are short, usually easy to read/search and with many of the mobile apps available I can quickly read them from my smart phone of choice.

My smart phone is generally how I consume Twitter because like I mentioned I have such a busy work schedule I don’t have time to keep a constant distraction up on my screen while working.  Nothing against you if you do this, you’re probably just better at multitasking than I am.  With Twitter post only allowing 140 characters my phone is fine for reading content, but 140 characters isn’t long enough to read the full story is it?  I certainly don’t think so and that’s why tweets that have more information often will include a link to a full article or blog.  This is great but I don’t want to use my phone to read a two page story.

That’s why for about  9 months now I’ve been using a a free online tool called Pocket(formally Read It Later) to read entire stories at my convenience.  Basically Pocket let’s you archive any Twitter post that has a link so you can read at a later time.  This has definitely saved me because often I’ve seen a tweet about a new piece of technology and if I didn’t have this archiving tool I would probably forget to go back and read about it later.  The nice thing is it’s not limited to Twitter.  For example, some web browsers have a Pocket add-in that will allow you to archive web pages as you surf the web.

I also like really like the output.  It almost has the look and feel of live tiles that you can scroll through to catch up on your stories.

I’m not much for endorsing products but I thought I would share this one sense it has allowed me to keep up with the details of news rather than just the headlines.  If you’re interested in giving Pocket a try visit them here http://getpocket.com/.

Getting Started with Microsoft Data Explorer

What is Data Explorer

Data Explorer simplifies the data discovery phase for Excel users that are creating self-service Business Intelligence solutions.  It does this by provided straightforward methods for connecting to data previously unheard of, without a developer, in Excel.  It also provides a basic ETL tool for those involved in self-service BI projects all within Excel.

What do I need

Currently Data Explorer is only available as a preview and works with Excel 2010 SP1 or Excel 2013.  You can download the Data Explorer preview from http://www.microsoft.com/en-us/download/details.aspx?id=36803.

Enabling the Add-in

Once you download and install the add-in you will have to enable it by going to File –> Options –> Add-Ins.  Then Select COM Add-ins from the Manage dropdown and click Go

Check off Microsoft “Data Explorer” Preview for Excel from the Add-Ins available list then click OK.

Once you have enabled the add-in the DATA EXPLORER tab will appear in the increasingly crowded Office ribbon.

Let’s take a look at what this new add-in has given us.

What does it do

A very detailed list of each element of Data Explorer can be found here http://office.microsoft.com/en-us/excel-help/start-page-HA104003813.aspx.

In this post I’ll walk you an example that i think all companies are starting to take a lot more seriously, which is social media sentiment.  In other words how does the public perceive our company.

With the built-in ability to import data from Facebook Data Explorer can very easily analyze things like statuses, likes. comments, and much more.  Let’s walk through an example:

1. Launch Excel and ensure the Add-in is enables with the steps detailed above.
2. Select the Data Explorer tab and choose From Facebook from the From Other Sources dropdown selection.
3. You will then be prompted to provide a UserName or object ID.  The default is “me”, which means it will allow you to import data from your personal Facebook account.  However, if you’re an administrator of a corporate Facebook page you could enter that page in here.  For example, I am an admin on the Pragmatic Works page.  So if change the default “me” to PragmaticWorks and set the Connection name to Posts I can see all posts on our corporate page.  Click Apply.
4. Now the true data exploration can begin.  My first step was to hide all the columns I don’t care about.  You can select multiple column headers at once and then right-click to select Hide Columns.
5. Now that we’ve got just the data we care about let’s analyze things like how many likes and comments we’re receiving on our posts.  You will notice on the columns for both comments and likes that the word Link is displayed.  This means there’s more data in a separate object that can be imported.
6. If you click the word Link it will preview that data in that object as shown below when i clicked on likes.  From this I can tell there were 6 likes on this particular post.  There’s also another option if i click Table that will allow be to see the actual users that liked my corporate post.
7. This is great for exploring but if i actually want to add this data to my query then I would clear my likes search on the Steps page as shown below.
8. For my Marketing team’s analysis they really want to know a count of how many likes and comments we had on each status.  To do this I will navigate back to the likes column in my query and click the Expand button to check off new values i want to return.  For this example i just need the count of likes but if I wanted to see who actually made the like on our post I could return the data.
9. We have the data we want now so hit Done and all the Facebook sentiment data will be imported into Excel.
10. Now that this data is in Excel we can create a PowerPivot workbook on it or even Power View report that looks something like this:

Having the ability to create these kinds of report in a very short amount of time is exactly what our Marketing department needs to analysis our true reach.

Excel 2013 Inquire Add-in

What is Inquire

The Inquire Add-in for Excel 2013 helps you analyze and review your workbooks for things like relationships, invalid formulas, and to compare .xlsx files for differences.  Let’s take some time to review how to enable the add-in and then how to use it.

Enabling the Add-in

If you are using Office Professional Plus, the Inquire add-in comes installed with Excel you simply need to enable it.

The Inquire add-in is already installed with Excel 2013 you simply need to enable it by going to File –> Options –> Add-Ins.  Then Select COM Add-ins from the Manage dropdown and click Go

Check off Inquire from the Add-Ins available list then click OK.

Once you have enabled the add-in the INQUIRE tab will appear in the increasingly crowded Office ribbon.

Let’s take a look at what this new add-in has given us.

What does it do?

Report

Workbook Analysis

The Workbook Analysis tool is used for analyzing details of a workbook for reporting on the things like the workbook structure, formulas, cells, ranges, and warnings.

One odd thing here that a lot of people notice a workbook structure property called “Very hidden sheets”.  This is simple another way of hiding sheets through VBA.  See here for more details on how to do a Very hidden sheet.  http://j-walk.com/ss/excel/usertips/tip036.htm

Diagram

Workbook Relationship

The Workbook Relationship tool shows links between multiple workbooks that you may have.  This can be especially helpful when you have dozens of cell references to an external workbook.  Trying to untangle that mess can be very difficult and this tool visualizes those dependencies.

Worksheet Relationship

The Worksheet Relationship tool works exactly the same as the Workbook Relationship but now visualizing dependencies at the sheet level.

Cell Relationship

Again the Cell Relationship tool is very similar but it only shows relationships of cells that you have highlighted prior to click on the command.  These are often dependencies are often displayed because a cell is referenced in a formula.

Compare

Compare Files

The Compare Files command lets you see the differences, cell by cell, between two workbooks. You need to have two workbooks open in Excel to run this command.

Any differences that the compare tool finds color coded by content type in a grid view.  Here’s an example output:

Miscellaneous

Clean Excess Cell Formatting

If you’ve developed an Excel workbook that’s gotten out hand with how large it is and how much logic is built in use the Clean Excess Cell Formatting tool to remove extra formatting on cells that you may not be aware of.

This can actually help increase the performance and file size of workbook that have become bloated with this kind of leftover formatting.  You can test it out by putting a background color on a couple irrelevant cells and then run the tool.

Workbook Passwords

If you are using the Inquire add-in for analyzing workbooks that are password protected you must add the workbook password to the Password Manager.

Help

This is the standard Excel Help that will walk you through what the add-in does.

I’ve found the Inquire add-in has a couple nice features that i can definitely see myself using in our increasingly Excel centric world we live in.

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.

New Book: Knight’s SSIS 2012 24-Hour Trainer

I’m very excited to announce that you can now purchase my latest release book: Knight’s Microsoft SQL Server 2012 Integration Services 24-Hour Trainer. The 24-Hour series of books offered by Wrox is a great way to get kick started into learning a new tool. Not only are the lessons very focused, making it easier to learn something new, but you also receive a DVD that accompanies the book. The DVD walks you through each of the lessons with the author showing you an example of solving a problem using the tool. You can get your copy of my new SSIS book here!