Archive

Archive for the ‘PowerPivot’ Category

HDInsight on Windows: Building PowerPivot Report from Hive in a Few Easy Steps (PDF)

January 7, 2013 Leave a comment

Download Step by Step PDF from Slide Share

Categories: HDInsight, PowerPivot

HDInsight on Windows: Building PowerPivot Report from Hive in a Few Easy Steps

January 4, 2013 2 comments

What are we Covering:

  • Hive
    • Create table
    • Load Data
    • Query Data
  • Configure ODBC
  • Create PowerPivot
    • With DataSource as HDInsight
    • Import Data from HDInsight 
  • Create Pivot Chat using imported data

 

(1)Open Hadoop Command Line

After installing HDInsight for Windows  you will get Hadoop Command Line Shortcut on desktop

Click on Hadoop Command Line Shortcut and it will open Cmd Line for Hadoop

clip_image002

(2)Type Hive and it will change to Hive Command

clip_image004

(3)Create Tables

CREATE TABLE emp(id int,name string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’;

CREATE TABLE sales(id int,sales int)ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’;

clip_image005

(4)Load Data using CSV Files

LOAD DATA LOCAL INPATH ‘c:/data/emp.csv’ OVERWRITE INTO TABLE emp;

clip_image006

LOAD DATA LOCAL INPATH ‘c:/data/sales.csv’ OVERWRITE INTO TABLE sales;

clip_image007

(5) Open HDInsight Dashboard (You will find shortcut on desktop) and click on Download

clip_image010

(6)Click on the appropriate link for your Excel.

clip_image013

(7)Configure ODBC

For configuring ODBC, Click start and type in ODBC. This will bring up the Data Sources dialog. Go to the System DSN tab and click Add. Configure it with a Data Source Name, Your Host Information, and a Username. In Single Node HDInsight Installation you don’t need to provide Username but if you are using HDInsight on Azure you can provide Username .

clip_image014

clip_image016

(8) Provider Data Source Name and Default Port is 10000

clip_image018

(9)Open Excel Sheet, select PowerPivot in ribbon and click on Manage

clip_image021

(10)Click on Get External Data

clip_image025

(11)Select – Get External Data and Data Import window and Select Others(OLEDB/ODBC)

clip_image027

(12)Click Build

clip_image029

(13)Select OLE DB Provider for ODBC

clip_image030

(14)Select DSN which we have created earlier in step number 8, Select Database (In Single Node HDInsight installation you don’t need to provider User Name and Password in current release but in case of HDInsight on Azure you need to provide User Name and Password) and follow the wizard for selecting tables in our case its EMP and Sales

clip_image031

clip_image034

clip_image035

clip_image036

(15)If you Notice Relationship between Emp and Sales is absent so with one click we can create relationship

clip_image038

clip_image040

(16)Added feature like Calculated Measure name Sales and created a KPI

clip_image041

(17)Select Pivot of your Choice from PivotTable

clip_image042

(18) Finally – you are good to go with PowerPivot Report based on Hive

clip_image044

Refer

HDInsight – What is it?

Configuring: PowerPivot for Excel 2013

PowerPivot–Enable Logging

For enabling Verbose Logging follow these steps –

Go to Central Admin and Click on Monitoring

image

Click on Configure Diagnostic Logging

image

Enable Verbose Logging as shown below – Here I have enabled Logging for Excel and PowerPivot

Set  Least Critical Event to report to the Event Log and Trace Log to Verbose as shown below and Click OK

image

Further Ref –

http://technet.microsoft.com/en-us/library/ee210681(v=sql.105).aspx

X========End of Article=======X

Categories: PowerPivot Tags: ,

Step by Step Guide of Installing Power View with PowerPivot 2012

April 16, 2012 Leave a comment

Install Win 2008 R2 + SP1

Roles

IIS

Add .Net Framework 3.5 Features

Features

Add Desktop Experience (needs reboot)

Install SQL Server 2012 (default instance)

Note:

Few things which we need to keep in mind during installation – (New Farm)

– Install  SP2010 , Apply SP1, Apply Latest CU (guess latest released in Jan 2012)

– Install SQL 2012 – PPIV

– Configure IT

– Install SSRS Integrated with SharePoint (during this install you can select Tabular Instance of SSAS, otherwise your Power View will be limited to only PowerPivot Work Book (As you know there are 2 options in Power View, PPIV URL or Tabular Instance Name)

– Last part is the most confusing part where we need to configure the Application and Content Type

 

Steps as Follow –>

· Double click on Setup

· Click on Installation (left hand side), then click on New SQL Server stand-alone installation or add features to an existing installation.

clip_image002

· Setup support rules will identify problems if any. If there are problems fix them else press OK button.

clip_image004

· On Product Key choose Evaluation, later you can specify the product key. Press Next button.

clip_image006

· Accept the License Terms then click Next button.

· Setup files will get installed. There will be a report at the end. Press Next button.

clip_image008

· In the Setup Role choose SQL Server Feature Installation then click Next

clip_image010

· Select all features you would like to install then click Next

clip_image012

· Installation Rules should show if any problem is remaining, else click Next button

clip_image014

· Define the Instance name (Default instance)

clip_image016

· Disk Space Requirements will show up. Press Next button.

· Assign appropriate Service Accounts

· In Database Engine Configuration add users to SQL Server Administrators

· In Analysis Services Configuration add users who will have administrative permissions

· In Reporting Services Configuration specify the configuration mode:

clip_image018

· In Distributed Replay Controller add users

· In Distributed Replay Client complete based on below image then click next

clip_image020

· Uncheck Send Windows and SQL Server….. option then click Next button

· Installation Configuration Rules will display any error. Click Next button

clip_image022

· Review the Ready to Install information to validate the your selected options.

· Click Install button

· Wait until installation is completed.

clip_image024

· Click Close button

1. Install SharePoint

· Right Click on Splah HTML Application, this will allow you to review Pre-Requirements

· Click on Install Software prerequisites

clip_image026

· Click Next button

clip_image028

· Accept the License terms by clicking the checkbox then click Next button.

clip_image030

· Installation will start

clip_image032

· Once completed click Finish button.

clip_image034

· Click Install SharePoint Server

· Enter Product Key, once is validated press Continue button

clip_image036

· Accept License Terms and click Continue

clip_image038

· Click Server Farm option

clip_image040

· Select Complete Option then Click Install Now button

clip_image042

· Wait until installation is completed

clip_image044

· Once the installation it is completed. Uncheck the option “Run the SharePoint Products Configuration Wizard now”

clip_image046

2. Install SP SP1 (Very important el se PowerPivot Configuration Tool will fail)

 

· Double click on officeserver2010sp1-kb2460045-x64-fullfile-en-us application

· Accept the License Terms then click Continue button.

clip_image048

· Once completed press OK button.

clip_image049

3. Run SQL Server setup to install PowerPivot for SharePoint

This step will configure your SharePoint farm and install PowerPivot. It will also customize your farm with recommended settings for a PowerPivot installation.

a. Launch Microsoft SQL Server setup, click the Installation tab, and then click New SQL Server stand-alone installation or add features to an existing installation.

b. On Setup Support Rules page click OK. Once operation is completed click Next.

c. On Installation Type page select Perform a new installation of SQL Server “Denali” CTP3, click Next.

clip_image051

d. Either enters the product key or select free edition when prompted, accept the License Terms and click Next.

e. On Setup Role page select SQL Server PowerPivot for SharePoint, and then leave the Add SQL Server Database Relational Engine Services to this installation checkbox selected. Click Next.

clip_image053

f. On the Feature Selection page you will see all the required features selected, click Next.

clip_image054

g. On the Installation Rules page click Next.

h. On the Instance Configuration page, enter the instance name then click Next.

clip_image056

i. On Disk Space Requirements, click Next button.

j. On the Server Configuration page, enter service accounts for each SQL Server service. The Analysis Services Service Account must be a domain account. Click Next to go to the Database Engine Configuration page.

 

k. On the Database Engine Configuration page, enter the name of an administrator for SQL Server (you can click Add Current User to ensure that you are a SQL Server admin), then click Next.

 

l. On the Analysis Services Configuration page, enter the name of an administrator for Analysis Services. At minimum click Add Current User to the administrators, this is required to ensure that PowerPivot is deployed correctly in the farm.

 

m. Click Next button until you reach Ready to Install page.

n. Click Install button.

o. Once complete click Please launch the PowerPivot Configuration Tool to configure the server.

p. On the Complete page of SQL Server “Denali” CTP3 Setup click Close button.

4. PowerPivot Configuration Tool

This tool (new in CTP3) performs the SharePoint Integration and configuration steps required for PowerPivot.

a. Click Configure or Repair PowerPivot for SharePoint

clip_image064

b. On the PowerPivot Configuration Tool complete all required information

 

The database server should be the instance you install with SharePoint integration (step 3), you can leave the default port or change it based on your preferences, keep the passphrase handy as you will require it later during the configuration.

c. Click Validate button.

d. If you entered the correct information validation should succeed and display below popup message:

clip_image068

e. Click OK.

f. Now click Run button to begin the configuration of your PowerPivot farm. There will be a warning message click Yes button.

g. You will see an Activity Progress popup window.

clip_image069

h. Once completed you will see following popup message:

clip_image071

i. Click Exit button.

j. Open SharePoint Central Administration to confirm that SharePoint is installed correctly.

5. Run SQL Server setup to install Reporting Services for SharePoint and a BISM instance of Analysis Services.

Although you already ran SQL Server Setup once, you now need to run it again to add Reporting Services and an Analysis Services server in VertiPaq mode. The Analysis Services instance will host the business intelligence semantic models that will serve as data sources for the “Crescent reports” you create.

a. Launch Microsoft SQL Server Setup, click the Installation tab, and then click New SQL Server stand-alone installation or add features to an existing installation.

b. Continue through Setup, selecting the defaults until you get to the Installation Type page. On this page, make sure Perform a new installation of SQL Server “Denali” CTP3 is selected.

c. Enter the product key when prompted and navigate through Setup, selecting the defaults until you reach the Setup Role page.

d. Select SQL Server Feature Installation (the default) and click next.

e. On the Feature Selection page, select Analysis Services, Reporting Services – SharePoint and Reporting Services Add-in for SharePoint Products. Click Next twice.

clip_image073

f. On the Instance Configuration page:

clip_image075

g. Click Next on the Disk Space Requirements page.

h. On the Server Configuration page enter a service account for SQL Server Analysis Services. The following screenshot shows a domain user account, but for an Analysis Services server in VertiPaq mode, you can also use the default virtual account provided by Setup.

 

i. On the Analysis Services Configuration page, enter the name of an administrator for Analysis Services. To ensure that you are (or the account you are using is) an Analysis Services Administrator, click Add Current User. Click Add if you want to add additional users as administrator. You should add anyone who will perform processing operations on the server. Also select BI Semantic Model as your Analysis Services Deployment mode. Click Next when you are finished.

 

j. Click Next on Reporting Services Configuration page.

clip_image080

k. Click Next until you are on Ready to install page, click Install button.

l. Close the SQL Setup window.

6. Create a Reporting Services application

a. Click SharePoint Central Administration > Application Management > Manage service applications.

b. Click New > SQL Server Reporting Services Service Application.

clip_image082

c. Specify a name for the service application and an application pool that this service will run under. While you can use an existing application pool such as Web Services Default, consider creating a new application pool to isolate Reporting Services from other web services like Excel Services and Secure Store Service.

 

d. On the same page, specify the Database Server to use in provisioning the service application database. SharePoint’s database server is the SQL Server Database Engine instance that was installed as a ‘POWERPIVOT’ named instance. To use this server, enter <ServerName>\POWERPIVOT. Ensure that Windows authentication (default) is selected. Also make sure the check box for the Web Application you wish to enable Crescent on (in this case SharePoint-80) is selected under Web Application Associations. Click OK

 

e. After click Ok button

clip_image088

clip_image090

f. Your installation is complete and your server is configured and ready to use.

To add different content types

http://technet.microsoft.com/en-us/library/hh230813(SQL.110).aspx

clip_image092

clip_image094

Click Yes and OK

clip_image096

On the same page under content type click

clip_image098

Add Different Content Types

clip_image100

To verify you added the content type, go back to the library and click New Document on the Documents area of the library ribbon. You should see BI Semantic Model Connection File in the New Documents list-

clip_image102

Click on BISM Connection – SSAS tabular Instance –

clip_image104

PowerPivot Workbook.

Workbook URL – http://localhost/PowerPivot Gallery/Book1.xlsx

clip_image106