Use SQL2005 to Connect and Present SAP on SharePoint / Business Scorecard Manager - Part III 
2. Start -> Programs ->Micosoft SQL 2005 -> SQL Server Management Studio
Keywords: SAP R/3 4.6c, SQL2005, SharePoint Portal Server 2003, Business Scorecard Manager 2005
In this tip, I will demonstrate how to extract SAP data via .NET Provider for mySAP into SQL 2005 database and then use SQL2005 business intelligence - analysis service to create cubes and then use reporting service to create report model and finally create reports and present on reporting service web page.
Architecture
The demo is to use SQL 2005 Integrating Service (SSIS) to extract sales data (Customer Info, Product Info, Sales Items Info) from related SAP database into SQL 2005. Then use Analysis Service (SSAS) to do business intelligence on these tables to create a Sales Item cube with two dimensions of Product and Customer. Then use reporting service (SSRS) to create a reporting model. Finally use report builder client tool to create a sales report/graph and publish them on report server. The data flow is shown in below figure.
The key fields of these SAP tables see below reference table:
| MARA | MATNR | Material number |
| General Material Data | MTART | Material type |
| MATKL | Material group | |
| MAKT | MATNR | Material number |
| Material Descriptions | MAKTX | Material description |
| T134T | MTART | Material type |
| Product Type Description | MTBEZ | Description of material type |
| VBAK | VBELN | Sales document |
| Sales Header | KUNNR | Customer ID |
| VBAP | VBELN | Sales document |
| Detailed Sales Item | POSNR | Sales item ID |
| MATNR | Material number | |
| KWENG | Cumulative order quantity in sales units | |
| NTGEW | Net weight of item | |
| NETPR | Price | |
| VOLUM | Volume of the item | |
| ABDAT | Reconciliation date for agreed cumulative quantity | |
| STADAT | Statistics date |
Extract SAP Data to SQL 2005 - Integration Service
1. Go to SQL 2005 server2. Start -> Programs ->Micosoft SQL 2005 -> SQL Server Management Studio
3. Connect to database engine-> Create a SQL database for data storage after extracted from SAP.
4. Start -> Programs ->Micosoft SQL 2005 -> SQL Business Intelligence Development Studio
5. Create a new Integrating Service project
6. The integration project in Solution explorer has three folders: Data Source, Data Source View and SSIS Package. Right click the SSI package -> Add a new SSIS package.
7. As above figure label 2, right click in the Connection Manager pad, add a new connection, in a pop define window, select .Net Provider for mySAP from the type dropdown menu and then define properties as shown in label 3 of above figure.
8. Then drag and drop controls from left control items panel to the control flow Tab as shown in label 4 of above figure.
9. Each data flow task such as “Customer Info from SAP”, “Product Info from SAP” and “Sales Items Info from SAP” has a data flow respectively in the tab “Data Flow”
10. Just use “Customer Info from SAP” data flow as an example, first draw the control items from left panel into the design panel. right click the Data Reader Source, say, “SAP -KNA1 Customer Data”, select SAP Data Connection(created in Step 7) as the connection manger
11. Continue to define the select statement to query tables from SAP.
12. The definition example for “Customer” please see below figure.
13. So finally the SSIS package has components of Control Flows, Data Flows(including Data Reader Source from SAP, Data Destination - sql database or flat file, action like Merge join, sort etc.) and Connection Managers.
14. Build and run this SSIS package. And then go to open SQL Server Management Studio. You can see the data has been extracted from SAP into SQL operational database.
Analyze Data - Analysis Service
15. Start -> Programs ->Micosoft SQL 2005 -> SQL Business Intelligence Development Studio
16. Create a new Analysis Service project
17. Add a new data source like below figure.
18. Add a new Data Source View and add all tables we created in sql operational database, Customer, Product, ProductType, Sales Item. And create their table¡¯s key and build relation between them like below figure.
19. Add a new cube.
20. At last the cube is built like below figure.
Generate Report Model - Reporting Service
21. Start -> Programs ->Micosoft SQL 2005 -> SQL Server Management Studio
22. Connect to reporting service-> Right click the new created Analysis Service -> generate model like below figure.
23. Finally a new node inserted under Data Source node in object explorer of SQL Management Studio.
Create Reports - Report Builder
24. Go to reporting services web site such as http://yourreportservername/reports
25. Click ?gReport Builder"
26. If it is your first time to run report builder, it will download this client tool.
27. A report builder window opened and it bit looks like other office interface such as word. It has a Get Started task pane like below figure.
28. Select the report model created in Step 21-23.
29. Select the type of reports, say, table.
30. Then design the table as you like.
31. Save the designed report to report server.
32. Users can look at the table you created on report web site.
Reference
1. Download: Feature Pack for Microsoft SQL Server 2005 - November 2005 http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displa... , accessed in Dec., 2005
2. Configuring Reporting Services to Use Integration Services Package Data http://msdn2.microsoft.com/en-us/library/ms345250.aspx, accessed in Dec., 2005
3. Use SQL2005 to Connect and Present SAP on SharePoint / Business Scorecard Manager - Part I, Ted Teng, /people/ted.teng/blog/2005/12/28/use-sql2005-to-connect-and-present-sap-on-sharepoint-business-scorecard-manager-150-part-i, accessed in Dec., 2005.
4. Use SQL2005 to Connect and Present SAP on SharePoint / Business Scorecard Manager - Part II, Ted Teng, /people/ted.teng/blog/2005/12/28/use-sql2005-to-connect-and-present-sap-on-sharepoint-business-scorecard-manager-150-part-ii , accessed in Dec., 2005.
18 Comments
