Month: September 2013

How to add additional Columns to an Existing Table in a Power Pivot Model?

Standard

Problem:

There’s a Power Pivot Model. It imports some number of columns from a table. Now, there’s a need to import an additional column to this table. How do you do that?

Solution:

Here are the steps:

1. Open Power Pivot Model.

2. Go to Design Tab.

3. Click on Table Properties:

Power Pivot Table Properties

4. From here, You can add additional columns:

Add Columns Table Power Pivot5. click on save and that should add the column to your model:

Power Pivot Edit Connections TableFAQ:

#1: I can’t see what you see inside Design Tab. What do I do?

Make sure you have the latest Power Pivot version. You can read this: http://parasdoshi.com/2013/06/04/excel-2010-how-to-check-the-installed-version-of-powerpivot-and-whats-the-latest-version/

#2: How about changing the data source & pointing to new cube/database? It’s very helpful when you switch between QA, Dev or PROD servers. Here are the steps:

you can do that too! Go to Design > Existing connections > Select Connection > Edit > Make changes > Test it! > Save > “Refresh”. Any Problems? No? Great. That’s Done!

Related Posts:
How to add a column in an imported Table while developing SQL Server 2012 Analysis services Tabular Model

Book Review: Confessions of a public speaker

Standard

Short version!

I like reading good books. This is one of those good books!

Why did I choose this book?

Public speaking is something I do every day. It’s part of my Job. It’s part of my daily life. So I had figured I’ll keep working on improving this skill. As a part of that, I decided to read “Confessions of a Public Speaker” by Scott Berkun

What did I like about it? Four things:

“There are two types of speakers: those that are nervous and those that are liars.”

– Real Life Examples. Lot’s of it!

– Actionable Insights & Tips (like how to work a tough room, keep Practicing, how to gather feedback, how to get audience’s attention etc!)

–  It’s an easy quick simple read!

– Funny!

Bottom-line:

if you think you public speaking is something you want to improve upon, I highly recommend this book!

Disclaimer:

I got a e-copy of the book via O’Reilly Media’s Blogger Review Program.

How to Track SSAS cube usage by End-User Tool?

Standard

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?

Problem:

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:

SSAS Cube Connection String Application Nameyou’ll have to modify ALL connection strings used to point to the cube though.

SSAS Cube SSRS Excel usage

Credit:

Erik Veerman

Resources:

Connection String Properties (Analysis Services)

 

Book Review: R in a Nutshell

Standard

R is a popular tool among data scientists because it’s just like a Swiss Army knife (or may be more!) for them!

R Language Data scientist swiss army knife tool

Analogy credit: Tapping the Data Deluge with R by Jeffrey Breen

Sometime back I worked on a research project that involved writing some R code – we were searching for tools ways to pull data from multiple social networks, perform text analysis and create effective data visualizations. R seemed like a great tool & so I was searching for a book/guides that teaches me fundamentals I needed to know to get few R related things done. One of the books that I used often during the research project was “R in nutshell”. I didn’t read it cover-to-cover but it was a great reference book for me. I used to read guides online/other-books and then I used to combine information from this book to get stuff done. The section I liked the most was on Data visualization which included some great code snippets to create effective data visualization using ggplot2 library. I used to take code snippets from this book & apply it on data-sets that I had.

text analysis sentiment

Fun stuff!

Also, I liked it that the book has some end-to-end examples that cover the entire life cycle of data analysis/statistical-analysis.

Summary:

I recommend this book as a “reference” for someone who started working with R.

Note:

I received a copy of this book as part of OREILLY’s Blogger program. Thanks OREILLY! If you are a blogger, you should check out that program!

Using Excel as BI Tool? Consider 64-Bit version.

Standard

Excel as Business Intelligence Tool. What do I mean?

For the purpose of this blog post, If you’re creating Excel based reports against Power Pivot Model, SQL Server Analysis Services data or creating reports by combining data from other data sources – then you are using Excel as a Business Intelligence Tool.

What’s the problem?

when you’re using Excel as a Front end tool for creating Business Intelligence reports – there’s a chance that you’ll be bringing in more data than excel could handle which would create “performance problems”.

How can 64-bit help?

Performance power offered by Excel 64-bit > 32-bit version.

Why? Because of the advantages that 64-bit computing has over 32-bit computing

How can YOU decide whether you need 64-bit version? (FOR BI FOLKS ONLY)

(prerequisite: your OS should be 64-bit)

If you don’t have Excel & planning to get it. Consider 64-bit!

If you already have Excel & it’s 32-bit – Do you see slow performance when you create your reports against Power Pivot model or SSAS? Do you have more than 4 GB RAM in your machine? YES? Try 64-bit.

What’s the down-side of 64-bit?

There are compatibility issues with 32-bit Excel add-ins. Read more here: Choose the 32-bit or 64-bit version of Office

Microsoft does not recommend using 64-bit excel.

What version of Excel support 64-bit?

Excel 2010 & onwards.

Case Study

In my current project, a Business Analyst who was creating reports against a SSAS (SQL Server Analysis services) cube & was seeing 1-2 min delays in “data refresh” each time filter values used to change. Clearly, She was not happy!

I looked at her computer configuration. She was using:

  • Excel 2010 32 bit
  • 8 GB RAM
  • 64 Bit edition of Windows 7

so I recommended 64-bit version of Excel. After the version was upgraded, we tried again! This decreased the time delays by 25x. The data was now getting refreshed in 2-4 seconds!