If database is involved in replication then we have to troubleshoot the performance issue in a little bit different way. We can improve the Merge queries and it will improve the overall system performance for synchronizing data. Identify high IO or high CPU SPs then explore how to tune. Here I have posted the article along with query which will be definitely help out to tune the query

Use Replication Monitor to get overall view of Replication performance. Add –OUTPUT to Merge Agent to identify which step (stored procedure) in the Merge processes is failing or taking longest time.

ReplMerge.log

Merge Replication automatically generates ReplMerge.log file which is ON by default. The minimal default logging may provide enough detail to resolve issue without needing to collect verbose log or Profiler trace.

C:\Program Files\Microsoft SQL Server\90\COM\replmerge.log

sys.dm_exec_query_stats can be used to examine query plans for the Merge Replication created SPs used to evaluate Publisher updates and to move data throughout the Merge topology. Looks for high CPU and high IO queries.

–Find TOP Top LogicalReaders for Merge Replication

–Examine Query Plans for SCANS

–Update STATS and/or REINDEX to see if better query plan is generated

SELECT TOP 25

st.text, qp.query_plan,

(qs.total_logical_reads/qs.execution_count) as avg_logical_reads,

(qs.total_logical_writes/qs.execution_count) as avg_logical_writes,

(qs.total_physical_reads/qs.execution_count) as avg_phys_reads,

qs.*

FROM sys.dm_exec_query_stats as qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp

WHERE st.text like ‘%MSmerge%’

– WHERE st.text like ‘%sp_mergemetadataretentioncleanup%’

– WHERE st.text like ‘%sp_MSenumgenerations90%’

– WHERE st.text like ‘%sp_MSmakegeneration%’

ORDER BY qs.total_logical_reads DESC

Need to review the below system tables:

Often unexpected Synchronization performance can be contributed to unexpected high data volume. After all Merge is just queries against SQL table in order to determine which data to upload/download. Examine Merge system tables for counts 10 million (for example).

SELECT COUNT(*) FROM MSmerge_contents (nolock)

SELECT COUNT(*) FROM MSmerge_tombstone (nolock)

SELECT COUNT(*) FROM MSmerge_genhistory (nolock)

SELECT COUNT(*) FROM MSmerge_partition_groups (nolock)

SELECT COUNT(*) FROM MSmerge_current_partition_mappings (nolock)

SELECT COUNT(*) FROM MSmerge_past_partition_mappings (nolock)

Note: Try updating STATS or REINDEX on key Merge system tables then see if a better query plan (lower IO and CPU) is generated by Query Optimizer.

Replication History Tables

High sync duration could be result of unexpected high data volume. Use query below to see which date had mass amount of changes.

SELECT COUNT(*) ‘generation count’, sum(changecount) ‘change count’,

datepart(dd,coldate) ‘day of month’, datepart(hh,coldate) ‘hour of day’

FROM MSmerge_genhistory (nolock)

group by datepart(dd,coldate), datepart(hh,coldate)

order by 3,4

By: Rajib Kundu

If the master database is corrupted or damaged, SQL Server won’t start. Attempting to start SQL Server will have no effect. Attempting to connect to the instance with Management Studio will invoke a warning that the server does not exist or that access is denied. The only solution is to first rebuild the master database using the command-line setup (as shown next), reapply any SQL Server updates, start SQL Server in single-user mode, and restore the master database.

1. Rebuild the master database using the following command-line setup:

setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=”<instance name>”

/SQLSYSADMINACCOUNTS=”<DomainName\UserName >” /SAPWD=”<password>”

■ setup.exe is either from your original installation media or the ‘‘local’’ setup.exe as found in the 100\Setup Bootstrap\Release directory.

■ The /QUIET switch suppresses all error messages.

■ The /ACTION=REBUILDDATABASE switch rebuilds all the system databases.

■ The /INSTANCENAME switch specifies the name of your SQL Server named instance. Use MSSQLServer for ‘‘<instance_name>’’ for the default instance.

■ The /SQLSYSADMINACCOUNTS switch corresponds to the currently logged in domain user running this rebuild process. The user must be a member of the SQL Server instance’s sysadmin server role.

■ The /SAPWD switch is used to indicate a new SA password if you configured SQL Server for mixed authentication.

2. Run the following from the command prompt to start a default instance of SQL Server in single-user mode:

sqlservr.exe -m

To start a named instance of SQL Server in single-user mode, run the following:

sqlservr.exe -m -s <instancename>

3. Reapply any SQL Server updates, service packs, and hot fixes that were previously applied to the SQL Server.

4. Restore the master database as you would a user database.

Note: Rebuilding the master database rebuilds the msdb and model databases too, so after rebuilding the databases restore the system databases (master, msdb, model) from the most recent good backup.

By: Rajib Kundu

We had a requirement from one of our client where we had to store and pass parameter from one HTML page to another.  We achieved this by simply using HTML/JavaScript. We created two files write.html and read.html, each for writing into a text file and reading from a text file. Below is the sample code for each.

Write.html

<HTML>

<BODY>

<H1>Writing…….</H1>

<Form name=”frmDefault” action=”read.htm” method=”get”>

<p>Enter Your Name:  <Input Type=”text”></p>

<INPUT TYPE=”submit” VALUE=”Write” >

</Form>

<SCRIPT language=”JavaScript”>

function WriteFile()

{

s=document.frmDefault.text1.value;

var fso  = new ActiveXObject(“Scripting.FileSystemObject”);

var fh = fso.CreateTextFile(“c:\\Test1.txt”, true);

fh.WriteLine(s);

fh.Close();

}

</SCRIPT>

</BODY>

</HTML>

Write.html file will accept the input from the user and write into a simple text file. Same time it will redirect the user to the read.html page.

Read.html

<html>

<head>

<script language=”javascript”>

function Read()

{

var Scr  = new ActiveXObject(“Scripting.FileSystemObject”);

var CTF  = Scr .OpenTextFile(“C:\\Test1.txt”, 1, true);

data = CTF .ReadAll();

document.form1.text1.value=data;

CTF .Close();

}

</script>

</head>

<body onLoad=”Read()”>

<H1>Reading……..</H1>

<Form name=”form1″ action=”" method=”get”>

<p>You Have Entered: <Input name=”text1″></p>

<br>

</Form>

</body>

</html>

Read.html file will read the content stored into the text file and display it to user.

By:  Sumeet Kumar

Pentaho allow us to use alternate row color for any report. We can achieve this by using HidePageBandForTableExportFunction. Below are to steps

• Select Report Function in report structure.
• select Available Function->Misc->HidePageBandForTableExportFunction under properties area. Click on Add Function to Report Button. Give the function name.

• After that Item Band option will appear into structure hierarchy. Select Item Band.
• Select Row Banding with desired properties.

• Save and Run the report.

By: Sumeet Kumar

We have upgraded 100 servers to SQL server 2008 R2 last quarter for our existing client because we evaluated that SQL 2008 R2 features can significantly improve the application performance of both SQL Server 2008 R2 and Microsoft Dynamics CRM 4.0 while resulting in space savings.

In addition, deploying these features does not require invasive infrastructure changes, and the features are easily enabled and disabled.

Microsoft SQL Server 2008 R2 contains a variety of features that, when implemented properly, can improve the performance of a Microsoft Dynamics CRM 4.0 implementation .

These Microsoft SQL Server 2008 R2 features include:

Compression

Sparse Columns

Backup Compression

ROW COMPRESSION

ROW compression maps a fixed length data type to variable length physical storage to save space used to store the data; basically, it compresses columns in the row. For example, a CHAR(100) column stored in a variable length storage format only uses up the amount of storage defined by the data. With ROW compression enabled, storing “SQL Server 2008″ in the column requires storing only 15 (not the full 100) characters, representing a savings of 85%. Also, with ROW compression enabled, storing zero or null values requires no storage space. Microsoft SQL Server 2008 R2 supports ROW and PAGE compression for tables and indexes.

PAGE COMPRESSION

A superset of ROW compression, PAGE compression takes into account the redundant data in one or more rows on a page to save space used to

store the data. PAGE compression uses column prefixes and a page level dictionary technique for this task. In other words, with both page

compression techniques, the storage engine reduces the amount of data that is repeated in the page.

SPARSE COLUMNS

_Sparse columns _are ordinary columns that have an optimized storage for NULL values. Sparse columns reduce the space requirements for NULL values at the cost of greater CPU overhead to retrieve not NULL values. Sparse columns enable applications, such as Windows SharePoint Services, to efficiently store and access a large number of user-defined properties by using SQL Server 2008 R2.

INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. Additionally, you can also view and work with all the sparse columns of a table that are combined into a single XML column. This column is called a column set.

BACKUP COMPRESSION

Because a compressed backup is smaller than an uncompressed backup of the same data, compressing a backup typically reduces device I/O and therefore usually increases backup speed significantly.

IMPORTANT: Creating compressed backups is supported only IN SQL SERVER 2008 ENTERPRISE EDITION and later versions, but every edition of SQL Server 2008 and later versions can restore a compressed backup. Also, backup compression is not recommended for use with TDE. Based on your evaluation we come up along with some recommendation

IMPROVING PERFORMANCE BY USING SQL SERVER 2008 R2 COMPRESSION

Columns in Microsoft Dynamics CRM tables are typically sparsely populated, which our analysis of the customer database used in the testing efforts confirmed. Except for certain columns such as Account ID, Name, Phone Number and Email Address, the Account table was sparsely populated. Both row and page compression are very effective on Microsoft Dynamics CRM entity tables, which also contain a lot of binary columns and columns that specify pick list values.

RECOMMENDATIONS

When considering the use of SQL Server 2008 R2 Compression to improve the performance of a Microsoft Dynamics CRM 4.0 implementation, keep in mind the following recommendations:

v Identify the largest tables in the Microsoft Dynamics CRM database and consult SQL Server best practices to pick the best candidates for compression.

v Note: Microsoft Dynamics CRM tables (such as metadata tables) that are frequently accessed or continuously updated may not be good candidates for compression. An example is the PrincipleObjectAccess table which manages the privileges for the Microsoft Dynamics CRM system. In these cases, the performance impact of managing these compressed tables may outweigh the space savings from compression.

v Estimate savings for each table by using the following stored procedure

v sp_estimate_data_compression_savings

v Enable Page Compression on tables with mostly static data.

v Enable Row Compression on entity tables.

v Verify performance impact.

IMPORTANT: Compressing every table in the database may put a lot of load on the computer running SQL Server and compression should be limited to large tables as per SQL Server best practices.

IMPROVING PERFORMANCE BY USING SQL SERVER 2008 R2 SPARSE COLUMNS as mentioned previously, columns in Microsoft Dynamics CRM tables typically are sparsely populated. The sparse columns feature in SQL 2008 is perfectly suited for Microsoft Dynamics CRM tables because it reduces the space required to store data in user-specified columns. In addition, by setting the columns as sparse, Microsoft Dynamics CRM administrators can optimize access to frequently accessed tables in which certain columns are rarely accessed and include all or mostly NULL values.

RECOMMENDATIONS:

Designating a column as sparse is only useful when the column contains mostly NULL values; consider using sparse columns when the space saved is at least 20 percent to 40 percent to strike a balance between space savings and additional CPU overhead. As you work to improve Microsoft Dynamics CRM 4.0 performance by using SQL Server 2008 R2 Sparse Columns, keep in mind the following

RECOMMENDATIONS:

v Identify large tables in the Microsoft Dynamics CRM database.

v Consult SQL Server best practices documented in the article mentioned previously to select the columns to be marked as sparse; typically, use sparse storage for columns in which most values are NULL.

v Note: Marking non-sparse columns as sparse will significantly increase the amount of space needed to store the data.

v Mark the columns as sparse and rebuild the indexes on the table.

v Verify performance impact.

PERFORMING AND MAINTAINING BACKUPS MORE EFFICIENTLY BY USING SQL SERVER 2008 R2 BACKUP COMPRESSION

As expected, results confirmed that enabling Backup Compression yields significant savings in the time required to perform backups and in the space required for storing those backups.

_RESULTS_

MEASURE

UNCOMPRESSED

COMPRESSED

PERCENT CHANGE

BACKUP TIME (MIN)

9:03:09

2:38:51

_-7__0__.__7__5_

BACKUP SIZE (KB)

12299686

2791721

_-7__7__.__0__3_

NOTE: The size of the database used in this test was 15.5 gigabytes.

_RECOMMENDATIONS_

Enable backup compression to increase the efficiency of performing and maintaining backups. However, keep in mind that compression can significantly increase CPU usage and that the additional overhead might adversely impact concurrent operations. As a result, when using backup compression, be sure to verify that the overall system performs at desired levels. Additionally, consider creating low-priority compressed backups in a session whose CPU usage is limited by Resource Governor.

By: Rajib Kundu

Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. Based on the information accumulated we would be able to track the changes to the database, access to the database etc. In this article we have audited if someone runs the DBCC command apart from DBA team and DELETEs issued against an important table.

While we are working with SQL Server 2008 auditing we need to keep four things in mind:

1. SQL Server Audit
2. Server Audit Specification (Events to capture on the Server Instance Level)
3. Database Audit Specification (Events to capture on a specific database)
4. Target (Where would be the events be logged)

SQL Server Audit

The SQL Server Audit object collects a single instance of server or database-level actions and groups of actions to monitor. The audit is at the SQL Server instance level. You can have multiple audits per SQL Server instance. When you define an audit, you specify the location for the output of the result. This is the audit destination. The audit is created in a disabled state, and does not automatically audit any actions. After the audit is enabled, the audit destination receives data from the audit.

Server Audit Specification

The Server Audit Specification object belongs to an audit. You can create one server audit specification per audit, because both are created at the SQL Server instance scope

Database Audit Specification

The Database Audit Specification object also belongs to a SQL Server Audit. You can create one database audit specification per SQL Server database per audit. The database audit specification collects database-level audit actions raised by the Extended Events feature. You can add either audit action groups or audit events to a database audit specification.

Target

The results of an audit are sent to a target, which can be a file, the Windows Security event log, or the Windows Application event log .Logs must be reviewed and archived periodically to make sure that the target has sufficient space to write additional records. Writing to the Windows Security log requires the SQL Server service account to be added to the Generate security audits policy. By default, the Local System, Local Service, and Network Service are part of this policy.

Step Need to follow:

• Audit DBCC usage.
• Audit DELETEs issued against a table.

By: Rajib Kundu

Step-1:    Download the language pack from http://www.microsoft.com/downloads/en/details.aspx?familyid=046F16A9-4BCE-4149-8679-223755560D54&displaylang=en link.

• Download the file by clicking the Download button (above) and saving the file to your hard disk.
• Run the setup program.
• On the Read the Microsoft Software License Terms page, review the terms, select the I accept the terms of this agreement check box, and then click Continue.
• The setup wizard runs and installs the language pack.
• Rerun the SharePoint Products and Technologies Configuration Wizard, using the default settings.

Step-2: Install the language packs

Step-3

Click on Site Actions -> Site Settings-> Site Administration->Language Settings

Step-4:

In the Alternate languages click on the check box to choose the languages that to be available for the site and click OK

Step-5

Open Menu ->Select Language Display Settings -> Select the language

By:  Vinitha

The translate function can be a very powerful function in the SQL coder’s tool belt. Most of us would have already used this in sql’s.

In Oracle/PLSQL, the translate function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. In other words, it converts a string expression character by character using a “from string,” or translation table, with a matching character in the “to string.”

The syntax for the translate function is:

Translate( string1, string_to_replace, replacement_string )

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string – All characters in the string_to_replace will be replaced with the corresponding character in the replacement_string.

For example:

1.    Removing characters/numbers from alphanumeric string using SQL:

In one of the scenario, I want to remove characters from the alphanumeric string. In a short and simple way we can use SQL to achieve this. In PeopleSoft, we can use peoplecode to get the same result, but can assume how big it would end up.

Example: SELECT TRANSLATE ((GRADE) ,’0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz’ ,’0123456789′)) ,’ ‘) FROM JOB;

The above query will convert the following strings in to

We can achieve in reverse way also by removing /separating numbers from alphabets.

2.    Checking date format

In another scenario I want to check the date format. If date is in DD-MON-YYYY or MON-DD-YYYY or any combination with month in words.

Example: DATE_FIELD with the date values like ’02-FEB-12’ or ’02 Fed 2012’ or ‘Feb 02 12’ etc.

As a beginner I can start writing JAN- DEC to check its existence in the DATE field using INSTR function.

1. SELECT ‘X’ FROM TABLE WHERE ((INSTR(DATE_FIELD,’JAN’) >= 1) OR (INSTR(DATE_FIELD,’FEB’) >= 1) OR (INSTR(DATE_FIELD,’MAR’) >= 1) OR (INSTR(DATE_FIELD,’APR’) >= 1))  OR (INSTR(DATE_FIELD,’MAY’) >= 1 OR (INSTR(DATE_FIELD,’JUN’) >= 1 OR (INSTR(DATE_FIELD,’JUL’) >= 1 OR (INSTR(DATE_FIELD,’AUG’) >= 1 OR (INSTR(DATE_FIELD,’SEP’) >= 1 OR (INSTR(DATE_FIELD,’OCT’) >= 1 OR (INSTR(DATE_FIELD,’NOV’) >= 1 OR (INSTR(DATE_FIELD,’DEC’) >= 1″);

In a short we can use the common letters from all the 12 months.

1. SELECT ‘X’ FROM TABLE WHERE ((INSTR(DATE_FIELD,’A') >= 1) OR (INSTR(DATE_FIELD,’E') >= 1) OR (INSTR(DATE_FIELD,’O') >= 1) OR (INSTR(DATE_FIELD,’U') >= 1))”);

Using Translate Function:

Translate function with the combination of other function makes query very simple and short to achieve the same result.

1. SELECT ‘X’ FROM TABLE WHERE INSTR(TRANSLATE(F.DATE_FIELD, ‘AaEeOoUu’,'X’),’X')  >=1 ;

We can compare the first and last query. Last one is even compatible for Case-Insensitive.

3.    Another example from web:

In the sample SQL below, TRANSLATE converts each character found in the DEPTNO column. The third parameter is the from string. When the character in the from string is found the corresponding to string, the value is displayed in the result string. In this example the character “0 through 9″ is converted to an “0″ and “A through Z” results in a space character (‘ ‘). The result is passed up to the outer select, which applies a case statement to return the type of data being stored in the column.

SELECT T.DEPTNO
,CASE T.TYPE
WHEN ’000′ THEN ‘NUMERIC’
WHEN ‘   ‘    THEN ‘ALPHA’
ELSE ‘MIXED’
END TYPE
FROM (
SELECT D.DEPTNO
,TRANSLATE(D.DEPTNO
,’0000000000                                                                     ‘
,’0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ’
) AS TYPE
FROM YDGF450P.DEPARTMENT D
) AS T  ;

The results of this query:
DEPTNO  TYPE
————+———+
A00          MIXED
B01          MIXED
C01          MIXED
D11          MIXED
E01          MIXED
E11          MIXED

XYZ         ALPHA
123          NUMERIC

Another reason you may need to use TRANSLATE is to fix a data processing problem. Maybe the string contains a special character (e.g., $) that must be removed so your application can process correctly. Let’s assume that “$” is in the string and you need to replace it with spaces (‘ ‘).

This use of TRANSLATE would correct this problem.

SELECT TRANSLATE(‘THIS$IS$A$TEST.’,  ‘ ‘,  ‘$’ )
FROM SYSIBM.SYSDUMMY1

Results:    THIS IS A TEST.

Applies To:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

By: Rama Krishna Paladugu

Explanation:

Here is a reference document that serves as a Guide to Defining Sales  Users and Sales Territory Tree. If you need this in the form of a document with screen shots, please  call Global Support Center.

Note: This Comprehensive resolution was written for CRM 8.0 and 8.1  releases, however, the Sales Security applies to CRM 8.4, 8.8 as well as 8.9 releases as well. The navigations for the 8.8 have been added.

I. Introduction to PeopleSoft CRM Sales

********************************************

PeopleSoft CRM Sales enables you to import leads, track opportunities, make forecasts, generate quotes, and track all the tasks you perform along the way. The companion Sales Insight product enables you to analyze the data you collect.

After your system is set up, your organization can get to the business of managing its sales efforts and understanding the effectiveness of its efforts, where your money is being made. First you bring lead data into the system; for example, by referrals, the data entry of business cards you may have obtained at a trade show, or lead lists you may have purchased. Sales representatives will work with those leads to qualify them as prospects or identify whether they should be removed from the system. If the lead is promising, you can click one button to convert it to an opportunity.

PeopleSoft CRM Sales provides a system calendar for tracking tasks. For example, if a prospect asks you to call back in two weeks, you can capture that request so that it appears in your personal calendar.

Depending on your company’s sales cycle, you may need to record sales support activities. Throughout the process of working with opportunities, sales representatives can assign approximate deal value and degrees of confidence that the deals will close.

Management can see how many opportunities are at specified stages in your company’s sales cycle and the approximate value of deals in the works. You can also use qualitative information to assess where your company is succeeding. For example, if, throughout the sales process, you are capturing details about your customers, such as their industry or who your competitors are on specific types of deals, you can readily see where your company is strong and where more resources might be needed.

II. PeopleSoft CRM Sales Security

**************************************

The Security in Sales is two folds.

§ Based on the People Tools security privileges which is common to all other applications § Based on the Sales Access Profile. A user gets rights to access pages and data depending on what permissions (s) he has. This Security is unique to only to Sales Application in the CRM suite.

PeopleSoft CRM Sales allows each User’s security access rights depending on his or her position in the organization. It also gives an ability to have a graphical representation of the structure of your sales organization from a Territory Tree. The Users higher in the tree have rights on the data of Users below them. This Access Profile of the user gives the Forecast Rollup ability. The Forecast of the Users in the Tree (like Sales Reps) rolls up to the Users higher in the Tree (like Managers).

In other words, the security on the Tree goes top to down. For example, let us take tree structure like below:

§ America (Sales Director)

o North America (Sales Manager1)

§ East (Sales Rep1)

§ West (Sales Rep2)

o South America (Sales Manager2)

§ East (Sales Rep3)

§ West (Sales Rep4)

*The Sales Manager1 can see the data of Sales Rep1 and Sales Rep2 below. (Parent can see the data of all its children)  *The Sales Manager1 on North America node would not be able to see the data that Sales Manager2 on South America node can see. (Siblings cannot see each other’s data)  *The Sales Director on America node can see the data of all users on all the nodes on the tree (Person at the top of the tree can see data of all users on the tree)

Some examples of rights that a Parent would enjoy:

*Parent can see the Leads/Opportunity of its children *Parent has the capability to Generate/Submit Forecast of Children (he should have rights set in Access profile) *Parents can View/Update Calendars of its children.

III. PeopleSoft CRM Sales Set up Overview

***********************************************

Getting a user set up to use PeopleSoft CRM Sales involves several steps. The first step in setting up your PeopleSoft CRM Sales system is defining system users and specifying what parts of the application those users can use. This also includes steps to set up Territory Tree since a hierarchy has to be created for the organization.

The major steps in the process are:

· Create Sales Business Units (BU)

· Create a Worker (Person ID) and PeopleSoft User ID for each person that uses the application. These IDs identify the user and enable him or her to log onto the system.

· Create Access Profiles and Create Sales User, which specify what data a user can see, and assign the appropriate profile to each user.

· Set up the Data Distribution Rules (New in 8.4 +) · Define your organizational structure by creating Territory Tree. You associate sales people with each territory in the tree.

IV. Defining PeopleSoft CRM Sales Users

***********************************************

Please Note: these steps provide only additional information to what has been provided in People Books. It assumes that you are familiar with the Sales Application and have followed People Books. These steps tell about the field that are most importantly taken care during Set Up and need you to follow PeopleBooks along the way.

For details, please refer below path in PeopleBooks:

Home >PeopleBooks Library >PeopleSoft 8 CRM Sales PeopleBook  >PeopleSoft CRM Sales.

Or,

Home > PeopleBooks Library > PeopleSoft Sales 8.8 SP1 PeopleBook

1. Define Sales Business Unit

Navigate:

(8.0) Home>Define Business Rules > Establish Business Units > Use > SFA  Definition

(8.8) Set Up CRM> Business Unit Related> Sales Definition

- Add new value or use existing one

While creating Business Units, associate them to the right SetID depending on your Organizational Structure.

Please refer resolutions, 709860 and 712509 for some information on SetId and BU.

2. Define Worker

Navigate to:

(8.0) Home > Manage Workforce > Manage Workforce > Use > Worker

(8.8) Workforce> Worker

-Add new value or use existing worker.

-Fill in the First Name/ Last Name and other relevant fields.

-Go to Work tab and fill the mandatory fields. Associate the SetID, Department, Location and Job code for the Employee. These are just for informational purposes and are not used in Sales Security.

-Save and note the PersonID was created for this Person/Worker

3. Define User Id

User Id is the User Login for a user.

Navigate to: Home>PeopleTools > Maintain Security > Use > User Profiles – Add new value or use existing UserID for the worker you have in Step#1. (Naming convention used in Demo data is First Letter of First Name and Last Name in UpperCase)  -Set password (In Demo data Password is same as UserId in UpperCase)  -To point User Profile to Worker and give them a Role – Got ‘ID’ tab and associate PersonID that you created in Step #2.

Choose ID Type as Person.

-GoTo ‘Role’ tab and associate the appropriate Role for the User. The assigned role determines what parts (Menus and Pages) of the system the user has access to.

4. Define Access Profile

Access profile defines what data the person has access to – for example, which leads and which opportunities he has access to.

Navigate to:

(8.0) Home>Define Business Rules> Structure Sales Force> Use> Access Profiles

(8.8) Set Up CRM> Product Related> Sales> Security and Personalization> Sales Access Profiles

-Add new value or use existing profile. Example, add for the permission for ‘Field Sales Rep’

-Select all permissions that have to be given to the user (for Leads,

Opportunity…)

There were many more Access Profile options in 8.0, in 8.4 and 8.8 many of them are transferred to Data Distribution Rules and Roles.

Navigation:

Enterprize Components> Component Configuration> Data sets> Dataset Rules Enterprize Components> Component Configuration> Data sets> Dataset Roles

Note:

Please refer PeopleBooks to see the details of each Access Option and Data Distribution Rules.

5. Define Sales Users

Here you associate Person (defined in Step #2) with an Access Profile (defined in Step#4).

Navigate to:

(8.0) Home>Define Business Rules> Structure Sales Force> Use> Sales Users

(8.8) Set Up CRM> Product Related> Sales> Security and Personalization> Sales Users

-Add new value or use existing Sales User. The Person Id you need to specify is the same you defined in Step #2 Sales User Detail Page:

This page provides details about each sales user: their job category, access profile, and quota information.

-Set Business Unit for the Sales User.

-Associate the Access Profile

Active/Inactive flag decides whether the Sales User will appear on the Tree and on the Prompts for Sales User. If an Inactive Sales User has a User Login, (s) he can still login and access her/his data.

Sales user Visibility Page:

This page specifies which territory or territories this user can view data from. It gives only the Visibility rights to the users who are not in the tree hierarchy but needs to view data (for example Financial Controller etc). This does not give the Sales User rights to any Sales Process.

-Add the tree node to the Territory Visibility section (optional)

V. Defining Your Organizational Structure – Territory Tree

**************************************************************

Detailed steps of Create/Set up Territory Tree 1. Define business units.

You have already created them in previous section.

2. Define the items that distinguish territories from one another.

Different sales organizations use different criteria to divide their sales activities: by product line, by geographic region, by customer, or by industry. Before you build your territory tree, you must define the items that distinguish one territory from another; for example, if your territories are based on customers, you must first define the customers.

Which pages you use to define these items depends on which criteria you are using. You can access any of these pages from the Territory Management Home page

(8.0) Home > Manage Sales > Manage Sales Territories > Use > Territory Management Home

(8.8) Sales> Territories> Territory Management Home

3. Define New Territory Tree

Navigate to:

(8.0) Home > Manage Sales > Manage Sales Territories > Use > Create Territory Tree

(8.8) Sales> Territories> Create Territory Tree

(8.8) Sales> Territories> Territory Management Home> Create Territory Tree

-Create Root node and Build the tree structure by specifying the hierarchical relationships between the territories.

To edit the tree or the nodes,

Navigate to:

(8.0) Home > Manage Sales > Manage Territories > Use > View/Edit Territory Tree

(8.8) Sales> Territories> View/Edit Territory Tree

4. Add Sales Users and Managers to the Tree nodes To do this click on the node, this will show various icons.

Click on the Edit Data icon (pencil icon) and this will allow you to add all the information on the node.

Please refer to PeopleBooks for details of each icon.

5. Run Sales Access Update

Sales Access Update process combines User identities with Access permissions.

You need to run Access Update (Application Engine prog) to make the Sales Security come in effect. Make sure you run this first time after creating the Tree and every time after making any change on the Tree.

General term that is used for running Sales Access Update and having the Tree come in Effect – Flatten the Tree.

In CRM 8.0 and 8.1 releases, the application engine program picks up the most current Effective Dated Tree. If you want to flatten an old tree, copy the tree (Save As), change its date then run the sales access update on it.

In CRM 8.4 onwards, a tree can be selected to run the Sales Access Update.

To Run Sales Access Update Navigate to:

(8.0)

Home>Define Business Rules> Structure Sales Force> Process> Sales Access Update Home>Define Business Rules> Structure Sales Force> Use> Sales Users, Visibility Tab.

(8.8)

Sales> Territories> Territory Management Home> Sales Access Update Set Up CRM> Product Related> Sales> Security and Personalization> Sales Access Update Set Up CRM> Product Related> Sales> Security and Personalization> Sales Users, Visibility Tab

VI. Test the Set Up

**********************

Once you are done with this set up for several Sales Users having different Access Profiles, you need to verify if the Sales Access Update is in effect now or not.

Here is a small test:

- Login as a Sales User.

- Create some Leads or Opportunity and make sure they are assigned to the Sales User Logged in.

- Now Login as this Sales User’s Manager, a person above him on the tree and has Manager permissions on his Access Profile.

- Go to the Lead/opportunity List page

Home > Manage Sales > Manage Sales Opportunities > Use > Opportunity

- Check Filter Option – My Staff’s Leads/Opportunities

- The manager should be able to see the Sales User’s Lead/Opportunities.

By:  Amit Vaishampayan

Cache: The cache option specifies how many sequence values will be stored in memory for faster access.

No Cache: The No Cache option specifies that none of the sequence values are stored in.

Caching sequences is especially important for high-DML applications with lots on insert/update statement.

How Cache Improves Performance?

Cache defines how many values of the sequence the database pre allocates and keeps in System Global Area for faster access.

This means it can age out of the shared pool in the same way as a procedure if it is not accessed frequently enough.

The “cache” clause caches the specified number of sequence values into the buffers in the SGA. This helps in speedy access avoiding frequent read/write in the disk.

To illustrate this concept, consider the following example where I explained by inserting values using sequence with cache and without cache in sequence which proves the performance improvement when we use Sequence with Cache.

Explaining with an example:

Creating a table:

1. Created a table named Cachperf.

Creating Sequence with Cache:

Created a sequence with cache value

Inserting  50 k records:

It took 7 seconds to insert 50k records with cache option.

Altered Sequence from Cache to Nocache:

Truncated the table and reinserting the values with nocache:

To insert same amount of records with no cache option, it took 24 seconds which is high comparing to with cache option.

By:  Siva Sankar