Mastering PowerPivot: Question and Answer

Q.1. How to Save The Images?

Ans: To save images, follow these steps:

• Create an "Images" folder within the desired folder. For example: http:///Shared Documents/sample/Images
• Save the downloaded images from the Download Center into the "Images" folder.

Q.2. How to Start PowerPivot From The PowerPivot Gallery?

Ans:

1. To start PowerPivot from the PowerPivot Gallery, perform the following steps:
2. Click on the image of the file "HelloWorldPicnicModelPowerPivotTutorialRTM.xlsx" in the PowerPivot Gallery. This will open the workbook in SharePoint.
3. Click on "Open in Excel."
4. Then, go to the "PowerPivot" tab and click on "PowerPivot Window."

Q.3. How to Start PowerPivot From Shared Documents?

Ans:

1. To start PowerPivot from "Shared Documents," follow these instructions:
2. Click on the drop-down arrow next to "Shared Documents" and select "Edit" for the file "HelloWorldPicnicModelPowerPivotTutorialRTM.xlsx" in Microsoft Excel.
3. After selecting the "PowerPivot" tab, click on "PowerPivot Window" to access PowerPivot and Excel functionalities.

Q.4. How to Create Linked Tables in PowerPivot?

Ans: To create linked tables in PowerPivot, adhere to the following steps:

• Select all the columns and rows in the "Items" worksheet within the Excel window.
• Click on "Format as Table" in the "Home" tab and choose a format for the table.
• Click on "Create Linked Table" in the "PowerPivot" tab.
• Verify that the table contains the correct columns and rows, and that "My table has headers" is checked. The table will open on a new tab in PowerPivot.
• Right-click the tab and rename it as "Items."
• Repeat the process three more times to create linked tables in PowerPivot for "Quantities," "Distributors," and "Dates."
• Click "Save," and your model will be created.

Note: After saving, you will be back in the Excel window, and Power View will enable straightforward ad-hoc reporting through interactive data exploration, visualization, and presentation.

Q.5. How to Create Relationships in Power Pivot?

Ans: To create relationships in Power Pivot, follow these steps:

• Click on "Diagram View" in the "Home" tab of the PowerPivot Window. You will notice that there are no connections between the tables in your model.
• To see all the tables in the window, drag the tables. The "Quantities" table should be in the center as it is the foundation of the model.
• Drag an item's ID from the "Items" table's "ItemID" field to the "Quantities" table's "ItemID" field. This will establish a one-to-many link between the two tables.
• Repeat the process to establish additional connections between the "Date" field in the "Quantities" and "Dates" tables, and the "DistribID" field in the "Distributors" and "Items" tables.
• Save the Excel (XLSX) file to save your model.

Q.6. What is The Procedure for Setting Reporting Services in Power Pivot?

Ans: To set reporting services in Power Pivot, follow these steps:

• In the PowerPivot Window, click on "Switch to Advanced mode" in the File menu.
• Go to the "Items" table.
• Click on "Table Behavior" in the Advanced tab.
• Set "Row Identifier" to "ItemID." The other items in the dialogue box will become usable after selecting a row identifier.
• Set "Keeping Unique Rows" to "Name." This ensures that if two rows have the same name but different ItemID values, Power View won't aggregate them.
• Set "Default Label" to "Name."
• Click on "OK."
• Click on "Default Field Set" and add "Name," "Category," and "Color," then click "OK."
• Save the Excel (XLSX) file to save your model.

Q.7. How Do We Create a Table and Cards With Default Values in The Power View?

Ans: To create a table and cards with default values in Power View, follow these steps:

• Click on the "Internet Explorer Refresh" button. You won't lose the changes made because the report has been saved.
• Click on "Inactive this page."
• Expand the "Items" table. The "ItemID" and "Name" fields have icons next to them, indicating that they are distinct fields with the default label for this table.
• Drag the "Name" field to the view. Now, there are two "apple" values visible.
• Click the "Undo" button.
• Click the "Items" table itself to create a table with the identified fields, as defined in the default fields for this table: "Name," "Category," and "Color."
• With the table selected, click on the drop-down arrow within the Visualizations Gallery and choose "Card." The values from the "Name" field will be displayed prominently because "Name" is the default label field for the "Items" table.
• Click somewhere on the blank view so that the cards are not selected.
• Expand the "Distributors" table within the fields (upper) section of the field list. Note that it contains only the "Distributor" and "DistID" fields.
• Save the Power View report on the SharePoint site.

Q.8. How do We Consolidate Tables and Add Images in Power Pivot?

Ans: To consolidate tables and add images in Power Pivot, follow these steps:

• Combine the single field from the "Distributors" table into the "Items" table to study the model.
• To create a calculated column, right-click the "Add Column" column in the "Items" table within the PowerPivot window and select "Insert Column."
• Rename the new column as "Distributors."
• In the column formula box, type the Excel formula: =RELATED(Distributors[Distributor Name])
• Click on "Hide from Client Tools" by right-clicking the "DistID" column to make it invisible in Power View while still being visible in PowerPivot.
• Right-click the "Distributors" table tab and click on "Hide from Customer Tools."

Q.9. How to Add Images to The Model?

Ans: To add images to the model, follow these steps:

• Download the photos from the Microsoft Download Center and save them to the SharePoint site.
• Add a calculated column to the "Items" table and name it "Image Name."
• Paste in the formula: =IFERROR(IF(FIND("apple",[Name])>0,SWITCH(Left([Color],1),"b","applejuice","g",[Name]&"g","r",[Name],[Name]),[Name]),[Name])
• Add another calculated column and name it "Photo." Paste in the formula, replacing with your server name: ="http:///SharedDocuments/HelloWorldPicnicSQL2012/Images/rs_crescent_picnic_image_" & [Image Name] & ".png"
• Add another calculated column and name it "Drawing." Paste in the formula, replacing with your server name: ="http:///Shared Documents/HelloWorldPicnicSQL2012/Images/rs_crescent_picnic_drawing_" & [Image Name] & ".png"
• Add another calculated column and name it "Category Drawing." Paste in the formula, replacing with your server name: ="http:///Shared Documents/HelloWorldPicnicSQL2012/Images/rs_crescent_picnic_drawing_" & [Category] & ".png"
• Add one more calculated column and name it "Category Photo." Paste in the formula, replacing with your server name: ="http:///Shared Documents/HelloWorldPicnicSQL2012/Images/rs_crescent_picnic_image_" & [Category] & ".png"
• Save the Excel (XLSX) file to save your model.

Note: The calculated columns will not appear in the Excel window, as they are only accessible in PowerPivot.

Q.10. How to Add Images in The Power View?

Ans: To add images in Power View, follow these steps:

• Click the "Internet Explorer Refresh" button. Since the report is saved, you won't lose the modifications made.
• Expand the "Items" table to see all the new fields, including the "Drawing" field.
• Drag the "Photo" column to the table on the view. However, the URL will be displayed instead of the actual image. We need to go back to the model to resolve this.

Q.11. How to Identify Image URLs in Power Pivot?

Ans: To identify image URLs in Power Pivot, perform the following steps:

• Click on the "Items" table and then go to the "Advanced" tab.
• Check the "Image URL" box for all four URL fields.
• Click on "Table Behavior."
• Set the "Default Image" to "Drawing" and click "OK."
• Click on "Default Field Set" and add "Drawing" to the Default fields set.
• Save the Excel (XLSX) file to save your model.

Q.12. How to Add Images, Dates, and Numbers to a Table, in Power View?

Ans: To add images, dates, and numbers to a table in Power View, follow these steps:

• Click the "Internet Explorer Refresh" button. The changes made won't be lost as the report is saved.
• Add the "Drawing" field to the table.

Q.13. How do We Validate The Model in Excel?

Ans: To validate the model in Excel, follow these steps:

• Set the "Dynamic Filters" property to "True" to incorporate dynamic filters in your report. This allows end users to add filters based on any of the report's fields.
• Click on "PivotChart" and then "New Worksheet" on the PowerPivot tab within the PowerPivot window. The PowerPivot Field List will be displayed on the right in the new worksheet.
• Drag "Month Name" to "Slicers Horizontal" from the "Dates" table.
• Drag "Color" to "Slicers Vertical" and "Sort to Slicers Horizontal" from the "Items" table.
• Drag "Serve" to the "Legend Fields" within the "Items" table.
• Drag "Category" to "Axis Fields" within the "Items" table.
• Drag "Leftovers" to "Values" from the "Quantities" Table (it will be automatically aggregated as a Sum).
• Change the Chart Type by right-clicking the chart and selecting "Stacked Columns."
• To filter the chart, click on various values in the slicers.
• Save the Excel (XLSX) file to save your model.

Q.14. How to Convert a Table to a Card?

Ans: To convert a table to a card, follow these steps:

• Click on the view's blank area or click the name of the "Particulars" table in the fields section of the field list.
• A table with attributes such as "Name," "Category," "Color," and "Drawing" will be automatically created based on the default fields for the table.
• With the table named, click on the "Distributors" field in the "Fields" section of the field list. It will be added to the table.
• While the table is still named, expand the "Visualizations Gallery" and select "Card."
• You can move through the remaining cards by dragging the scroll bar on the right of the card. Each card will display the "Name" and photograph, as specified in the model's parcel settings.

Image: Tutorial Folder

Conclusion

This guide discusses SQL server elements and how to use them for creating data-driven subscriptions, basic table reports, sample reports, Power View, and optimizing sample Power Pivot models for Power View. Interested individuals can benefit from SQL training to gain proficiency with Microsoft SQL Server Reporting Services. SQL DBA Training and certification are highly sought-after courses in the industry, offering opportunities to become a skilled database administrator.