The connected user is not an Analysis Services server administrator. Only an administrator can make changes to server properties

You are a part of the local administrators group on the server. You want to add a user to the Server administrator group of your Analysis Server. After adding the user you will get next error message: The connected user is not an Analysis Services server administrator.  Only an administrator can make changes to server properties.

Solution: Start SQL Server Management Studio with the option 'Run as Administrator'. Now it is possible to add users to the Server administrator group of your Analysis Server.

The sqlncli10 provider is not registered on the local machine. Failed to connect to the server.

When you configure the data source of your Office 2010 Excel Power Pivot sheet, you can get the message:
Failed to connect to the server. Reason: The sqlncli10 provider is not registered on the local machine.

This can happen if you want to connect to a SQL 2008 (R2) server. On the client you need to have installed the Microsoft SQL Server Native Client (SQL Server Native Client). This is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver.

Solution: Install the sqlncli10 provider on your machine.

The sqlncli10 provider for SQL 2008 R2 can be downloaded from here.

X86 package.
X64 package
IA64 package.


In case you have a SQL 2012 server and have received a Power Pivot sheet which want to use the SQLNCLI10 provider, you can change the data provider to use to data provider for SQL 2012: SQLNCLI11

1. Press the Existing connection button in the Power Pivot Window
2. Select the PowerPivot Data Connection
3. Press Edit
4. Specify the SQL server name
5. Specify the Database name
6. Press the advanced
7. Select Provider:  SQL Server Native Client 11.0  

In case you can't selected the SQL Server Native Client 11.0. The provider is not installed on your client. The SQLNCLI11 provider for SQL 2012 can be downloaded from here:
X86 package
X64 package

After installation it should be possible to configure your datasource, to be able to update your Excel Power Pivot sheet with all data.

Tsql script to see creation anf modification time of all indexes in database.

Next script will show the modification date of all indexes in the selected database.

Select s.name, t.name, t.create_date, t.modify_date,i.name, c.name
From sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
inner join sys.columns c on c.object_id = t.object_id and
             ic.column_id = c.column_id
Where i.index_id > 0   
and i.type in (1, 2) -- clustered & nonclustered only
and i.is_primary_key = 0 -- do not include PK indexes
and i.is_unique_constraint = 0 -- do not include UQ
and i.is_disabled = 0
and i.is_hypothetical = 0
and ic.key_ordinal > 0
Order by 4 desc

Performance tips for your Power Pivot sheet

Power Pivot is a really good personal Business Intelligence tool with a great performance. However, for every tool there are tips to optimize the performance. In Power Pivot you need to define the BISM. (Business Intelligence Semantic model), please take next tips into consideration during the design of your BISM model:

• Use views to import data in Power Pivot. The view will contain the business logic of how the data is stored in your database. If changes are made to your business logic, you only need to change the views. The Power Pivot sheet will still work.
• Use logical columns names in the views. For instance [Account code] in stead of debnr. Everybody should understand what kind of content is stored in each column.
• Import only columns you really need. Avoid SELECT * FROM MyView1 As described in my previous blog post: Memory management in Power Pivot, all data is kept in memory. Every column which is not used will use memory which can not be used for other purposes.
• Import columns which are useful for analytics purposes. For instance for customer data: Account code, Country, State. Columns like street name are not so useful. As described here, it will create a lot of distinct values in your dictionary for this column. This will have a negative impact on performance.
• Import DateTime columns in 2 separate columns. One Date column and one Time column. If time portion is not useful for your analytics do not import it at all.
• Import master data in separate tabs. For instance all item attributes in one tab and use the item key in all transactional tabs. Link the item key from the transactional tab to the item key of the Item master tab.
• Reduce the number of rows to import. If you analyse on month level, group all data in the view to the level you want. For instance group by Date, Item, Amount. This will save a lot of rows to import. Of course, this is not possible sometimes because you do not want to loose the granularity of analysis.
• Reduce the number of rows to import by selecting only the subset you are going the analyze. For instance your database contains financial transaction as of financial year 2008. If you need to analyze of the current and previous year, import only the last 2 years.
• Optimize column data types. A column with few distinct values will be lighter than a column with a high number of distinct values. This is important also for measures, which are considered also possible quantitative attributes. If the measure you are storing is a float and is the result of a calculation, consider reducing the number of digits to be imported. This will reduce the size of the dictionary, and possibly also the number of distinct values.
• Avoid high-cardinality columns. Columns with unique ID's like invoice numbers are very expensive. Sometimes you can skip this columns and use the COUNTROWS function instead of the DISTINCTCOUNT.
• Use measures instead of calculated columns if possible. Calculated columns are stored as an imported column. This does not apply to calculated measures. A calculated measure is calculated at query time.
• In case you need to store a measure in a calculated column, consider to reduce the number of digits of the calculation.
• Normalizing data doesn’t have a big effect on the size of the resulting database. However, it might have a strong impact on both processing time and memory required to process data. The key is to find a right balance. A Star schema is in most situation the right balance.

Enjoy it, to make your Power Pivot sheets even more powerful.