Category: PowerPivot

Development Tips, Excel, How To, Office Integration, PowerPivot, Report

How to add Filter for data retrieval in PowerPivot Data model.

Ashwini Tripathi

Recall from previous posts we have added Item Ledger Entry Table to our Data Model of PowerPivot.

Adding more tables to the Data Model using Existing Connection – In PowerPivot

Add relationships to Data Model in PowerPivot

My requirement is to retrieve only records related to Sales.

We will apply filter on Entry Type field to value [1]-[Sales] this way Item Leger Entry Sheet will have only Sales Data.

To do this we will add this filter to the query used for data retrieval.
PowerPivot-15

Select the Sheet for table – Item Ledger Entry from bottom.

Click on Design Tab on Top.

Now From Ribbon choose Table Properties.

PowerPivot-16

Select Query Editor from Switch to.

Add the Filter to your query.

Save your Query.

PowerPivot-17

From Home Tab, Select Refresh from Ribbon.

On Success completion Close the window.

Now your Item Ledger Entry is populated with Fresh data as per the modified Query.

I.e: All Entries which have Entry Type = 1 [Sales].

I will come up with more details in my upcoming post, till then keep practicing and stay tuned for future updates & posts on this topic.

Development Tips, Excel, How To, Office Integration, PowerPivot, Report

Adding more tables to the Data Model using Existing Connection – In PowerPivot

Ashwini Tripathi

For Introduction please see PowerPivot for Excel

Recall from my earlier post we have created a connection to SQL Navision Database in my previous post.

PowerPivot Creating a Data Model in Excel 2013

I will be modifying same connection to include more tables to the Data model.
PowerPivot-10

Click on PowerPivot-> Manage.

From the PowerPivot Sheet click on Existing Connections.

This will open Connection Window Listing all available connection to PowerPivot for this Sheet.

Double Click the Connection in my case Nav2015DB_SQL_Connection.
PowerPivot-6

Select first option as we used in previous post and then Next for next Step.
PowerPivot-11
Select Item & Item Ledger Entry Tables and then Finish to import Data to the Sheet.
PowerPivot-12
On Success Click Close to return to Data Model Sheets.
PowerPivot-13

You will find two new sheets have been added to PowerPivot Sheet.

I will come up with more option on this topic in my next post, till then start practicing and stay tuned for more details & Updates on this topic.

Development Tips, Excel, How To, Office Integration, PowerPivot, Report

PowerPivot Creating a Data Model in Excel 2013

Ashwini Tripathi

Before you can create a PivotTable, you’ll need some data. Let’s get some data from the Navision database.

In Excel, open a blank workbook.

PowerPivot-1

PowerPivot-2

You have several options for getting data from no of sources.

Recall from my earlier post where we accessed data using web service in Excel.

Viewing Page Data in Excel Using PowerPivot (OData)

Let us see what other options are available to us.
PowerPivot-3

In today’s Example I will be connecting with SQL but will access Navision 2015 Database.

Follow the Steps below:
PowerPivot-4

Give meaningful name to your connection in my case I have used Nav2015DB_SQL_Connection.

Select the SQL Server to which you wish to connect in my case I have used INDEL-AXT5283N1, basically this is the SQL Server installed on my Laptop.
PowerPivot-5

Specify the Login method, I am using Windows Authentication, if required you can use SQL Server Authentication. In later case you will have to specify your User Name & Password.

Select the Database to Connect, I have selected Demo Database NAV (8-0) Std. Database for Navision 2015.

Click the Test Connection button, If everything is ok you will get Connection Succeed Message, as shown in above screen shot.

Press Next for next step.
PowerPivot-6

In this screen you have option to either import data from Tables and Views or you can Write Query to fetch data for import.

In my case for this example I am importing data from tables.

Select option – Select from a list of tables and views to choose the data to import and Press Next for next step.
PowerPivot-7

I have Selected two Tables Cust. Ledger Entry & Customer.

Select Related Tables button ensure to select if any other tables related to this Table.

In my case not applicable. Click Finish to add data to the Data Model/ Import the data of these two tables.
PowerPivot-8
Select Close to return to Data Sheet imported after this operation.
PowerPivot-9
Here you see your Data for these two tables have been imported in two sheets.

What happened?

You might not have realized it yet, but you’ve just created a data model. It’s created automatically when you import or work with multiple tables simultaneously in the same PivotTable report. The model is mostly transparent in Excel, but you can view and modify it directly using the Power Pivot add-in. In Excel, the presence of a data model is evident when you see a collection of tables in the PivotTable Fields list. There are several ways to create a model.

I will come up with more details in my next post.

Development Tips, Excel, Office Integration, PowerPivot, Report

PowerPivot for Excel

Ashwini Tripathi

Power Pivot: Powerful data analysis and data modelling in Excel

Power Pivot is an Excel add-in you can use to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily.

In both Excel and in Power Pivot, you can create a Data Model, a collection of tables with relationships. The data model you see in a workbook in Excel is the same data model you see in the Power Pivot window. Any data you import into Excel is available in Power Pivot, and vice versa.

How the data is stored

The data that you work on in Excel and in the Power Pivot window is stored in an analytical database inside the Excel workbook, and a powerful local engine loads, queries, and updates the data in that database. Because the data is in Excel, it is immediately available to PivotTables, Pivot Charts, Power View, and other features in Excel that you use to aggregate and interact with data. All data presentation and interactivity are provided by Excel; and the data and Excel presentation objects are contained within the same workbook file.

Power Pivot supports files up to 2GB in size and enables you to work with up to 4GB of data in memory.

Download PowerPivot for Excel

To determine whether you are using 32-bit or 64-bit software, look at the C:\Program Files folder.

Download x86\PowerPivot_for_Excel_x86.msi if you have only “C:\Program Files”. Both the operating system and Office 2010 are 32-bit.

Download x86\PowerPivot_for_Excel_x86.msi if you have both “C:\Program Files” and “C:\Program Files (x86)”, and the Excel.exe application file is found in “C:\Program Files (x86)\Microsoft Office\Office14”. The operating system is 64-bit, but the version of Office is 32-bit.

Download x64\PowerPivot_for_Excel_amd64.msi if you have both “C:\Program Files” and “C:\Program Files (x86)”, and the Excel.exe application file is found in “C:\Program Files\Microsoft Office\Office14”. Both the operating system and Office 2010 are 64-bit.

Install PowerPivot for Excel

  • Double-click the .msi file to start the Setup wizard. Click Run.
  • Click Next to get started.
  • Accept the license agreement, and then click Next.
  • Enter your name, and then click Next.
  • Click Install.

Click Finish.

Verify Installation

Start Excel. After you install the add-in, you can open the PowerPivot window by clicking the PowerPivot tab on the Excel ribbon, and then clicking PowerPivot Window.

An empty PowerPivot window opens over the Excel application window.

You can then use the Import Wizard to add tables of data, create relationships between the tables, enrich the data with calculations and expressions, and then use this data to create PivotTables and PivotCharts.

Stay tuned for more information in my upcoming posts.