Xu hướng Business Intelligence 2012

By: dotrunghieu on 10:39 AM

Xu hướng Business Intelligent 2012

BI đã bắt đầu xuất hiện ở Việt Nam trong những năm gần đây. SAP, Oracle, IBM là những đại gia đầu tiên đặt chân vào Việt Nam và cũng nằm trong nhóm Big boys của thế giới.

BI đã xuất hiện ở các nước phát triển có khi đến hàng chục năm nay, hiện tại ở Việt Nam có lẽ nó cũng chưa mang lại hiệu quả đúng như kỳ vọng mà nó đã làm được như ở nước ngoài.

Bài viết này dựa trên những nhận định của Tableau software (một BI vender), những nhận định này của Tableau nói đến xu hướng BI nói chung (không phải Việt Nam), nào cùng xem BI của thế giới đã tiến đến đâu.

BI trong năm 2012 sẽ thế nào?

1.       Big data get even bigger. – Dữ liệu ngày càng khổng lồ

Đó là thực tế, các tổ chức đã qua cái thời lưu trữ dữ liệu mà giờ đã đến cái đoạn xử dụng dữ liệu để phục vụ mục đích kinh doanh, hỗ trợ ra quyết định. Dữ liệu càng ngày càng lớn, việc xử lý ngày càng phức tạp. Các vender cần giải quyết bài toán big data, bài toán về performance của hệ thống BI, ad-hoc solution, in-memory analytic.

2.       Seft-services:  IT cung cấp cho người nghiệp vụ môi trường đầy ắp thông tin, công cụ để họ tự tạo ra các báo cáo, các thông tin quan trọng.

3.       BI mobile: Các thiết bị di động ngày càng phát triển, ngày càng nhiều các ứng dụng được viết cho di động. Tại sao BI lại không? Các lãnh đạo có thể có thông tin điều hành doanh nghiệp ngay trên ipad của họ. Apple tuyên bố trong năm 2012, có đến 92% doanh nghiệp trong top 500 sẽ dùng thử hoặc triển khai ứng dụng trên ipad.

4.       Social BI: Mạng xã hội cũng đang phát triển mạnh mẽ, đó là một xã hội “thật”, người ta mua bán trên mạng xã hội, người ta comment về sản phẩm trên mạng xã hội, ca ngợi sản phẩm này, ném đá sản phẩm kia. Sử dụng dữ liệu từ mạng sẽ hội sẽ giúp cho các nhà kinh doanh có những dự đoán chính xác hơn.

5.       BI Cloud: Cuối cùng vẫn là làm sao TCO thấp, Cloud giúp cho doanh nghiệp giảm chi phí, đưa các ứng dụng lên cloud là xu hướng. và BI cũng không ngoại lệ.

6.       BI jobs: Ngày càng nhiều các job IT đòi hỏi kỹ năng analytics.

7.       Interactive data visualization: Đó là yêu cầu với BI, người dùng cần có công cụ để khai thác dữ liệu ra những thông tin dễ hiểu, thành những biểu đồ, dashboard, đồ thị xu hướng …

Nguồn : 10 business intelligent trend – tableau software.

Read More

Cannot copy file from host machine to vmware machine

By: dotrunghieu on 4:44 PM

When you cannot copy file form host machine to vmware machine, may be vmware tool was not installed. Now, you have to install vmware tool. From the VM menu, click VMware tool Installation.
Unfortunately, you have an other error:"VMware Tools installation cannot be started manually while the easy install is in progress". To solve this problem, you shutdown the machine, re-add the floppy and CD/DVD devices. Next, you start machine and re-install vmware tool.

Read More

Oracle ADF 11g Official Tutorial

By: dotrunghieu on 3:48 PM

This is useful tutorial for Oracle ADF beginer to get started.
This tutorial shows you how to build a rich Web application that interacts with a database. You'll be using Oracle JDeveloper 11g Version 11.1.2.0.0 and the Oracle ADF framework to build the application, and in the process you'll work with Oracle ADF Business Components, Oracle ADF Faces Rich Client Components and Oracle ADF Task Flows.
http://docs.oracle.com/cd/E18941_01/tutorials/jdtut_11r2_55/jdtut_11r2_55_1.html

Read More

Using help when creating formulas in Crystal report 2008

By: dotrunghieu on 3:17 PM

There are many function in Crystal report. How to use them correctly is a challenge. Using help and auto complete is 2 tips.

1. Using help

• Enter help by pressing F1 or clicking the ? button on the formula editor.
• Select the index tab.
• Type the keyword functions or operators
• Scroll down the list of functions or operators and chose the function or operator that you want to examine.

2. Using auto complete in formula editor

• When enter a name of function or operator, type the few first letter then press Ctrl + Space . Crystal report will compare the letter you typed with list of keyword. If there is only one match, it auto complete for you. If there are some match, you select the one that you want to use.

Read More

Run SSIS package in a stored procedure

By: dotrunghieu on 10:56 AM

There are 3 ways to run SSIS package:
- in an other ssis package
- in a stored procedure
- in batch file
When I test the second way, (run a ssis package in a stored procedure)
- I create a SSIS package named Load_VPB_Customer
- I create a procedure to call  this package
The code for a stored procedure here:

CREATE  PROCEDURE [dbo].[ExePakageLoadCustomer1]
AS
BEGIN
       SET NOCOUNT ON;
    Declare @cmd varchar(1000),
            @p_File_Path varchar(1000),
            @p_File_name varchar(100)
    set @p_File_Path='D:\ETL\TestLoadCustomer\TestLoadCustomer\'
    set @p_File_name='Load_VPB_Customer.dtsx'
    select @cmd =  'dtexec /F "' + @p_File_Path + @p_File_name + '"'
    exec master..xp_cmdshell @cmd
   END

 When executed   the stored procedure I encountered with some error about encription user key. Like that: "   Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that..."
And the solution is:
- change property of ssis package:ProtectionLevel default is EncryptSensitiveWithUserKey





 You change to EncryptAllWithPassword
And type value for pakagePassword: (my password is admin123)
Rebuild the package
- The next step is changing code for the stored procedure.
Modify the code that call ssis packag:
select @cmd =  'dtexec /DECRYPT "admin123" /F "' + @p_File_Path + @p_File_name + '"'
- Rerun the package and I work.

Read More

Installing SAP Business Objects 4.0

By: dotrunghieu on 3:13 PM

 source: http://www.maxima.co.uk/about-us/maxima-blog/item/installing-sap-business-objects-40

Installing SAP Business Objects 4.0

Added by Stuart Wallace, Posted in Maxima Blog, Business Intelligence Blog

The new SAP Business Objects 4.0 Enterprise Platform is finally available so it's perhaps useful to run through the basic installation process. The documentation from SAP is, of course, comprehensive but this blog is aiming to distil that down to the basics.  Warning- there are a lot of very similar screen shots in here.

Let's start with a few notes about this installation:- This is a single server install. Enterprise deployments are, of course, supported whereby you can install specific services on specific servers to introduce load balancing and availability features. - This is a Custom Install rather than a Full Install only because you see more of what is happening during the Custom Install and it's useful to illustrate exactly what is involved in the Full Install.
- The install is on Windows 2008 64 bit server (only 64 bit is supported for the Enterprise Install) with 14GB spare memory
The CMS and Audit databases required by earlier versions of SAP Business Objects are still required so a core pre-requisite is to make sure you have decided where these are to be hosted. The install package includes a SQL Server Express database (how long till this becomes Sybase I wonder?) but I want to take advantage of Analysis Services reporting in 4.0 so have opted to first install the full SQL Server Enterprise product. I then manually create the two databases required, taking a note of the details for use during the install process. With SQL Server I also have to make sure that the ODBC connections are configured before beginning the install.
Another required decision before installation is which web server to use. Most are supported but, as with previous releases, I'm opting to take advantage of the Tomcat deployment delivered as part of the installation.
I've been installing Business Objects software since the original version 4 and it's always something that has improved with each new release. That's maintained with this version though one suggestion I would make is that it might be quite nice to have an indicative progress bar as you progress through the install option screens – sometimes it felt like they were never going to end and light at the end of the tunnel would have been welcomed!
So, we start in the time honoured tradition by clicking on the setup.exe. We get to select our install language and then the process proper begins. The install package first does a fairly useful pre-req check.

This looks a bit scary at first until you realise there is a scroll at the bottom which, if used, shows you've passed them all – or not, as the case may be. In my case, I'd read the documentation beforehand and had. Next up is the licence agreement, name, organisation and product key code details. All fairly standard. Following that is the option to choose different language packs depending on the internationalisation of your environment. I'll stick with English.
Then we have the first important choice – what sort of install are we doing? A full install, custom install or web tier only install.

I'm choosing the Custom Install which starts off with everything which is in the Full Install selected so is a good way to view what's included there. In fact it is pretty much everything. The only features not included in the Full Install are the ERP integrators as shown below.

Also interesting to note by reviewing the features be installed the various additions that have come along in 4.0 e.g. Subversion for their new Lifecycle Management processes and the SAP Support Packages allowing a greater degree of online support should you require it. The next option requires you to specify whether this is a genuinely new install (requiring a CMS) or an extension of an existing deployment (where a CMS is already available on the network)

Following this we have the screens requiring information about our CMS and Audit database. Note that these would not appear if I'd selected the option to have an Integrated Database (SQL Server Express) installed when customising the features. First we select which database we want to create our CMS and Audit schemas on.

Next it's time to specify the SIA name and node. As I'm only doing a simple install on a single machine with no software requiring the default ports I'm happy to click Next on this...

...and, indeed, on the CMS port.

The SIA and CMS ports do become important when deploying on a networked environment – especially where firewalls are involved and the ports have to be opened. As with the earlier XI releases the ports for individual services can be managed within the CCM. You are then prompted to supply the Administrator password. Take a note of this – you'll need it for your first login to the system. You also get to specify a Cluster Key which would be used to configure multi-server deployments (a blog for a later day perhaps?)

Next we supply the connection details for our SQL Server – once for the CMS and once for the Audit database. Note that the available ODBC connections are provided as a list.

We then can select to start the servers immediately after installation before specifying our Tomcat parameters – again, with no other network considerations, I go with the defaults.

We then get into the more esoteric elements of the 4.0 configuration. I've chosen to install the Web Application Container Services (WACS). This basically allows a .NET environment to deploy the Java apps that come as standard with SAP Business Objects e.g. the CMC. I keep with the default port option for WACS and select Next. Now for the Subversion configuration. This will certainly reduce the ongoing maintenance costs of a complex SAP Business Objects deployment allowing the quick and easy reapplication of previous configurations, etc.... Again, I keep the default parameters and supply my own Subversion Repository password.

After the Subversion config we are presented with the option to configure SAP Solution Manager Diagnostics. As I understand it, this will allow SAP to access your system if required and assist in any troubleshooting, support cases, etc... For now, I'm not going to configure that.
 
And finally – yes, we're almost installing – comes the option to configure the C A Wily Introscope.

The Introscope is something I'd though Jon Pertwee's Doctor Who had dispensed with back in the 1970s but, no, it's actually also part of the enhanced SAP support ecosystem and entirely optional for my purposes so I'm avoiding the config for now.
That's the last step done and I'm now ready to start the install. On my machine it took just under two hours to complete. I'd specified for the services to all start after install and a check on the CCM confirms they are there.

One final check of course is the actual business of logging in to the new SAP Business Objects portal.
 
That works successfully and I have a new, clean SAP Business Objects 4.0 platform to play with.
In conclusion then, I'd certainly say that SAP Business Objects 4.0 is one of the simplest installs I've had to do for a BI system.  Of course, I was hardly stretching it's enterprise capabilities but to get a demo/Proof of Value environment up and running it's straigthforward enough.

Read More

Slowly Changing Dimension - SQL Server - Sử dụng lệnh Merge

By: dotrunghieu on 12:10 AM

Design Tip #107 Using the SQL MERGE Statement for Slowly Changing Dimension Processing
By Warren Thornthwaite
 
Most ETL tools provide some functionality for handling slowly changing dimensions.  Every so often,
when the tool isn’t performing as needed, the ETL developer will use the database to identify new and
changed rows, and apply the appropriate inserts and updates.  I’ve shown examples of this code in
the Data Warehouse Lifecycle in Depth class using standard INSERT and UPDATE statements.  A
few months ago, my friend Stuart Ozer suggested the new MERGE command in SQL Server 2008
might be more efficient, both from a code and an execution perspective.  His reference to a blog by
Chad Boyd on MSSQLTips.com gave me some pointers on how it works. MERGE is a combination
INSERT, UPDATE and DELETE that provides significant control over what happens in each clause.

This example handles a simple customer dimension with two attributes: first name and last name. 
We are going to treat first name as a Type 1 and last name as a Type 2.  Remember, Type 1 is where
we handle a change in a dimension attribute by overwriting the old value with the new value; Type 2
is where we track history by adding a new row that becomes effective when the new value appears.

Step 1: Overwrite the Type 1 Changes
I tried to get the entire example working in a single MERGE statement, but the function is
deterministic and only allows one update statement, so I had to use a separate MERGE for the Type
1 updates.  This could also be handled with an update statement since Type 1 is an update by
definition.
 
MERGE INTO  dbo.Customer_Master AS CM
USING Customer_Source AS CS
ON (CM.Source_Cust_ID = CS.Source_Cust_ID) 
WHEN MATCHED AND -- Update all existing rows for Type 1 changes 
            CM.First_Name <> CS.First_Name
            THEN UPDATE SET CM.First_Name = CS.First_Name

This is a simple version of the MERGE syntax that says merge the Customer_Source table into the
Customer_Master dimension by joining on the business key, and update all matched rows where
First_Name in the master table does not equal the First_Name in the source table.

Step 2: Handle the Type 2 Changes
Now we’ll do a second MERGE statement to handle the Type 2 changes.
This is where things get a little tricky because there are several steps involved in tracking Type 2
changes.  Our code will need to:
1.  Insert brand new customer rows with the appropriate effective and end dates
2.  Expire the old rows for those rows that have a Type 2 attribute change by setting the
appropriate end date and current_row flag = ‘n’
3.  Insert the changed Type 2 rows with the appropriate effective and end dates and current_row
flag = ‘y’
The problem with this is it’s one too many steps for the MERGE syntax to handle.  Fortunately, the
MERGE can stream its output to a subsequent process.  We’ll use this to do the final insert of the
changed Type 2 rows by INSERTing  into the Customer_Master table using a SELECT from the
MERGE results.  This sounds like a convoluted way around the problem, but it has the advantage of
only needing to find the Type 2 changed rows once, and then using them multiple times. 

The code starts with the outer INSERT and SELECT clause to handle the changed row inserts at the
end of the MERGE statement.  This has to come first because the MERGE is nested inside the
INSERT.  The code includes several references to getdate; the code presumes the change was
effective yesterday (getdate()-1) which means the prior version would be expired the day before
(getdate()-2). Finally, following the code, there are comments that refer to the line numbers 
 
1  INSERT INTO Customer_Master
2  SELECT Source_Cust_ID, First_Name, Last_Name, Eff_Date, End_Date, Current_Flag
3  FROM   
4         (  MERGE Customer_Master    CM      
5             USING Customer_Source    CS
6            ON (CM.Source_Cust_ID = CS.Source_Cust_ID) 
7           WHEN NOT MATCHED THEN 
8              INSERT VALUES (CS.Source_Cust_ID, CS.First_Name, CS.Last_Name,                 
convert(char(10), getdate()-1, 101), '12/31/2199', 'y')
9         WHEN MATCHED AND CM.Current_Flag = 'y'
10              AND   (CM.Last_Name <> CS.Last_Name )  THEN
11                   UPDATE SET CM.Current_Flag = 'n', CM.End_date = convert(char(10), getdate()-2, 101)
12         OUTPUT $Action Action_Out, CS.Source_Cust_ID, CS.First_Name, CS.Last_Name,
convert(char(10), getdate()-1, 101) Eff_Date, '12/31/2199' End_Date, 'y'Current_Flag
13         ) AS MERGE_OUT        
14  WHERE  MERGE_OUT.Action_Out = 'UPDATE'; 
 
Code Comments
Lines 1-3 set up a typical INSERT statement.  What we will end up inserting are the new values of the
Type 2 rows that have changed. 

Line 4 is the beginning of the MERGE statement which ends at line 13.  The MERGE statement has
an OUTPUT clause that will stream the results of the MERGE out to the calling function.  This syntax
defines a common table expression, essentially a temporary table in the FROM clause, called
MERGE_OUT.

Lines 4-6 instruct the MERGE to load Customer_Source data into the Customer_Master dimension
table.

Line 7 says when there is no match on the business key, we must have a new customer, so  Line 8
does the INSERT.  You could parameterize the effective date instead of assuming yesterday’s date.

Lines 9 and 10 identify a subset of the rows with matching business keys, specifically, where it’s the
current row in the Customer_Master AND any one of the Type 2 columns is different.


Line 11 expires the old current row in the Customer_Master by setting the end date and current row
flag to ‘n’.

Line 12 is the OUTPUT clause which identifies what attributes will be output from the MERGE, if any. 
This is what will feed into the outer INSERT statement.  The $Action is a MERGE function that tells us
what part of the MERGE each row came from.  Note that the OUTPUT can draw from both the source
and the master.  In this case, we are outputting source attributes because they contain the new Type
2 values.

Line 14 limits the output row set to only the rows that were updated in Customer_Master.  These
correspond to the expired rows in Line 11, but we output the current values from Customer_Source in
Line 12.

Summary
The big advantage of the MERGE statement is being able to handle multiple actions in a single pass
of the data sets, rather than requiring multiple passes with separate inserts and updates.  A well
tuned optimizer could handle this extremely efficiently.

Read More