The issue is when we save the page using SharePoint Designer, the mapping between the report and the filter was lost.
To add a web site in IIS, right click on Sites in the IIS Manager and select Add Web Site as shown in the below figure.
Then you will get the Add Web Site window as shown below and enter the required details.
Physical Path should be the folder which contains the web site files and it should locate inside the C:\inetpub\wwwroot\wss\VirtualDirectories folder.
Then click Ok to complete it.
But if you try to browse the site, it will give you an error.
To avoid it and browse the web site you need to do two things.
First one is, add the IIS_IUSRS and allow full permission. To do it right click on the site and select Edit Permission as shown in the below figure.
Then go to the Security tab in the new window and add the IIS_IUSRS user and allow Full Control. Then click Ok.
Still you cannot view the page because of an authorization error.
To avoid it you need to do the second thing.
Select the site and you will get a screen as below.
Then double click on Authentication and you can see enabled authentication types. In that Windows Authentication is displayed as Disabled. Right click on it select Enable as shown in below figure.
It will fix the authentication error and you will be able to browse the web site...
We can increase the size of the disk by using Edit Disk under Actions in the Hyper-V Manager.
Then you will get a window as shown below.
Select Expand from it and click Next.
Then add the New Size as shown below and click Next.
Then you will get a Summary as shown below and click Finish to complete it.
But you cannot use that increased space straight away. When you start the Virtual Machine, previous disk size will be avilable. To add the increased capacity, go to the Disk Management window by right clicking Computer and by selecting Manage. Then right click on the partition and select Extend Volume as shown below.
Then you will get the Welcome screen as shown below and click Next.
Then you will get the Extend Volume Wizard. Give the amount to be increased as shown below and click Next.
After that you will get the final screen as shown below.
Click Finish to add that disk space.
We can use Toad to explore the DB2 Database Structures. Before using Toad we need to configure IBM DB2. To configure it open the Configuration Assistant as shown in the below figure.
Then you will get a window as shown below. Select TCP/IP as the Protocol and click Next.
After that we have to provide the details of the Host and the Service.
Insert those information and click on Retrieve button to get the Port Number.
Then follow the instructions as shown in below figures.
Then launch the Toad for DB2 as shown in the below figure.
After that create a new connection right click on the Connection Manager and select Create as in the below figure.
Then you will get a window as shown below.
You can see the available databases in the Database alias.
Then provide login details and the category.
To that we can execute the below query
Then using below query we can verify whether the CDC is enabled for MyDatabase.
SELECT is_cdc_enabled FROM sys.databases WHERE name = 'MyDatabase'
We can see the result of the above query as 1, which is the value for a CDC enabled database. Otherwise it will give the result as 0.
After that we have to enable CDC for each table. To do it we can use the below query to enable CDC for Table1.
@source_schema = N'dbo',
@source_name = N'Table1',
@role_name = NULL
When we enable CDC for a table we can see a table is created under System Tables of the database as cdc.dbo_Table1_CT. Also there are two Table Valued Functions are created as
fn_cdc_get_all_changes_dbo_Table1 and fn_cdc_get_net_changes_dbo_Table1.
Function to Retrieve Change Data
We can write a SQL Function as given below to retrieve change data for a particular period for a particular table.
CREATE function cdc.uf_ChangeData_Table1 (
returns @Table1 table (
declare @from_lsn binary(10), @to_lsn binary(10)
select @from_lsn = sys.fn_cdc_increment_lsn(sys.fn_cdc_map_time_to_lsn('largest less than or equal',@start_time))
select @to_lsn = sys.fn_cdc_get_max_lsn()
insert into @Table1
when 1 then 'D'
when 2 then 'I'
when 4 then 'U'
end as CDC_OPERATION
cdc.fn_cdc_get_net_changes_dbo_Table1(@from_lsn, @to_lsn, 'all')
In this function only the start time is given and get all the change data after that.
Stored Procedure to Execute the Function and Load the Data to the Table
Stored procedure was written to execute the earlier function to get the change data for a particular period and load those data to the destination tab. Stored Procedure is as below and it takes the input parameter as the start time and the required table.
CREATE PROCEDURE dbo.LoadChangedData (
MERGE INTO dbo.Table2 AS dest
USING (SELECT Column1,
Column3 FROM cdc.uf_ChangeData_Table1(@start_time)) AS src
ON (src.Column1=dest.Coulmn1 AND src.Column2=dest.Coulmn2 )
WHEN MATCHED AND (src.CDC_OPERATION='U')
UPDATE SET dest.Column3=src.Column3
WHEN MATCHED AND (src.CDC_OPERATION='D') THEN DELETE
WHEN NOT MATCHED AND (src.CDC_OPERATION='I' OR src.CDC_OPERATION='U') THEN
Column3 ) VALUES (src.Column1,
This stored procedure will do the insert, update or delete operations to the destination table considering the operation type.
Combining With the ETL
We can add a Execute SQL Task to Data Flow after OLEDB Destination component in the Data Flow and Execute the LoadChangedData stored procedure by providing required two parameters. We need to have the start time as the time before inserting the values to the first table. That means before the OLEDB Destination component.
This procedure will load the inserted data to an another table.
There are few modifications to be done for the Cube to load data, otherwise it gives errors while processing due to those null values.
We need to modify some properties in those dimensions (in this example Region and Branch dimensions) and also need to change some settings in the dimension usage.
Double click on one of above dimension (assume DimRegion) in the Analysis Services Project. Then select the key (RegionKey) from Attributes and go to properties. In that expand the KeyColumns property and change the NullProcessing property to UnknownMember. After that you will see Key is underline by red color. To solve that issue, select DimRegion from Attributes and go to properties. In the bottom of that property list you can find a property as UnknownMember. Change the value of that property to Visible.
Then you should change some settings in the Dimension Usage area of the Cube. Double click on the Cube in the Solution Explorer and click on Dimension Usage tab. In that you can see the facts and the dimension used for those facts.
Select the RegionKey and click the button in the Right side to get the Define Relationship window. Click on Advanced button and you will get the Measure Group Bindings window.
Under Relationship change the Null Processing of the RegionKey to UnknownMember. Need to do this for all the facts which uses the dimension.
After that you can process the cube and load data.
The approach I used to create the scorecard is to Import SQL Server Analysis KPIs and after that add some KPIs from PerformancePoint. But the values and the indicators for the KPIs which are created by the PerformancePoint were not shown as shown in the below figure. PPSKPI is the KPI created in PerformancePoint.
I checked the procedure to create that scorecard and KPIs, and found it is ok. Finally I was able to find the reason for that issue. It is a very simple reason.
The reason is that the column names of those two KPI types are different.
For the KPIs which were imported from Analysis Services, those column names are Value and Goal and Status. KPIs which were created from PerformancePoint, those column names are Actual and Target.
When those column names were changed to Value and Goal and Status, the problem was solved...
Server should has
- .Net Framework 3.5
- Internet Information Services (IIS)
- Microsoft SQL Server 2008
- Microsoft Office SharePoint Server 2007
- PerformancePoint Server 2007
To install IIS, go to the Server Manger and then select Add Roles as shown in the below figure.
Then you will get the Add Role Wizard as shown below
Click Next and then you will get the window to select Server Roles.
Put tick in the check box of Web Server (IIS) to install IIS.
Then click Install to install the selected roles.
After that Install the Microsoft SQL Server 2008. Then install the SQL Server 2008 Service Pack 1.
Then Install the Microsoft Office SharePoint Server 2007. In that select the Basic installation and after installation completes, configure it.
Before installing PerformancePoint Server, we need to install Ajax and some patches for SQL Server.
Those patches are list down below (for 64-bit computer).
Finally install the PerformancePoint Server 2007.
Server is ready for the developments related BI applications...
When you try to save a change to a table in the SQL Server Databse, sometimes you will get a error message saying "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created..." as shown in the below figure.
Due to that you will not be able to change database tables.
You can solve that issue with just a one click. You can find Tools -> Options from SQL Server Management Studio and you will get a Window as shown below. Select Design and under that you can find there is a option saying Prevent saving changes that require table re-creation is selected as shown in the below figures.
Just remove the mark of it and try to save your changes. You should be able to save it unless there are no other issues....
In that report we get the values from an SQL Server Analysis Service Cube and get the summation of those values considering the group.
In the Data warehouse we used the data type for the taken field as money.
Finally I was able to find the reason for the error occurs during the summation.
In the cube, we assign the value 0 for the field if it is null. Problem arises due to it.
When we change that to 0.00 instead of 0, it works.
I don't know any exact reason why it happens like that. It may be due to some problems while converting to the data type it may convert it to different data type like int and it gives errors while summation.
Anyway I think this will help you as well...
= RunningValue(Fields!CountColumn.Value, CountDistinct, "MyDatasetName")
To add Row Number to the Details Group, select the field of the Row Number in the Details Group and add the below expression.
Follow the wizard by providing a name and the data source, then you will get a screen to select members for the filter as shown in the below figure.
In the shown figure Filter members are already selected. But you can set the default value for the filter while selecting the filter members. To do it, just select the member value and right click on it. Then you will get a screen as shown below.
Select Set as Default Selection as shown in the above figure and after that you will get a screen as below showing the default selection.
Then just follow the other steps to add the filter...
The problem was when I try to click on a Report publish from the PerformancePoint, it opens the redirected page in the same zone, not in the same window.
Then add the redirect.aspx page (according to my sample) which contains the below code.
Those warning are
- SQL Server Native Client 9.0 (SP2) is missing
- SQL ADOMD.NET 9.0 (SP2) missing
Intalled patches are
I was able to resolve that issue by installing the sqlncli_x64.msi and SQLServer2005_ADOMD_x64.msi from the Feature Pack for Microsoft SQL Server 2005 - December 2008 release in the http://www.microsoft.com/downloads/details.aspx?FamilyID=536fd7d5-013f-49bc-9fc7-77dede4bb075&displaylang=en link.
It is better to install others from this release too...
Those errors occurs due to the security settings provided by the OS.
To solve those issues go to the Server Manager.
Then as shown in the below figure, you can find Configure IE ESC under the Security Information.
Click on it and select Off form those selections as shown in the below figure.
Then click Ok and check your sites.
It will works...
Below diagram shows when data cleansing is required.
To avoid those situations and to have consistent, relevant and accurate data, data cleansing is required.
Stages of the data cleansing process is as shown below.
As shown in the above figure, before data cleansing, Data Quality Testing need to be done.
After that data cleansing is done by parsing, data transformation, duplicate elimination and by doing some statistical analysis.
The final output of the data cleansing process will be accurate, consistent and relevant data.
SQL Server Integration Services(SSIS) is providing the facility to implement data cleansing processes.
There are some components which can be used to perform data cleansing operations provided by SSIS.
- Fuzzy Lookup
- Fuzzy Grouping
Below diagram shows how we can use those components to achieve data cleansing for a given record set.
In that example a simple scenario is considered. For a given sales record set, it checks whether the customer exists. If not it enters the customer record by considering it as a new customer.
There may be situations, that the customer name is misspelled. SSIS Fuzzy Lookup component can be used identify that kind of situations.
Also when new customer records are inserted, duplicate records may exists. To prevent inserting duplicate records, Fuzzy Grouping component can be used.
I'll discuss how Fuzzy Lookup and Fuzzy Grouping works in another post...
The error is Rule "Performance counter registry hive consistency check" failed. The performance counter registry hive is corrupted.
The problem occurred since the installation trying to read the above register value and does a calculation on Last Counter and Last Help.
We can solve this issue by changing the those Last Counter and Last Help values
Type regedit in the Run and press Enter. It will open Registry Editor window.
Open the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009.
Then you will get a screen as shown in below figure.
Double click on Counter record to get a window as shown below.
Then scroll to the second last line and you will see a number like 3422 as shown in the below figure and remember it.
This is the Last Counter number that the system uses.
Then do the same thing for the Help record too and it is the Last Help number that the system uses.
Then go to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib. The screen is shown below.
Double click on Last Counter and you will get a window as below.
Then select Decimal radio button and put the value taken from the earlier Counter record for the Value data field.
Do the same thing for Last Help which is in the same window.
It will solve the above mentioned issue and do the installation again.
You will be able to pass from that System Configuration Check...
The requirement is to refresh an Excel file in the SQL Report Server by running the Macro in the Excel file. In this post, I'll summa...
In SQL Report Subscription, you may want to send the report with the link and you can do it by selecting Include Link value. But some time...
In SQL Server Analysis Services Cube, there may be a situation where we need to combine two related fact tables. This kind of a situation ma...
The URL is not available, does not reference a SharePoint site, or you do not have permission to connect...When I tried to connect to a SharePoint URL in dashboard designer options, it prompt a message saying "The URL is not available, does n...