SAS Enterprise Business Intelligence

NAIP ConsortiumStrengthening Statistical Computing for NARSwww.iasri.res.in/sscnarsSAS EnterprisepBusiness IntelligencegBYRajender Parsad, Neeraj Monga, Satyajit Dwivedi, RS Tomar, RK Saini

Contents SAS Enterprise Guide Upload Data from Local Excel File to SAS BI ServerMerging Local Data with Server DataAccessing Data from Server – Create PromptsAccessing Data from Server – Create GraphsAccessing Data from Server – Create Stored Process (STP)SAS AddAdd‐ini forf MS OffiOffice (SAS AMO)Creating OLAP CubesCreating Information MapsWeb based Reporting using SAS Web Report Studio (WRS)SAS Portal2

UploadpData from Local Excel File to SAS BI Server

Upload Data from Local Excel File to SAS BI ServerClick on SAS Enterprise Guide from Start Menu4

Upload Data from Local Excel File to SAS BI ServerEnsure that you are connected to server. Click on Connection to verify credentials.5

Upload Data from Local Excel File to SAS BI ServerVerify the connection credentials6

Upload Data from Local Excel File to SAS BI ServerFollowing sample local excel file contains details on Crop Yield, Returns etc by Centre,Year, Season, Treatment etc. This will be uploaded to server in SAS Dataset format.7

Upload Data from Local Excel File to SAS BI ServerClick on File ‐ Import Data8

Upload Data from Local Excel File to SAS BI ServerOpen the excel file from local computer9

Upload Data from Local Excel File to SAS BI ServerLeave settings as default10

Upload Data from Local Excel File to SAS BI ServerCheck both buttons11

Upload Data from Local Excel File to SAS BI ServerLeave as default or modify, if required12

Upload Data from Local Excel File to SAS BI ServerLeave settings as default13

Upload Data from Local Excel File to SAS BI ServerImport process flow is automatically created14

Upload Data from Local Excel File to SAS BI ServerTemporary SAS Dataset is created15

Upload Data from Local Excel File to SAS BI ServerExport this temporary Dataset to Server16

Upload Data from Local Excel File to SAS BI ServerSelect Servers ‐ SASApp and appropriate library & Save with valid name for SAS Dataset17

Upload Data from Local Excel File to SAS BI ServerSame dataset can now be accessed from different locations.Click on File‐ Open‐ Data from a new Process Flow.18

Merging Local Data with Server Data

Merging Local Data with Server Data1 – Repeat steps in previous section to import another excel sheet or text file from Local computer toa temporary SAS Dataset.2 – Open a permanent SAS Dataset from a Library on SAS Server.20

Merging Local Data with Server Data1 ‐ Right click on temporary Dataset and select “Query Builder”.2 ‐ Click on “Add Tables” and select other Table from the Project.21

Merging Local Data with Server DataClick on “Join Tables” and select appropriate joining condition & type22

Merging Local Data with Server DataProcess Flow similar to the following is created.23

Accessing Data from Server – Create Prompts

Accessing Data from Server – Create PromptsOpen required Dataset from Server25

Accessing Data from Server – Create PromptsClick on View ‐ Prompt Manager. Click on “Add” to add a new Prompt.26

Accessing Data from Server – Create PromptsInput Prompt details like Name, Type and Values.E.g. “User selects values from a dynamic list”27

Accessing Data from Server – Create PromptsOpen Dataset from Server.Select appropriate Column, which will provide the dynamic list of values for Prompt.28

Accessing Data from Server – Create PromptsPrompt created29

Accessing Data from Server – Create PromptsRight click on the SAS Dataset & select “Query Builder”. Select “Filter Data” tab.30

Accessing Data from Server – Create PromptsSelect “New Filter” ‐ “Basic Filter” ‐ Select a column on which filter is to be applied.31

Accessing Data from Server – Create PromptsClick on Value drop‐down. Click on Prompts tab. Select the Prompt created in last step.32

Accessing Data from Server – Create PromptsProcess Flow with Prompt filter is created. When run, Drop down for selection is prompted to user.33

Accessing Data from Server – Create Graphs

Accessing Data from Server – Create GraphsOpen a dataset. Select any of the options from Tasks menu. E.g. Graph ‐ Bar Chart35

Accessing Data from Server – Create GraphsSelect appropriate Chart Type, Columns to be assigned and Appearance36

Accessing Data from Server – Create GraphsProcess flow with Prompt and Graph is created. Run Process flow.37

Accessing Data from Server – Create GraphsOutput graph is displayed38

Accessing Data from Server – Create Stored Process (STP)

Accessing Data from Server – Create Stored Process (STP)Right click on the required process flow. Select “Create Stored Process”40

Accessing Data from Server – Create Stored Process (STP)Save STP in appropriate location41

Accessing Data from Server – Create Stored Process (STP)Leave settings as default42

Accessing Data from Server – Create Stored Process (STP)Modify path, if required43

Accessing Data from Server – Create Stored Process (STP)Ensure that the Execution Server and Source File Path & Name are correct44

Accessing Data from Server – Create Stored Process (STP)Stored Process is created. It can be accessed from different interfaces likePortal, Web Report Studio, Enterprise Guide, Add‐in to Microsoft Office.45

Accessing Data from Server – Create Stored Process (STP)Login to SAS Web Report Studio and access Stored Process.46

Accessing Data from Server – Create Stored Process (STP)Create a new Report and Insert a Stored Process and “Edit” to select required Stored Process47

SAS Add in for MS Office (SAS AMO)

SAS Add in for MS Office (SAS AMO)Access SAS Stored Process from “Reports” tab in SAS AMO49

Creating OLAP Cubes

Creating OLAP CubesClick on SAS OLAP Cube Studio 4.2 from Start Menu51

Creating OLAP CubesEnsure that connection profile is configured52

Creating OLAP CubesMention appropriate cube path and Input Type53

Creating OLAP CubesSelect the input table to be used for Cube creation54

Creating OLAP CubesAdd Dimensions, Levels & Hierarchies55

Creating OLAP CubesSelect appropriate measures. Leave settings as default. Click Finish.56

Creating Information Maps

Creating Information MapsClick on SAS Information Map Studio 4.2 from Start Menu58

Creating Information MapsSelect Cube with all dimensions & measures.measures Save Information Map in appropriatefolderc59

Web based Reporting using SAS Web Report Studio (WRS)

Web based Reporting using SAS Web Report Studio (WRS)Login to SAS WRS with credentials. Create a new report.61

Web based Reporting using SAS Web Report Studio (WRS)In “Data” menu, click on “Select Data” and select appropriate Information Map.62

Web based Reporting using SAS Web Report Studio (WRS)Select required data items. Select Crosstab, Graph as required.Click on View to see the report.63

SAS Portal

SAS PortalLogin to SAS Portal. Click on Options ‐ Add Page. Give a name to the new page.65

SAS PortalIn new page, click on “Edit Page Content”. Adjust Layout. E.g. as shown here.Click on “Add Portlets”.66

SAS PortalSelect required Portlet Type. E.g. “Collection Portlet”.Give a Name to it and assign it to a Row in Portlet Layout.67

SAS PortalEdit Content to Add Items into this Portlet.68

SAS PortalAdd Items (e.g. SAS report, SAS Stored Process) by searching with Keywords.69

SAS PortalAdd “URL Display Portlet”, similar to Collections Portlet.Give it a Name & Assign to a Row.70

SAS PortalEdit new URL Display Portlet to mention the URL of required website.Select the button to “Show full HTML content an in‐line frame.71

SAS PortalAdd “SAS Stored Process Portlet”, similar to “Collections Portlet”.Give it a Name & assign to a Row.72

SAS PortalEdit Portlet to select appropriate Stored Process73

SAS PortalOutput of Stored Process is directly displayed on the Portal74

Thank You!

Creating OLAP Cubes Click on SAS OLAP Cube Studio 4.2 from Start Menu 51. Creating OLAP Cubes Ensure that connection profile is configured 52. Creating OLAP Cubes Mention appropriate cube path and Input Type 53. Creating OLAP Cubes Select the input table to be used for Cube creation 54. Creating OLAP Cubes .