Unable to open Excel workbook as it does not contain any visible named items

Recently I setup the Excel Services on SharePoint 2010 Enterprise and posted an Excel workbook to the PowerPivot Gallery. But when I tried to open it on the portal, the following error displayed:

This is a very common error produced by Office Web Applications.  When saving an Excel workbook to a SharePoint site, you can decide to save the complete workbook or only specific Named Items (includes sheets, named cells or ranges, tables, PivotTables, or charts). This is different than saving the workbook to the file system.  By default the Named Items option are not enabled or needed in direct Excel interaction. However when you uploading the workbook to SharePoint, you must edit the Publish Option (to select the entire worksheet or Individual sheet) before you Save & Sent to the SP site.
There are two ways to edit the option as showing below:

 

Once you set the options, you should be able to view the published workbook on the SharePoint portal now.

Share Excel Workbooks on SharePoint Server 2010

You can reuse and share Excel workbooks on SharePoint Server 2010 portals and dashboards by using Excel Services.   This means that you can control what data is displayed and maintain a single version of your Excel workbook.
Excel Services supports workbooks that are connected to external data sources. By saving them centrally in a data connection library file on the SharePoint Site, other people could also use the same connection method to view and interact with your data.
To do that you will need to export the data source connection files to SharePoint Site as following:

• Open the excel
• Go to Data tab and click on the Connection Properties menu for your worksheet

• 
  

• Click on the Definition tab and then click on the Export Connection File button on the bottom 
• Save the file on a SharePoint site with Office Database Connection extension (*.odc)

• Once the data connection file been saved on the SharePoint site, the location of the connection file should be changed and pointed to the SharePoint Site

• Now you could Save and Publish your Workbook to SharePoint site by Click on the Save & Send  menu

Once you did that, other people from your team/company can view live, interactive workbooks you created by using the browser on the SharePoint site.

SSIS Error Loading Excel Data with Office 2010 installed

Recently I am setting up a virtual machine to play with the new SQL Server “Denali”. The latest Office 2010 applications and Visual Studio 2010 also have been installed on it.  I open a SSIS project and create an Excel Connection Manager with default version set to Microsoft Excel 2007 to load a excel spreadsheet that was saved as *.xlsx format .
When I  use this newly created Excel Connection Manager as the Data Source in my data flow, I got the following error after click the Preview button:

The SSIS error log has following:

SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR.  The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. Error code: 0x00000000.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".
People will normally assume that the machine should contains the latest data provider (Office 2007 provider(12)), when the Office 2010 Excel/Access application installed on the machine. Unfortunately, it is not the case.

There are two ways to resolve this issue:

1. Install the latest office driver form the following link: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en
2. Save the Excel spreadsheet to be Microsoft Excel 97-2003 (*.xls) format to load the data in.

Prepare Microsoft 70-452 Certification Exam

Recently I want to get the Microsoft 70-452 certification, so I could become a Microsoft Certified IT Professional (MCITP) for Business Intelligence Developer 2008.  There is a pr-requirement for this exam which I already had:
Exam 70-448: TS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance
After I search the internet for free/cheap training materials, I find the following:

1. A free 6-part webcast series to prep for 70-452 available from the Microsoft Partner Learning Center:  https://training.partner.microsoft.com/learning/app/management/LMS_LearnerHome.aspx
2. A free 12 hours self-paced course: Essential SQL Server 2008 For Developers from Microsoft e-learning: https://www.microsoftelearning.com/eLearning/courseDetail.aspx?courseId=166269

To me, the first webcast is introduction type information and it is very generic. It is suitable for people who are new to SQL 2008 BI.  If you are expert or advanced BI developer, you may not want to waste you time on it. I did find the 2nd course very helpful.  Since I don’t have time and I feel I am expert in some area, I skip the first several topics and only review those that I am not familiar with such as Data Mining for SQL 2008, Understanding SSAS Query & Performance Improvements, etc.. Lots contents in there are very helpful for 70-452 exam.
I also use Transcender’s practice material: http://www.transcender.com/practice-exam/microsoft/70-452.kap. It is a very useful tool to simulate the actual certification exam and provides validated answers.
After studying for a week using above materials, I passed the exam and got the MCITP.
Hope you will find this information useful.

A Good Time at PASS Summit 2010

There are lots session I could choose, lots people to meet with, and definitely lots parties and fun after hours. I really enjoyed the atmosphere and the different ways people share their knowledge.

This is the best week of the year for me and I hope I will see you all there next time.

Process SSAS Dimensions and Cubes Individually and Automatically using AMO .NET objects

I posted a blog entry before to Process the SSAS dimensions individually and automatically. If you use the same method to process the cube, you will find that it may not work all the times since the cube could be in an invalid state and unable to retrieve the Cube ID/Cube Name.

To overcome the issue, I will need to use the AMO .Net in stand of using MDV to accomplish this.

1. Add a Script Task first in the the ETL control flow to retrieve the Dimension and Cube information.

   Here is the steps in the Script Task:

   • Create the AMO.Server object and connect to the SSAS database; Make sue you add Microsoft.AnalysisServices reference to the project.
   • Retrieve the Database ID once it is connected.
   • For each AMD Dimension in the database, retrieve the Dimension ID into an Array List
   • For each AMO Cube in the database, retrieve the Cube ID into an Array List
   • Save the ArrayLists as the SSIS Object Variables to be used later.

   // Create instance of Analysis Services object

   AMO.Server aServer = new AMO.Server();

   ArrayList aDimensionList = new ArrayList();

   ArrayList aCubeList = new ArrayList();    

   try

   {

       //Get SSAS_DataMart connection string

       string strConnection = "";

       foreach (ConnectionManager aManager in this.Dts.Connections)

       {

           if (aManager.Name == "DataMart")

           {

               strConnection = aManager.ConnectionString;

           }

       }

       //Throw error if not such connection manager

       if (strConnection == "")

       {

           Exception ex = new Exception("No OLAP Connection manager: OLAP_DataMart found");

       }

       // Connect to DataMart instance of Analysis Services

       aServer.Connect(strConnection);

       string strCatalog = aServer.ConnectionInfo.Catalog;

       //Get the Database name

       AMO.Database myDataBase = aServer.Databases[strCatalog];

       System.Diagnostics.Debug.WriteLine(" Database Id = " + myDataBase.ID);

       Dts.Variables["_DatabaseName"].Value = myDataBase.ID;

       //Get list of dimension and set to the DTS Variable

       foreach (AMO.Dimension aDimension in myDataBase.Dimensions)

       {

           System.Diagnostics.Debug.WriteLine(" Dimension Id = " + aDimension.ID);

           aDimensionList.Add(aDimension.ID);

       }

       Dts.Variables["_DimensionList"].Value = aDimensionList;

       //Get list of Cube and set to the DTS Variable

       foreach (AMO.Cube aCube in myDataBase.Cubes)

       {

           System.Diagnostics.Debug.WriteLine("Cube = " + aCube.ID);

           aCubeList.Add(aCube.ID);

       }

       Dts.Variables["_CubeList"].Value = aCubeList;

   }

   catch (Exception ex)

   {

       System.Diagnostics.Debug.WriteLine(ex.ToString());

       Dts.TaskResult = (int)ScriptResults.Failure;

       Dts.Variables["Pamlico_PackageExecutionResult"].Value = Dts.TaskResult;

       throw (ex);

   }

   finally

   {

       // DisConnect to the local instance of Analysis Services

       if (aServer != null)

       {

           if (aServer.Connected)

           {

               aServer.Disconnect();

           }

       }

   }

   Dts.TaskResult = (int)ScriptResults.Success;

   Dts.Variables["PackageExecutionResult"].Value = Dts.TaskResult;

   
   
2. Add Foreach Loop to process the Dimensions individually. In this step, you may add logging information to indicate the dimension being processed.
   
3. Add another Foreach Loop to process the Cubes individually after processing the dimensions.

This way even thought the dimension process failed for some reason, we will still be able to retrieve the Cube ID back and reprocess them later without problem.

Using Color in Reports

Recently I reviewed an article Using Color in SSRS Charts reported from Melissa Coates. It is a very useful article contains Pros  and Cons with various methods when choosing color for SSRS Charts. You may also want to check this out.