QPR Installation Instructions

Importing Data from an Excel Sheet with Mass Import

Importing Data from an Excel Sheet with Mass Import

Previous topic Next topic  

Importing Data from an Excel Sheet with Mass Import

Previous topic Next topic JavaScript is required for the print function  

mouse_24 Exercise 139: Import Data from an Excel Sheet with Mass Import

 

BTN_AR131. Create an Excel sheet as below and save it:

id

date

act_value

act_series

tar_value

tar_series

ala_value

ala_series

sc_identifier

MEA166

6.1.2008

20

act

20

tar

12

ala

sc1

MEA166

8.2.2008

21,3

act

20

tar

12

ala

sc1

MEA166

7.3.2008

21,1

act

20

tar

12

ala

sc1

MEA166

1.4.2008

21,1

act

20

tar

12

ala

sc1

MEA166

20.5.2008

20

act

20

tar

12

ala

sc1

MEA166

15.6.2008

19,8

act

21

tar

12

ala

sc1

MEA166

1.7.2008

19,8

act

21

tar

12

ala

sc1

MEA166

5.8.2008

21

act

21

tar

12

ala

sc1

MEA166

6.9.2008

21,3

act

21

tar

12

ala

sc1

MEA161

6.1.2008

75

act

78

tar

70

ala

sc1

MEA161

8.2.2008

75,1

act

78

tar

70,6

ala

sc1

MEA161

13.3.2008

72,3

act

78

tar

65,8

ala

sc1

MEA161

1.4.2008

71

act

78

tar

70

ala

sc1

MEA161

4.5.2008

73,3

act

80

tar

71,1

ala

sc1

MEA161

15.6.2008

74

act

80

tar

71,1

ala

sc1

MEA161

1.7.2008

74,1

act

80

tar

72

ala

sc1

MEA161

5.8.2008

73

act

80

tar

72

ala

sc1

MEA161

6.9.2008

72,3

act

80

tar

69,9

ala

sc1

 

BTN_AR132. In QPR Metrics client, create a new model based on the Basic Scorecard Template:

pic_mass_import1

BTN_AR133. On the bottom of the Model Navigator frame, click the btn_scorecard_navigator to open the Scorecard Navigator.
BTN_AR134. From the Tool Palette, drag two Measure elements under the Learning & Growth element in the Scorecard Navigator:

pic_mass_import2

BTN_AR135. In the Scorecard Navigator, double-click the Measure to open the Element Properties window.
BTN_AR136. On the General tab of the Element Properties window, change the Identifier of the measure to "MEA161" (this matches the 'id' column in the Excel sheet), choose Maximize as the Value setting and Month as the Period level:

pic_mass_import3

BTN_AR137. Close the Element Properties window by clicking the btn_close_element_properties button on the top right corner of the window.
BTN_AR138. Similarly, change the Identifier of the Measure(2) element to "MEA166", the Value setting to "Maximize" and the Period level to "Month".
BTN_AR139. On the Model Navigator frame, double-click the Scorecard scorecard to open the Scorecard Properties window.
BTN_AR1310. Change the Identifier of the scorecard to "SC1" (this matches the 'sc_identifier' column in the Excel sheet):

pic_mass_import4

BTN_AR1311. Close the Scorecard Properties window by clicking the btn_close_element_properties button on the top right corner of the window.
BTN_AR1312. Next, we'll need to create an ODBC to the Excel sheet. From the Windows Taskbar, select Start -> Control Panel -> Administrative Tools -> Data Sources (ODBC).
BTN_AR1313. The ODBC Data Source Administrator window opens. Switch to the System DSN tab and click Add:

pic_mass_import5

BTN_AR1314. The Create New Data Source window opens. Select Microsoft Excel Driver and click Finish:

pic_mass_import6

BTN_AR1315. The ODBC Microsoft Excel Setup window opens. Type in a name to the Data Source Name field, click the Select Workbook button, browse for the Excel sheet you created and click OK.
BTN_AR1316. Click OK to close the ODBC Microsoft Excel Setup. The System Data Source you created should now be listed in the Data Source Administrator window:

pic_mass_import7

BTN_AR1317. Click OK to close the ODBC Data Source Administrator window and return to the QPR Metrics client.
BTN_AR1318. On the View tab of the ribbon, click the btn_integration_view Integration View button.
BTN_AR1319. The Integration View window opens. From the Views group, select SQL mass imports, and from the Commands group, select New:

pic_mass_import8

BTN_AR1320. The SQL Mass Imports window opens. Click the Connect button, select the ODBC we created and click OK:

pic_mass_import9

BTN_AR1321. The Database Login window opens. Leave the Username and Password fields empty and click OK.
BTN_AR1322. Type in a name to the Task name field and write select * from "sheet1$" to the SQL query field:

pic_mass_import10

BTN_AR1323. Click the Execute button. The values are imported from the Excel sheet.
BTN_AR1324. Next, we will save the values. This has to be done separately for the Actual, Target and Alarm values. First, we'll save the Actual values. From the drop-down menus in the Imported Values tab, select the following:

Values in column: act_value

Periods in column: date

Scorecard: sc_identifier

Element: id

Series: act_series

BTN_AR1325. Click Save.
BTN_AR1326. Now repeat steps 24 and 25 for the Target and Alarm values:

Values in column: tar_value

Periods in column: date

Scorecard: sc_identifier

Element: id

Series: tar_series

Save

 

Values in column: ala_value

Periods in column: date

Scorecard: sc_identifier

Element: id

Series: ala_series

Save

BTN_AR1327. Click Close and answer Yes to the "Save changes" question.
BTN_AR1328. In the Scorecard Navigator frame, double-click the Measure element.
BTN_AR1329. The Element Properties window opens. Switch to the Values tab and scroll down to the periods from the year 2008. The imported values should be visible:
pic_mass_import12

 

For more information, see the Integration View Window and SQL Mass Import topics in QPR Metrics - User's Guide.