Snowflake Schema Design

If we did not de-normalize our dimensions into one table each, then the schema would look like Fig. 1

You can see, this looks like a snow flake, hence this type of schema is called Snowflake Schema. General rule of thumb is keep away from snow flake schemas as even though they may save you some space, they will cost a lot in terms of query time.

Star Schema

Let us discuss Star Schema with respect to an example: -

If you carefully look at our new dimensional modeled shcema, it will look like the one shown in Fig 1. You can easily tell, this looks like a STAR. Hence it is also known as Star Schema.

Advantages of Star Schema

1. Star Schema is very easy to unerstand, even for non-technical business managers.

2. Star Schema provides better performance and smaller query times.

3. Star Schema is easily extensible and will handle future changes easily.

A typical SQL Query Template for the Sales Schema will look like: -

----- Select the measurements that you want to aggregate unsing SUM Clause

SELECT P.Name, SUM(F.Sales)

--- JOIN the FACT table with Dimension Tables

FROM Sales F, Time T, Product P, Location L

WHERE F.TM_Dim_Id = T.Dim_Id AND F.PR_Dim_Id = P.Dim_Id AND F.LOC_Dim_Id = L.Dim_Id

--- Constrains the Dimension Attributes

AND T.Month ='Jan' AND T.Year='2003' AND L.Country_Name='USA'

--- finally the 'group by' clause Identifies the aggregation level. In this example you are aggregating all sales within a category

GROUP BY P.Category

Cognos8: New data does not appear even when the query is rerun

This is due to internal caching which is a default setting in Cognos 8.
Caching options can be set in three places:
1. Properties of the query in Report Studio.
2. Properties of the model in Framework Manager (under edit Governors)
3. Global cache set which is set as follows:
a) Go to the \Cognos\C8\configuration directory.
b) Make a copy of the CQEConfig.xml.sample file.
c) Rename the above file to CQEConfig.xml.
d) Open the file for editing.
e) About half way through the file there is an entry name called queryReuse. Change the value to 0.
f) Restart the Cognos8 Service

Accessing Windows Scheduler using VB Code

Option Explicit
' Schedule api's
Declare Function NetScheduleJobAdd Lib "netapi32.dll" _(ByVal Servername As String, Buffer As Any, Jobid As Long) As Long

' Schedule structure
Type AT_INFO
JobTime As Long
DaysOfMonth As Long
DaysOfWeek As Byte
Flags As Byte
dummy As Integer
Command As String
End Type

' Schedule constants
Const JOB_RUN_PERIODICALLY = &H1
Const JOB_NONINTERACTIVE = &H10
Const NERR_Success = 0

Private Sub Command1_Click()
Dim lngWin32apiResultCode As Long
Dim strComputerName As String
Dim lngJobID As Long
Dim udtAtInfo As AT_INFO

' Convert the computer name to unicode
strComputerName = StrConv(Text1.Text, vbUnicode)

' Setup the tasks parameters
SetStructValue udtAtInfo

' Schedule the task
lngWin32apiResultCode = NetScheduleJobAdd(strComputerName, udtAtInfo, lngJobID)

' Check if the task was scheduled
If lngWin32apiResultCode = NERR_Success Then
MsgBox "Task" & lngJobID & " has been scheduled."
End If
End Sub

Private Sub SetStructValue(udtAtInfo As AT_INFO)
Dim strTime As String
Dim strDate() As String
Dim vntWeek() As Variant
Dim intCounter As Integer
Dim intWeekCounter As Integer

vntWeek = Array("M", "T", "W", "TH", "F", "S", "SU")

With udtAtInfo
' Change the format of the time
strTime = Format(Text2.Text, "hh:mm")

' Change the time to one used by the api
.JobTime = (Hour(strTime) * 3600 + Minute(strTime) * 60) * 1000

' Set the Date parameters
If Val(Text3.Text) > 0 Then
' Set the task to run on specific days of the month i.e. 9th & 22nd of the month
strDate = Split(Text3.Text, ",")
For intCounter = 0 To UBound(strDate)
.DaysOfMonth = .DaysOfMonth + 2 ^ (strDate(intCounter) - 1)
Next
Else
' Set the task to run on sepecific days of the week i.e. Monday & Thursday
strDate = Split(Text3.Text, ",")
For intCounter = 0 To UBound(strDate)
For intWeekCounter = 0 To UBound(vntWeek)
If UCase(strDate(intCounter)) = vntWeek(intWeekCounter) Then
.DaysOfWeek = .DaysOfWeek + 2 ^ intWeekCounter
Exit For
End If
Next
Next
End If

' Set the interactive property
If Check1.Value = vbUnchecked Then
.Flags = .Flags Or JOB_NONINTERACTIVE
End If

' Set to run periodically
If Option2.Value = True Then
.Flags = .Flags Or JOB_RUN_PERIODICALLY
End If

' Set the command to run
.Command = StrConv(Text4.Text, vbUnicode)
End With
End Sub

Steps to Restore Contributor application to a different database and application name

To accomplish this, first back up the Contributor application then restore the database using SQL Enterprise Manager. Restore it to a new database name, then link to new application within the Contributor Admin console.

Follow these steps to restore a Contributor Application to a new database name:
1. From the Contributor Admin Console, Expand the application you want to backup, expand Development, expand Datastore options, then select Datastore Maintenance.
2. Make a note of the location that you're backing up your files to, and then click Backup.
3. Launch SQL Enterprise Manager and browse to Databases.
4. Right-click Database and select 'All tasks', then 'Restore database'.
5. In the Restore as Database field, type the new name for the database (make certain there are no uppercase characters in the name).
6. In the Restore section click From Device, click Select Devices, click Add.
7. In the File name section browse to the location where you saved your backup database.
8. Select the .dat file that was saved in step 2, then click OK, click OK again, then click OK again.
9. Go to the Options tab and in the Move to physical filename field, type the path where you want to restore the new database to. Do the same for both the log file and the dat file.
10. Go to the General tab, select View Contents and make sure you check off the latest backup set date. (The date should correspond to the date when you performed your backup in step 2.)
11. Click OK, and click OK again. The SQL Restore is complete.
12. Launch Contributor Admin Console, right-click your Datastore server and select Link to Existing Application.
13. You should see the Application name of the new database you just restored, check that off and select Add.

Recommended to set Hyper-threading off when configuring the Contributor Web/Application

Contributor: Applies to all version

According to the documentation from Intel, the hyper-threading concept was designed to improve performance of software that uses an average of 30% CPU cycles at any given time. Hence you can effectively push two threads through the CPU for an average of 60% CPU utilization, plus some overhead, increasing the software's performance by 100%.

Contributor was designed with performance in mind, so that when you publish, initialize, and/or synchronize, the application uses 100% of the available CPU cycles.

For applications like Contributor that already maximize the CPU processes, when you attempt to push two 100% CPU utilization threads through each processor you do not receive any improvement in performance. You actually get a decrease in performance since there is some overhead to the hyper-threading processing that gets added to the 200% utilization. If we assume an additional 2% for overhead, you end up attempting to utilize 202% of the CPU cycles available. Thus, Contributor processes will run faster with hyper-threading turned off.

Hyper-threading can be setted off from BIOS Settings. You can take the help of IT Person or Network person for this task.

Bursting a Report Using SDK and Save the Output to the Local File System

When the report runs with the burst option, multiple outputs are being created in a single run. The Java code for this technique – will extract all of the outputs and store them in a local file system in HTML format.You can also change the option to extract just the latest output version of the report outputs.
For this example, we will be using the "GO Sales and Retailers" sample database to create the report with the burst key.
Follow these steps to complete the technique:
Create a new report in Report Studio. Set the Burst Options and execute at least once. Do not delete the report outputs.
Modify the value of the endpoint variable if you are not using the default gateway:
String endpoint = "http://localhost/crn/cgi-bin/cognos.cgi";
Modify the savePath variable to where the output will be saved:

String savePath = "C:\\temp\\";

Modify the reportName variable to the report that will run:

String reportName = "Banded Report";
Modify the latestVersion variable to true if you want to retrieve the latest version of the report outputs; otherwise, set it to false to retrieve all versions of the report outputs:
boolean latestVersion = true;
Modify the username, password, and namespaceID variables to the correct login information:
String userName = "nameSpaceID";
String passWord = "userName";
String nameSpaceID = "passWord";
Compile and execute the script. Information about the Cognos ReportNet Java Toolkit can be found in Chapter 1 of the ReportNet SDK Getting Started Guide, under the ReportNet Java Toolkit section.
Check from your save path to see if the report outputs are being generated.

What is SOX ?

SOX - Sarbanes-Oxley

The Sarbanes-Oxley Act of 2002 protects investors by improving the accuracy and reliability of corporate disclosures made pursuant to the securities laws. One of the most significant provisions within Sarbanes-Oxley are the criminal and civil penalties that place executive management and the board of directors in the “hot seat.” Specifically, under Section 404 of the Sarbanes-Oxley Act, executives need to certify and demonstrate that they have established and are maintaining an adequate internal control structure and procedures for financial reporting.

Objectives to meet Sarbanes-Oxley compliance
Sarbanes-Oxley requires a new level of corporate governance and accountability. As a result, the vital role security information and event management (SIEM) plays in establishing and maintaining internal controls have never been greater. Companies must institute log monitoring and vulnerability assessments as a critical part of their IT internal control systems. Both domestic and international publicly-traded companies must comply with Sarbanes-Oxley. If you are a covered entity you must have methods to maintain audit trails and to log possible altering of electronic records. Network Intelligence has mapped best practices and reports to help organizations comply with audits under Sarbanes-Oxley Section 404.

For more details check http://www.network-intelligence.com/solutions/compliance/regulations/sarbanesoxley.asp

Redirecting from Contributor Application Help

Sometimes it is desirable to redirect users to other web-resources, when they click on the help button for a given cube.
For example:
- Place a instruction on a sales forecast cube telling users to click help to view a report showing previous years sales.
- Link to a Capex model that may feed the expenses model so that users can review input that has been feed in using admin or system links.

This allows the administrator the ability to use a web-authoring tool, such as MS FrontPage, to create a central company-themed site for documentation and easily link to it, rather than re-creating pages each time. Also, this
provided the added benefit of reducing the model size, as none of the help text needs to be included in the model definition.

Steps

1. Go to any Contributor Application --> Development --> Configuration --> Contributor Help Text
2. For each cube for which you wish to provide a help link that redirects to another page, enter the following tag in the Detailed Cube Help:
<meta http-equiv="refresh" content="0; URL=http://webserver/site/page.htm">

This meta tag is instructing the browser to refresh the page. The “content=” tag tells the browser how long to wait (in seconds) before refreshing the page. To redirect immediately, without the user noticing anything, leave this
value at 0.
The “URL=tag” tells the browser where you want to redirect to. If you have a specific page in mind (like a report, for example) you can cut and paste the URL into this location

For more details on various HTML Tags check the following website: -
http://www.w3schools.com/

Publish Failure Error in Cognos Planning 7.3 and 8.1

Error Message: -
Publish fails with bulk load error and no .out file is present. Installing Oracle Client and executing sqlldr.exe is successful.
Unable to Bulk Load the data.
BulkLoad Execution returned 1.Output file (C:\DOCUME~1\cognos\Local Settings\Temp\CBE61F5133084ED8AEFA2C22B10E00A9et_forecast__machiE282F3CBD61140398D4F0873872C1B1C{6B5305F0-D569-4B3D-8E0B-DB818AF5F6A9}.out)

Solution:
If you are using Windows 2003, there may be an issue with using the Disable8dot3 setting.

Look at the error message as displayed above or open the .cmd file in a text editor. If the path contains a space such as "c:\docume~1\cognos\local settings\temp" SQL*Loader will fail with a syntax error, even though the path is enclosed in quotes.

Set your temp directory to a path that contains no spaces such as c:\temp. Modify the temp settings for the user account assigned to the COM+ component to point to a path with no spaces in Environment Variables, then set this path in Configuration Manager under Cognos Planning>Shared. Apply your configuration, then stop and start services

For more details check Cognos support site http://support.cognos.com/kb-app/knowledgebase?document_search_show_document=1&document_id=1012528&version_id=1