BIS 2012 con đường cho giải pháp Business Intelligence và SharePoint Web Application


Khái lược:

Sau 12 năm nghiên cứu và xây dựng hệ thống BI bằng các phương án cổ điển như: Visual Basic  để thiết lập môi trường AW Ad-Hoc Reporter bằng Windows Form

Mô hình sản phẩm nhằm thiết lập Hệ thống báo cáo báo biểu đã được Microsoft phát triển từ thập niên 90:

BIS_Production

Tham khảo bài viết: https://thangletoan.wordpress.com/2013/07/30/lap-trnh-aw-ad-hoc-reporter-lm-bo-co-rdl-trong-sql-server-2008-r2/

Các sản phẩm và công nghệ BI tôi đã nghiên cứu và thiết kế trải dài hơn 13 năm qua được thể hiện:

  1. 1995: VISData 1.0
  2. 1998: VISData 1.1
  3. 2001: VASEPData 2.0
  4. 2003: PSMS Data 3.0
  5. 2004: WCOO Customs DB 1.0
  6. 2005: VN Customs Data 3.1
  7. 2008: Spanish Customs Data 2.0
  8. 2010: VN Customs DB 4.0
  9. 2013: Global Customs BIS 2012

Và vừa qua tôi đã có cơ hội để thể hiện một quan điểm, một cách nhìn đổi mới, một con đường phát triển công nghệ ứng dụng giải pháp mới cho toàn bộ

mạng lưới:

–  WCOO – ECC (Hải quan Châu âu)

– FIGIS (Tổ chức liên minh các Chuyên gia Nghiên cứu phân tích Kinh tế liên hợp quốc )

– FAO (Tổ chức nông lương thế giới)

– FIIS (Phòng kinh tế Thuỷ sản)

BIS2012

Kết quả:

– Sự thành công là chắc chắn vì nó là sự nghiệp, đam mê nghiên cứu và ứng dụng thực tiễn của tôi, của cả một đội ngũ các chuyên gia phân tích, thống kê quốc tế.

– Trong đó vai trò: Công nghệ Microsoft BI góp một phần rất quan trọng trong đường lối tư duy “Đổi mới” để giải quyết các  bài toán về Big Data vô cùng cấp thiết, phức tạp và quan trọng.

– Thay đổi trên đây mang tính quyết định vì nó đột phá hẳn tư duy cũ, cách làm cũ, về việc muốn lập các báo cáo phân tích phải làm Windows Form, phải xây dựng từ người lập trình ứng dụng, cung cấp báo cáo phải được xây dựng và quản lý bởi người quản trị và phải có Website, Windows Form hoặc Portal để cung cấp dịch vụ… Tất cả đều trở nên cổ điển với cách tư duy và thiết kế hệ thống mới… đó chính là BIS 2012.

Lập trình AW Ad-Hoc Reporter làm báo cáo RDL trong SQL Server 2008 R2


I. Giới thiệu về BI:

Mô hình sản phẩm nhằm thiết lập Hệ thống báo cáo báo biểu đã được Microsoft phát triển từ thập niên 90:

BIS_Production

Các sản phẩm và công nghệ BI tôi đã nghiên cứu và thiết kế trải dài hơn 13 năm qua được thể hiện:

  1. 1995: VISData 1.0
  2. 1998: VISData 1.1
  3. 2001: VASEPData 2.0
  4. 2003: PSMS Data 3.0
  5. 2004:  WCOO Customs DB 1.0
  6. 2005: VN Customs Data 3.1
  7. 2008: Spanish Customs Data 2.0
  8. 2010: VN Customs DB 4.0
  9. 2013: Global Customs BIS 2012

II. Nhận xét:

Tất cả các sản phẩm trên thành công đều dựa trên nguyên tắc sau:

  1. Công nghệ mới của Microsoft chuyên về xử lý dữ liệu.
  2. Tập trung cải tiến và phát triển các hệ thống CSDL lớn (Big Data – Data Warehouse – OLAP – Ad-Hoc Report on Web – Report Services).
  3. Học hỏi các giải pháp phân tích và lập trình bằng các ngôn ngữ dễ tích hợp trên nền SQL Business Intelligence Development Solution như VB , C#.

Mô hình xử lý dữ liệu của Microsoft hiện nay đã thay đổi mạnh mẽ và con đường công nghệ cũng đã chọn theo hướng:

image

 

III. Thông tin tham khảo:

Truy cập các trang web để tải về các ứng dụng cần thiết cho việc thiết kế ứng dụng:

  • Windows Form hoặc Web Form làm theo mô hình AW Ad-Hoc Reporter.
  • Nên chọn ứng dụng chuẩn và phù hợp với phiên bản SQL Server 2008 R2.

http://msftrsprodsamples.codeplex.com/ 

http://sqlserversamples.codeplex.com/#utilities

http://technet.microsoft.com/en-us/sqlserver/ff660783.aspx

http://archive.msdn.microsoft.com/reportviewer/Release/ProjectReleases.aspx?ReleaseId=4940

 

Source code
The book’s source code can be downloaded from Manning’s web site at http://www.manning.com/lachev.

The next sections discuss the software requirements for executing the code and the steps to set it up. Instead of partitioning the source code on a per-chapter basis, we decided to consolidate most of it in two applications: a WinForm-based AWReporterWin application and a web-based AWReporterWeb application. This approach has several advantages, including the following:

  • Simplifies the setup—For example, you need only one virtual folder to host the AWReporterWeb web application.
  • Allows the reader to launch the samples conveniently from a single application menu.
  • Simulates real-world applications—For example, you can encapsulate the code logic in a set of common classes.
    The trade-off is that you may not have all the software dependencies required to compile the sample applications and you may run into compilation errors, as explained in the next section.

SOFTWARE REQUIREMENTS
Some samples have more involved setup requirements. For example, chapter 12 requires the Office Web Components Primary Interop Assemblies (PIAs) to be installed, while chapter 13 requires the Authorization Manager (available only on Windows 2003 and Windows 2000 as a separate download) to be installed. To prevent compilation errors because of missing external dependencies, we excluded the source code for these two chapters, the AWReporterWin and AWReporterWeb projects, respectively. Please follow the setup instructions found in the readme files in the sample folders to run these samples successfully.
In case you still experience compilation errors as a result of missing external dependencies, we suggest that you resolve the issue by excluding the samples. For example, let’s say you don’t have Office 2003 and you can’t compile AWReporterWin. To fix this, right-click on the corresponding folder that contains the sample code in the Visual Studio .NET 2003 Solution Explorer and choose the Exclude from Project menu item. Then, compile the project and fix the compilation errors (if any) by commenting out any references to the excluded code.
Microsoft SQL Server 2000 Required by Reporting Services. You will need to install the AdventureWorks2000 database from the RS Setup program.

SOURCE CODE
SETTING UP THE SOURCE CODE

At this point, you should be able to run most of the reports.
Some reports require a more involved setup process. For example, there are reports that require that additional assemblies, such as AWC.RS.Extensions.dll and AWC.RS.Library.dll, be configured properly. The readme files that accompany the sample code include specific step-by-step instructions about how to configure these assemblies. Deploying the reports to the Report Server To run most of the code samples successfully, you need to deploy the sample reports
to the Report Server. Assuming that you have Administrator rights to the report catalog, the easiest way to do this is to follow these steps:

  • Step 1: Copy the AWC.RS.Library.dll and OpenForecast.dll to the Report Server  binary folder, C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin. This step is needed because some reports reference these assemblies, and the deployment process will fail if these assemblies are not found in the Report Server binary folder.
  • Step 2: If you haven’t done this already, copy the AWC.RS.Library.dll and Open-Forecast.dll to the Report Designer binary folder C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer.
  • Step 3: Open the AWReporter.rptproj project (found under the Reports folder) in Visual Studio .NET 2003.
  • Step 4: Right-click the AWReporter project in the Visual Studio .NET Solution Explorer and choose Properties to open the project’s properties.
  • Step 5: Verify that the TargetFolder setting is set to AWReporter and the TargetServer-URL setting is set to http://<servername>/ReportServer, where <servername> is the computer name where the Report Server is installed. If RS is installed locally, the TargetServerURL setting should be http://localhost/ReportServer.
  • Step 6: Click the Configuration Manager button and verify that both the Build and Deploy check boxes are selected for Debug configuration. Click OK to dismiss the Property Pages dialog.
  • Step 7: Right-click the AWReporter project again and choose Deploy. This will build the reports and then deploy them to the report catalog.
  • Step 8: To verify the setup, open the Report Manager web portal. If RS is installed locally, the default Report Manager URL will be http://localhost/reports. Under the Home folder, verify that the AWReporter folder exists. Click its link and run the Sales By Territory report. If everything is okay, the report will render in the browser.

SOURCE CODE
Configuring the AWReporterWeb application

To configure the web-based samples, you need to set up the AWReporterWeb virtual folder by following these steps:

  • Step 1 Right-click the AWReporterWeb folder in Windows Explorer and choose Properties.
  • Step 2 Select the Web Sharing tab.
  • Step 3 Click the Share This Folder radio button.
  • Step 4 In the Edit Alias dialog, enter AWReporterWeb as an alias.
  • Step 5 Make sure that the Read Access Permission check box is selected and the Scripts radio button is selected. Click OK to close the Edit Alias dialog.
  • Step 6 Open the Internet Information Manager (IIS) console. Right-click the AWReporterWeb folder, choose Properties, and then select the Directory
    Security tab. Click the Edit button in the Authentication and Access Control panel. Uncheck the Enable Anonymous Access check box. Make sure that
    the Integrated Windows Authentication check box is selected.

 

RSExplorer Sample Application

 

This sample works only with SQL Server 2008 R2. It will not work with any version of SQL Server earlier than SQL Server 2008 R2.
RSExplorer is a sample Microsoft Windows application developed in Microsoft Visual C# using Microsoft Visual Studio. The application is based on a real-world scenario, and it demonstrates how to develop a Windows application that uses the Reporting Services Web service. RSExplorer uses a Microsoft .NET Framework proxy class to call Web service methods exposed by the Reporting Services SOAP API. It also highlights new features in the area of enterprise reporting. This sample is not supported on Itanium-based operating systems.

Important:

Sample applications should not be connected to or used with your production SQL Server database or your report server without the permission of the system administrator.

Requirements

You should be familiar with Visual Studio and Visual Basic or C#, and you should have installed the following to use the RSExplorer sample:

  • Microsoft Visual Studio 2010.
  • SQL Server 2008 R2, including Reporting Services.
  • Microsoft .NET Framework 2.0.
  • Reporting Services samples. You can choose to install the samples during SQL Server setup. For this release, when you choose to install samples, a separate installer is installed on your computer that you can launch to extract and install samples. You can run this installer from the Start menu in the Microsoft SQL Server program group.
  • A report server that you have permission to access on your network, if you plan to use the sample client application to view the contents of a report server and render reports.

Note:

RSExplorer may be converted to compile and run in previous versions of Visual Studio. However, it is designed specifically to be compatible with the version mentioned in this section.

Important:

SQL Server samples and sample databases must be downloaded and installed before you can view or work with them. For more information, see Considerations for Installing SQL Server Samples and Sample Databases.

Location

This sample is located by default at:
C:\Program Files\Microsoft SQL Server\100\Samples\Reporting Services\Application Samples\Report Search Sample

Building the Sample

If you have not already created a strong name key file, generate the key file using the following instructions.

To generate a strong name key file
  1. Open a Microsoft Visual Studio 2005 command prompt. Click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.– or –Open a Microsoft .NET Framework command prompt. Click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.
  2. Use the change directory command (CD) to change the current directory of the command prompt window to the folder where the samples are installed. Note: To determine the folder where samples are located, click the Start* button, point to All Programs, point to Microsoft SQL Server, point to Documentation and Tutorials, and then click Samples Directory. If the default installation location was used, the samples are located in <system_drive>:\Program Files\Microsoft SQL Server\100\Samples.*
  3. At the command prompt, run the following command to generate the key file: sn -k SampleKey.snkImportant: For more information about the strong-name key pair, see “Security Briefs: Strong Names and Security in the .NET Framework” in the .NET Development Center on MSDN.
To build and run the RSExplorer sample within Visual Studio
  1. Open the solution (RSExplorerCS.sln or RSExplorerVB.sln).
  2. From the Debug menu, click Start Without Debugging.
  3. To exit the application, choose File and then Exit.
To build and run the RSExplorer sample from the Command Line
  1. Use the Change Directory command to change to the RSExplorer directory.
  2. Type the following to build both the C# and Visual Basic versions of the sample: * for /r %f in (*.sln) do C:\WINDOWS\Microsoft.NET\Framework\v2.0.40607\msbuild.exe “%f” * Note: The Windows system path and .NET Framework version number may differ on your system.
  3. In the application’s \Bin\Debug directory, double-click RSExplorer.exe to start the application.
  4. From the File menu, choose Exit to close the application.
Using RSExplorer

After installing the RSExplorer sample application, you can use the sample to view the contents of a report server, add new folders, import reports, edit item properties, and view reports.

Important:

Sample applications should not be connected to or used with your production SQL Server database or your report server without the permission of the system administrator.

To start the RSExplorer sample application
  1. Open RSExplorer.sln in Visual Studio.
  2. On the Debug menu, click Start to run the sample application.
  3. In the Server Address box, enter the virtual root of the report server you want to access for example, http://&lt;localhost>/reportserver, and then click Go. A detailed list of report server items should appear in the Catalog Explorer list view.
  4. Navigate the report server database tree by clicking on folders. You can navigate back by clicking the Up arrow on the toolbar.
  5. Click on any report in the list and click the Show Properties link to view the description and path properties.
To add a folder to the report server database
  1. Navigate to a location in the tree to which you want to add a new folder.
  2. On the File menu, click New, and then click Folder. The Add Folder dialog box opens.
  3. Choose a name and description for the folder, and then click Add.
To import a report
  1. On the File menu, click Import Report. The Import Report File dialog box opens.
  2. Use the dialog box to browse for a Report Definition Language (RDL) file that you want to upload to your report server, and then click Open.
To delete an item from the report server database
  1. Browse to and select an item that you want to delete in the Catalog Explorer list.
  2. On the Edit menu, click Delete. Click Yes to confirm the action.
To copy items in the report server database
  1. Select the item that you want to copy. RSExplorer is limited to copying reports, resources, and data sources.
  2. On the Edit menu, click Copy.
  3. Browse to a folder into which you want to copy the selected item or items, and then, on the Edit menu, click Paste.
To view the properties of a selected item
  1. Select an item in the Catalog Explorer list.
  2. Click the Show Properties link above the right window pane. The properties are displayed.

Các thủ tục, hàm, quy tắc và thiết kế DB Warehouse trong BI và SQL 2008 R2 Enterprise


1. Các toán tử dùng để sửa dữ liệu Null:

– Dùng toán tử IS [NOT] NULL hoặc IS NULL để lọc dữ liệu Null

ví dụ:  

UPDATE [Spanish_CustomsDB].[dbo].[SNUOC] 

SET [Francophone] = ‘X’

WHERE [Francophone] IS NULL

GO

 

2. Chuyển dữ liệu từ font ABC, Vni, BK II hoặc Việt hoá dữ liệu sang phông Font Unicode tiếng việt:

– Dùng hàm thủ tục Procedure hoặc T-SQL viết query kết hợp toán tử Like ‘%’  để lọc

Ví dụ:

UPDATE [Vietnam_CustomsDB].[dbo].[SNUOC]

SET [CHAU] =  N’Châu Phi’

/***** N’Châu Phi’ là font đã được Tôi chuyển thành Unicode Tiếng Việt *******/

WHERE [CHAU] Like ‘%Ch©u Phi%’

/*****   Ch©u Phi là font đã được hải quan nhập bằng ABC  *******/

GO

Cách thêm nhiều máy chủ Database Server trong hệ thống SharePoint Farm 2010


Add a database server to an existing farm (SharePoint Server 2010)

This article provides information about adding a new database server to an existing Microsoft SharePoint Server 2010 farm. You can add additional database servers at any time in response to business or operations requirements. Because the database server contains the farm content, which can consist of diverse types of data and can have a rapidly growing document collection, the size of the farm databases can grow rapidly. Although storage capacity is a key factor driving the need to add additional database servers, other factors are adding new features, performance, and high availability.

Có 3 cách cài thêm máy chủ Database Server vào hệ thống SharePoint Farm Server 2010:

1. Cách 1:  Cài máy chủ SQL Server tuần tự cho tới bước chọn Setup Role: SQL Server PowerPivot for SharePoint

(đây gọi là cách bổ sung tính năng dịch vụ, chia sẻ tải PowerPivot Service cho SharePoint Farm).

image

2. Cách 2: Cài SQL Server 2008 R2 kiểu cluster failover trên Windows cluster hoặc SQL.

(đây gọi là cách cấu hình hạ tầng toàn vẹn, nó cổ điển và phức tạp).

3. Cách 3: Cài máy chủ SQL Server giống hệt máy chủ Database Server đã join trong SharePoint Farm.

(đây gọi là cách cấu hình hiện đại và đơn giản, chia sẻ toàn bộ  hiệu năng, hiệu suất, dịch vụ và cân bằng tải).

Hãy theo các bước dưới đây

noteNote:

In the case of high availability, this is typically implemented as part of the initial farm topology design and deployment; and is not included in this article. For more information about high availability for Microsoft SQL Server 2008, see High Availability Solutions Overview (http://go.microsoft.com/fwlink/p/?LinkId=188016).

In most cases, all that is required to add a database server to an existing SharePoint Server 2010 farm is to set up and configure a new database server and join it to the farm by referencing the new server when you add a feature or move database content from the old server to the new one. SharePoint Server 2010 will automatically allocate and assign new database resources as necessary when they are required.

Prepare the new database server

Before you can use the new database server, you need to prepare it so it can be used in a Microsoft SharePoint Server farm. Use the following steps as guidance for provisioning the new server.

ImportantImportant:

IT policy may require that some or all of the steps given in these procedures be performed by a database administrator (DBA). Before beginning any of the following procedures, ensure that you are complying with your company’s IT policy requirements.

To provision the database server
  1. Review Hardware and software requirements (SharePoint Server 2010)

  2. Install the operating system, ensuring that:

    • The drive configuration is the same as the existing server.

    • The operating system is updated to the same service pack or hotfix level as the existing server.

  3. Install the same version of SQL Server that is installed on the existing farm database server. The procedures in this article are intended to show how to configure a new database server for a specific task in SharePoint Server 2010.

    For information about installing and configuring SQL Server before adding it to an existing server farm, see the SQL Server installation documentation.

  4. Configure SQL Server, confirming the following:

    • The database collation is LATIN1_General_CI_AS_KS_WS.

    • Membership in the SQL Server database dbcreator fixed server role and the Farm Administrators SharePoint group is the minimum requirement to perform the procedures in this article.

    • A login account is created for the SharePoint Server Setup user account. This account will be the database owner for the new database.

  5. Install the same SQL Server service packs and hotfixes that are installed on the existing database server.

Configure and use the new database server

Use the following procedures as a guide for configuring a new database server to host specific SharePoint Server 2010 databases. This includes:

  • Creating a new Web application

  • Moving a site collection to the new server

You can use either Central Administration or Windows PowerShell to create a new Web application; however, you must use Windows PowerShell to move a site collection.

To create a new Web application
  1. Verify that you have the correct credentials on the SharePoint Server farm and on SQL Server.

  2. Use the Application Management page on the Central Administration Web site to create a new Web site.

  3. Configure either classic mode authentication (Windows authentication) or claims-based authentication.

  4. Configure IIS to use either the existing Web site or create a new Web site and configure the following settings:

    • Specify the port number you want to use to access the Web application.

    • Provide the URL you want to use to access the Web application (optional).

    • Provide the path to the site directory on the server where the Web site is hosted.

  5. Configure authentication and encryption for your Web using the following options.

    • Negotiate (Kerberos) or NTLM authentication.

    • Anonymous access to the Web site

    • Secure Sockets Layer (SSL)

  6. Provide a URL for the domain name for all sites that users will access in this Web application.

  7. Use the existing application pool or create a new one.

  8. Configure security for the application pool (pre-defined or configurable).

  9. Identify the database server, database name, and authentication method for your new Web application.

For detailed instructions, see Create a Web application (SharePoint Server 2010).

To move a site collection by using Windows PowerShell
  1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin. Also verify that the user account has access to one of the servers on which Windows PowerShell 2.0 is running, and that the user account is a Farm Administrator and is a member of the SharePoint_Shell_Access role for the SQL Server source content database, administration content database, destination content database, and configuration database.

  2. Verify that the following conditions are true:

    • The destination content database exists.

    • The source content database and destination content database reside on the same instance of SQL Server.

    • The source content database and destination content database are attached to the same Web application.

  3. Determine the size of the source site collection and verify that the destination hard disk has at least three times the free space that is required for the site collection.

    Use the Get-SPSiteAdministration cmdlet to determine the size of a site collection. For more information, see Get-SPSiteAdministration.

  4. Use the Move-SPSite cmdlets to move a site collection from the source content database to the new content database. For more information, see Move-SPSite.

Truncate and Shrink SharePoint Transaction Logs, Databases


Some of the Transaction Logs and databases grown rapidly and on urgent basis I had to truncate the Log files on SharePoint Content Databases. Here is the SQL script to truncate and shrink SharePoint transaction log:

USE <Database_Name>

GO

BACKUP LOG <Database_Name> WITH TRUNCATE_ONLY

DBCC SHRINKFILE(<Transaction_Log_Name>, 1)

GO

After executing the SQL script to truncate transaction log in my SharePoint 2010, it recovered me considerable space now!
Alternatively, You can set the recovery model to “SIMPLE” temporarily, Shrink the Log file and set back to “FULL” recovery model

Use <database_name>

GO

ALTER DATABASE <database_name> SET RECOVERY SIMPLE

GO

DBCC SHRINKFILE (Database_Log_Name, 1);

GO

ALTER DATABASE <database_name> SET RECOVERY FULL

GO

Important:Please note the <database_Name> and <database_log_Name> in above examples are logical names.
Shrink SQL Server Database, Log Files from SSMS:
Shrinking can be done from SQL Server Management Studio also.
Go to SSMS >> Right click the target Database >> Tasks >> Shrink >> Files (or Database)

truncate sharepoint database log

Select the File Type and click on “OK” button to start shrinking. Here I’ve selected “Log” File type to shrink SharePoint transaction log files.

shrink sharepoint content database log

Same applies when you want to shrink SharePoint database size. I find it useful when I had to shrink SharePoint search databases.
How about removing the existing Log file and create a new one?
I had SharePoint Content database log file of 30GB! So, wanted to remove the existing log and create a new one. Simply add a new log file and remove the existing one from SSMS. Or you can:

  1. Detach the database
  2. Rename the log file
  3. Attach the database without the log file
  4. Delete the old log file