Ms SQL Girl

PowerPivot 2013 Error: Data Model Damaged

Mar 14th, 2013 7 Comments

I have been using PowerPivot in Excel 2013 and SQL Server 2012 SP1 Tabular Model side by side for over 3 months now. Recently I have just encountered the following error when creating new Excel 2013 workbook from scratch and adding a table into the Data Model.

We couldn’t load the Data Model. This may be because the Data Model in the workbook is damaged.

PowerPivot is unable to load the Data Model.

It seems that I am not the only who is having the issue. Chris Webb (b | t) mentioned to me that he also has encountered it in the past. Others have mentioned that it’s been working for them without any issue.

I have recently logged the issue on Connect. Please vote if you encounter the same issue.

To summarize, the environment details I have this happened are:

Operating System: Windows 8 Pro (6.2.9200) – 64-bit

SQL Server 2012 SP1 (11.0.3128) – 64-bit

Office Professional Pro 2013: Microsoft Excel 2013 15.0.4454.1503 MSO (15.0.4454.1504) 64-bit

The workaround is simply to turn off SQL Server Analysis Services (Tabular) services. This should allow users to add data model to PowerPivot again. Although the workaround is simple and works, it becomes cumbersome for us who needs to use both PowerPivot and Tabular Model at the same time.

Hopefully this will be fixed soon.

Wrap Up

Having PowerPivot (Excel 2013) and SQL Server Analysis Services Tabular Model instance installed on the same machine may result in an error when opening PowerPivot. The error “We couldn’t load the Data Model. This may be because the Data Model in the workbook is damaged.” appears even though the data model is not damaged (or not even created yet). The work around is to stop the Tabular Model instance. The issue has been logged on Connect.

Editing Published SSIS Package in SQL Server 2012

Mar 6th, 2013 Leave a comment

So, you have inherited a set of SQL Server 2012 Integration Services projects that have been deployed on a server. Your company do not use source control so you can’t easily see what was last published (Oh no!!). Or, you have deployed something on the server but you are not sure if it is the right version. Bottom line is you want to inspect or to edit a package that is already published on the server. Unfortunately at the moment there is no way of just quickly downloading that single SSIS package.

SQL Server 2012 Integration Services now is now making use of Project Deployment Model. For more information, read it here. Back to the issue at hand, if you want to get a copy of what is in the production, in summary, there are two “easy” ways of doing so.

A. Creating a new project in SQL Server Data Tools (SSDT) and import the project from existing catalog.

B. Exporting the project into ispac file via SQL Server Management Studio (SSMS).

Caveat: Both these options require access to the project on the server.

Option A: New Project

1. In the New Project dialog box of SSDT, browse to Installed Templates > Business Intelligence > Integration Services on the left navigation pane.

2. Choose Integration Service Import Project Wizard option, enter the Name, Location and Solution Name accordingly. Then click OK to proceed to the next step.

3. Click Next on the Integration Services Import Project Wizard window

4. On the Select Source dialog, choose Integration Services Catalog. Then provide the details of the project on the server that you wish to import.

5. Follow the rest of the instruction.

6. Once the project is created, you can browse to the package(s) that you wish to inspect. In my example here, I have 4 connection manager files and 9 dtsx files.

This option is one of the safest / simplistic ways of getting SSIS project from the server. All the components in the Project, such as Project Variables and Project Connection Managers are kept in tact.

Option B: The shortcut

1. Navigate to the SSIS project on SSMS; i.e. [Server Name] > Integration Services Catalogs > SSISDB > [Project Folder] > Projects > [Project Name] as shown on the picture below.

2. Right click on the [Project Name], then choose Export….

3. Choose the destination folder on “Save As” to save the .ispac file

4. Navigate to the folder where the file has been saved and rename the file extension to .zip file.

5. Browse the zip file and you should be able to see the .dtsx file(s). Please note that in this example, I have 4 connection manager files and 9 dtsx files.

6. Now you can extract the desired .dtsx file(s) from the zip file.

Note: If the server is in Production, you could ask your DBA to export the .ispac file of the project (Step 1 & 2). Then you can continue with Step 3.

Special thanks to Josh Fennessy (b | t), for providing Option B.

Would it work for SSDT Visual Studio 2012?

Update – 6 March 2013: Both options work for Visual Studio 2012 too. (Read Microsoft announcement on SQL Server Data Tools – Business Intelligence for Visual Studio 2012). At the time of writing (a few minutes of exploring SSDT Visual Studio 2012), everything works the same for Option A. There is a major face-lift with monochrome look.

Wrap Up

Integration Services in SQL Server 2012 introduces Project Deployment Model. This means that accessing a single SSIS package that has been published from the server is not as trivial. This article describes two ways of accessing the desired package(s) or the project as a whole from a server. Option A, which is importing the package from the Integration Services Catalogs is the safest way. Option B, is a shortcut method that is recommended only for advanced level users.

This article does not describe how the pros and cons of the new Integration services Project Deployment Model as it is covered in a couple of blogs as listed on the Further Reading section.

SQL Saturday 198 – Calling SQL, Data and Snow Enthusiasts

Feb 8th, 2013 Leave a comment

There are 42 amazing sessions lined up for SQL Saturday 198. There will be a lot of speakers and attendees from different continents, America, Europe as well Australia! So, if you are a SQL Server professional or Data is your bread and butter, this is the event you would not want to miss this month! Register and build your schedule now.

If that doesn’t entice you, SQL Saturday 198 organizers sure know how to throw a great event. They are also organizing a ski trip on Friday, Feb 15, 2013. Check it out at Scott Stauffer’s website.

Josh Fennessy and I are providing you tips and tricks in “The Essential 8: Narrative Reporting Techniques“. This will be a preview to our PASS Business Analytics Conference in April 2013.

I am also honored to showcase “Power View for Geographical Analysis” next week at SQL Saturday 198. A sneak-peek interview video podcast, titled “The Power of Power View in Excel 2013″, with Karen Foster (Director | Microsoft) and me is available from Microsoft Technet Radio site.

A Lightning Talk demonstrating an upcoming GeoFlow add-in for Excel 2013 is also in the schedule. Here’s a little teaser from me:

And last but not least,, SQL Saturday 198 is fantastic way of networking with other passionate SQL Server and Data Professionals at both local and international level.

Hope to see you in beautiful Vancouver next week!

Malibu SQL Server User Group

Feb 5th, 2013 Leave a comment

SQL Community in Malibu!

Great news for new and experienced SQL Server professionals! Malibu SQL Server User Group will be having our first meeting in March 2013. We are currently finalizing the event and would be announcing the speaker / presentation details.

Please visit our website on: http://sqlmalibu.sqlpass.org

Our initiative survey is also available for you to fill in.

Join SQL Malibu

By joining, you can get an up to date info about our User Group meetings. If you are not a PASS member, you can register first.

Once you have registered or if you are an existing PASS member, you can add us to your chapter membership through the dashboard.

Simply type “Malibu” on [Filter by Keyword] field to find us.

Speakers wanted!

If you are interested in speaking or have any questions about the Malibu SQL Server User Group, please contact me on sqlmalibu at outlook dot com

Are you new and wanting to test what it feels like to be a speaker? Give me a shout, I have plenty of ideas to ease you in to speaking at our friendly Malibu SQL Server User Group!

Hope to see you there in March!

24HOP Back Again With Business Analytics

Jan 29th, 2013 Leave a comment

In 12 hours back to back live sessions and 12 hours on-demand reply, industry experts will take to the virtual stage to deliver a series of one-hour webcasts focused on data analytics and visualization, big data innovations and integration, information delivery, advanced analytics, and much more! This 24 Hours of PASS event is a preview of PASS Business Analytics Conference to be held in April later this year.

Starting at 1:00 PM GMT / 8:00 AM EST / 5:00 AM PST on Jan 30th 2013 (or midnight on Jan 31st, 2013 Sydney), the live sessions are listed below:

January 30th, 2013 13:00 GMT
Session 01 Live: PowerPivot for Excel 2013 in Action
Presenter: Alberto Ferrari

January 30th, 2013 14:00 GMT
Session 02 Live: Putting the Business into Business Analytics
Presenter: Jessica Moss

January 30th, 2013 15:00 GMT
Session 03 Live: Big Data on the Microsoft Platform
Presenter: Andrew Brust

January 30th, 2013 16:00 GMT
Session 04 Live: Why? Where? Spatial Analysis in Excel 2013
Presenter: Peter Myers

January 30th, 2013 17:00 GMT
Session 05 Live: Finding Rare Needles in the Haystack – Predictive Modeling with Unbalanced Data
Presenter: Paul Bradley

January 30th, 2013 18:00 GMT
Session 06 Live: Ensuring Compliance of Patient Data with Big Data and BI
Presenter: Denny Lee

January 30th, 2013 19:00 GMT
Session 07 Live: Dashboard Design Dos and Don’ts
Presenter: Stacia Misner

January 30th, 2013 20:00 GMT
Session 08 Live: What Is Big Data?
Presenter: Mark Whitehorn

January 30th, 2013 21:00 GMT
Session 09 Live: Marketing Forecasting with Excel
Presenter: Wayne Winston

January 30th, 2013 22:00 GMT
Session 10 Live: Visualizing Data with Power View
Presenter: Sean Boon

January 30th, 2013 23:00 GMT
Session 11 Live: Analyzing Data with Power View
Presenter: Jen Stirrup

Date/Time: January 31st, 2013 00:00 GMT
Session 12 Live: From Data to Insight – Views from Microsoft Finance
Presenter: Marc Reguera

I am very excited to host not one, but two 24 Hours of PASS Sessions tomorrow. The sessions I’ll be hosting are two of my favourite topics, Putting the Business into Business Analytics (Jessica Moss) and Why? Where? Spatial Analysis in Excel 2013 (Peter Myers).

See you there!

Starting 2013 with BI Talks

Jan 23rd, 2013 Leave a comment

After the great PASS Summit 2012 finished, I have been counting down to the next SQL community events. Partly because I have learning withdrawals, and mostly because I miss my SQL Family!

Luckily, there are two SQL Saturdays scheduled near* my area. One of them is SQL Saturday #198 in Vancouver, Canada (16 Feb 2013) and another one is SQL Saturday #177 in Silicon Valley, US (23 Feb 2013). What I love the most about SQL Saturday is that it’s the best bang for your buck! If you look at the schedule for SQL Saturday #198 (Vancouver) here, you can immediately recognize many of the speakers are MVPs! Same goes to SQL Saturday #177 (Silicon Valley), the Partial List of Speakers shows that you will be in for a treat.

Also, if you are in the Business Analytics arena, the PASS BA Conference 2013 in Chicago is surely not to be missed. It’s a one stop shop for business analysts, data scientists, data architect and BA/BI practitioners. Not convinced yet? Twelve of these talks are going to be previewed for free on the upcoming 24 hours of PASS. So check them out!

I am very delighted to also announce that I will be speaking at all those three events with the following topics:

1. Power View for Geographical Analysis for SQL Saturday #198.

2. The Essential 8: Narrative Reporting Techniques, with an expert BI architect and a friend, Josh Fennessy, for both SQL Saturday 198 and PASS BA Conference.

3. Analytic Cartography: Master Geospatial Reporting for SQL Saturday #177.

That’s my way of starting 2013!

Hope to see you, SQL Family, in some (or all!) of these events.

Standing Firm – Story of a WIT

Jan 8th, 2013 10 Comments

This is my first blog for T-SQL Tuesday. The theme for this month is Standing-Firm, which could be a story relating to one of these words: resolve, resolution, or resolute. I picked resolute.

My story is in relation to Women In IT/Engineering. Looking back, the first woman in Engineering, I have known, is my mum. Growing up, I was aware about this but never really gave it the proper recognition it deserved. Today I would like to do that.

I was born and grew up in the tropical country of Indonesia, where, as a child I learned so much about the limitations and boundaries that females could face. I became interested in data, specifically business data, in my early teens as my parents owned a business supplying standard and custom nuts and bolts. While their business was small it did well enough to keep all four children fed and educated.

One of my role models and inspirations is my mum. For 45 years, she has managed the day-to-day goings on within the workshop, negotiating deals, organising stocks and giving advise to engineers. Her understanding did not come from school (as she only finished year 8), but from practical experience that she learned from my dad and peers. There were no associations in Indonesia designed to help women in Engineering or IT. She came from a poor family, with little education, but she did it all. She raised four children and embedded these principles when we were growing up: passion, determination and helping others.

My mum has lived a very interesting and admirable life. For just over 14 years while I was still living under the same roof as my parents, my mum always said that “Our gender should never inhibit us from pursuing our dreams”. I held on to that, moved out of home and left Indonesia to live in Australia so I could pursue studying IT in high school and university. While studying IT, it was not uncommon that I would be the only girl, or the only one from a non-English speaking background. This didn’t quiver my passion for or my determination to excel in IT. Even with entering IT at a professional level, it is still not uncommon to be the only female in my area. I have become so used to this situation that I don’t see gender difference as an issue.

Map of places I have lived in

In 2012, I was determined that I wanted to learn more and to help others, by blogging and speaking at community events. I was nervous, not because I was a female, because I considered myself inexperienced compared to some others. Even so, if my blog posts or presentations could help even one person, I would be happy. My determination to help others has helped me to overcome my nervousness and learnt more about SQL Server. PASS and #SQLFamily have also been very supportive in this.

This year, I want to become a more confident speaker and a more knowledgeable SQL professional who helps and learns from #SQLFamily. I also want to be involved more with Women in IT activities via PASS and on personal level. My Women in IT/Engineering inspiration started with my mum at a young age, and I would like to help other females to pursue their professional dreams. We should be able to show our abilities within the workplace and still embrace our female nature (for me this would include my inability to open jars, but that’s why jar opener was invented, right?). We are all unique, female or male.

This post is dedicated to my mum.

“Our gender should never inhibit us from pursuing our dreams” – Paulin Koesmarno

Wrap Up

Thank you to Jason Brimhall (b | t) for hosting TSQL Tuesday – Standing Firm this month. The original invitation is available here. The rules for participation are in the invite. You can also read others’ submissions for the theme this month.

If you are a PASS Member and interested in Women in IT events and activities, please check our website here.

Power View for Geographical Analysis with Excel 2013

Nov 26th, 2012 Leave a comment

Earlier this month, I have presented “Power View for Geographical Analysis” as part of my Pacific Northwest US tour. My apologies for posting this fairly late in the month, following the presentations, as I have a few other commitments earlier this month.

Without further ado, below are the sample files:

1. Earthquake Last 30 Days

Download Excel 2013 file.

The data is retrieved from the USGS website. This particular workbook was manually prepared (i.e. data copied and pasted from the webstite). The workbook has been cleansed and manipulated using Excel flash fill functionality and contains from 11 September 2012 to 11 October 2012.

The workbook does not retrieve the data from the USGS website automatically.

Earthquake Analysis - About 1000 miles radius of Portland

Earthquake Analysis - by the hour

2. PASS Chapter and SQL Saturday Growth

Download Excel 2013 file.

Taking the data from 2 different sources, Dan English’s SQL Saturday workbook and Karla Lundrum’s report from PASS HQ, this demo shows how to combine them together.

There is also a section here that shows implications of incomplete Location hierarchy. In this example, Australia has complete Country – StateProv – City definition, whereby Singapore is missing StateProv information.

PASS Chapter and SQL Saturday Growth

Data Integrity - Incomplete StateProv Level

3. Internet Sales by Customer Occupation

Download Excel 2013 file.

A simple Adventure Works sample which shows well structured data model with good quality data. In this case we analyse Internet Sales by an attribute that doesn’t directly link to the sales, such as Customer Occupation.

Internet Sales by Customer Occupation

Wrap up

These sample files contain static data – i.e they do not retrieve data from data sources auotomatically. Hence the data is as at the time of preparation. The aim of the sample files is to provide an overview of Geographical Analysis techniques using Power View in Excel 2013. As I have mentioned in my sessions, having good data model and data quality is important to build good interactive analysis using Power View. However, in real life, we may be far from a perfect data quality as shown in the Earthquake example and the PASS Growth example. However, with creativity we can generate something meaningful for analysis.

Hope you enjoy the sample files.

Note: the workbook should work with Office 2013 Preview, if it does not, please let me know.

My version of Excel 2013 is: Microsoft Excel 2013 (15.0.4420.1017) 64-bit; Part of Microsoft Office Professional Plus 2013.

Measure Selection Using Slicers – Excel 2013

Nov 22nd, 2012 Leave a comment

I enjoy reading Jason Thomas’ blog, especially on SSRS. He has very good techniques that he describes very well.

In his latest post, Measure Selection Using Slicers, using Excel 2010 he explores how you can allow measure selection using slicers, which I think is really great. I have used the technique multiple times in Tabular Model.

The Excel 2013 version of his is here, in case you are thinking of testing it out on Excel 2013. I upgraded the PowerPivot first when I opened the original workbook in Excel 2013. Below is a reusable set of steps that you can use to upgrade an Excel 2010 workbook with PowerPivot to Excel 2013.

Caveat: upgrading from Excel 2010 to Excel 2013 may be problematic Read Jason Thomas discussion here. This discussion is related to having different Excel table names to the PowerPivot table names.

Upgrading Excel 2010 workbook with PowerPivot to Excel 2013

1. Open the workbook. in Excel 2013. Ensure you click on “Enable Content”

If you click on one of the slicers, you may get an error similar to the following.

“The workbook has a PowerPivot data model created using a previous version of the PowerPivot add-in. You’ll need to upgrade this data model with PowerPivot for Excel 2013″.

2. Click on PowerPivot tab, and choose Manage

You will get a warning similar to when you click on a slicer. Clicking OK will open up a dialog box to upgrade.

3. Click OK on the PowerPivot upgrade option.

4. Once PowerPivot is finished upgrading, a dialog box will appear to notify you that the workbook is still running in compatibility mode. Choose Yes to upgrade the workbook fully.

The workbook will be closed and reopened as an Excel 2013 version.

The version I am using is Microsoft Excel 2013 (15.0.4420.1017) 64-bit; Part of Microsoft Office Professional Plus 2013.

Update: I spoke with Jason Thomas and we confirmed that we are running different versions of Microsoft Office 2013. I have downloaded mine from my Technet Subscription which is the Microsoft Office Professional Plus 2013, whereas he obtained his copy from Office Preview website.

Enjoy!

My 2012 US Tour with #SQLFamily

Nov 15th, 2012 4 Comments

My Pacific North West US tour earlier this month officially concluded on 11 Nov 2012. I am very honoured and grateful that I met new friends, caught up with old friends and delivered 4 presentations too. The greatest thing about this trip is the community and networking, especially at SQL Saturday 172 in Portland, PASS Summit 2012 in Seattle, and SQL Saturday 166 in Olympia. The sessions delivered in these two SQL Saturday events were superb and attendees were so eager in learning.

Last year, I was just a book-worm (or a session worm?) where I went to every single session slot, as I had two very specific aims; i.e. learning SSAS performance tuning and finding out more about SQL Server 2012. I achieved what I wanted and was really happy to find a gateway to learning. I met lots of new people during the sessions too.

This year, I decided to take a different approach. Throughout 2012, I have talked to people from other parts of the world via twitter, LinkedIn and emails. So, it was important during PASS Summit 2012 for me to meet them, and to thank them in person for sharing their thoughts and opinions. The result is, throughout my US trip, I met more people, from different parts of Australia, United States (outside of Seattle), United Kingdom, Germany, Portugal, Spain, Mexico, Denmark, Slovenia, Philippines, Netherland, Norway, Brazil, New Zealand, to name a few.

One thing that I realise is that we are a family, #SQLFamily; we are diverse in knowledge, expertise and other aspects of life, but we are one that we share passion in knowledge sharing and learning. So, I am proud to be part of #SQLFamily and that every time I am around my #SQLFamily, I feel home.

As an Alumni Mentor for the First Timers group at PASS Summit 2012, I sent a few tips. One of them was that everyone’s experience for PASS Summit would be unique. You are entitled to have different experience from your peers. The most important thing is that you are having a good time and learning. I had a blast! So, I’d like to thank the sponsors, organisers, volunteers, speakers and #SQLFamily in making PASS Summit 2012 happen. The feedback that I have received from other speakers, my First Timers group and attendees have been nothing but positive. So, once again, many thanks.

How do you rate your PASS Summit 2012 experience? Share your thoughts here and let PASS know!

Last but not least, I would also like to extend my thank you to SQL Saturday 172 and SQL Saturday 166 organisers, sponsors, volunteers and attendees.

Highlights from PASS Summit 2012

WIT Luncheon – PASS Summit 2012 by Sarah Strate

PASS 2012 Day 1 – Interviews and Keynote

PASS 2012 Day 2 – Keynote and Session Recording (Query Tuning Mastery with Adam Machanic)

PASS 2012 Day 3 – Interviews and Session recording (Working with Claims and SQL Server BI Technologies with Adam Saxton)

PASS Global Community Video

PASS 2012 Keynote and Mobile BI Announcement by Marco Russo

PASS 2012 Announcements by James Serra

Miguel Escobar says: It's definitely worth spending some time trying to... on "Revisiting GeoFlow Public Preview"
Anil says: will be a frequent visitor as i have subscribed your... on "Upgrading from SQL Server 2012 Evaluation Edition"
Julie Koesmarno says: Thank you for visiting and for the great questions... on "Upgrading from SQL Server 2012 Evaluation Edition"
Anil Mahadev says: Looking fwd to following your posts and helping me... on "Disclaimer"
Anil Mahadev says: I plan to try this weekend where I will have a SQL... on "Upgrading from SQL Server 2012 Evaluation Edition"

Ms SQL Girl

PowerPivot 2013 Error: Data Model Damaged

Wrap Up

Further Reading

Editing Published SSIS Package in SQL Server 2012

Option A: New Project

Option B: The shortcut

Would it work for SSDT Visual Studio 2012?

Wrap Up

Further Reading

SQL Saturday 198 – Calling SQL, Data and Snow Enthusiasts

Malibu SQL Server User Group

SQL Community in Malibu!

Join SQL Malibu

Speakers wanted!

24HOP Back Again With Business Analytics

Starting 2013 with BI Talks

Standing Firm – Story of a WIT

Wrap Up

Power View for Geographical Analysis with Excel 2013

1. Earthquake Last 30 Days

2. PASS Chapter and SQL Saturday Growth

3. Internet Sales by Customer Occupation

Wrap up

Measure Selection Using Slicers – Excel 2013

Upgrading Excel 2010 workbook with PowerPivot to Excel 2013

My 2012 US Tour with #SQLFamily

Highlights from PASS Summit 2012

Upcoming and Past Events

Archives

Recent Posts

Categories