Framework Issues

In Go sales model order detail and order header are combined to make Sales model query subject .where sales acts as a fact .You will notice that the order header does not have any fact data alone .but when combined with order detail it becomes a fact .

By making it sales fact the model is simplified .The model represents a star schema and is much easier to track. Its difficult in actual scenario to find such a group of tables but worth looking for .

Similary in 1 to n relationship either represent fact or hierarchies are represented.Consider the case of product line , product type ,product they follow 1 to n in a line .

In the screenshot below notice that if you drag item from product brand and product type there are two path for the query this causes ambiguity . So it is solved by removing the join between product brand and sales target .In the other diagram 2 you will notice a new query item products is created which is joined to on product type code to sales target and product brand code to product brand .This new object product is combination of product line ,product type ,product .So actually we are limiting the join of sales target with product only based on product type and the direct join based on product brand does not exists .

The loop is solved in below screenshot.Notice that Products on (right hand corner ) is different from Product at center

Diagram 2




Now notice that the products that is there is a model query subject .so whats the idea behind this??

Above issue is solved below diagram 

Scenario if you drag sales and branch there are two paths that exists that is taken care below

Notice that the sales target that is connected to product type is actually a model query subject that is joined to product type base on product type code.

So its a good place to understand the difference between using model query subjects and creating join to them v/s using a shortcut

As per IBM cognos 10 Help file

1) Shortcut are easy to maintain and are automatically updated if the table structure changes
2) Shortcut are exact replicas and do not allow addition of columns whereas if you create model query subjects you can add or remove columns from query subjects.
3) Creating shortcut does not create a copy of the joins.
4)However if you directly create a copy of query subject it will create a copy of all the joins of that query subject(In our example we have created a new model query subject and not a copy of query subject)
.Its gives just for knowledge purpose.

Point to Ponder -- How will the join form if we take data from sales target and product type .Will the join differ based on among the two which sales target we choose ??

Even in case of shortcut how will the join that is formed decided on?? It depends on where you pick the data from which sales fact


The product forecast and sales also forms a loop we will look below 

As you can see below the retailer type is made by combining retailer and retailer type .Its actually a model query subject,So by creating a join with model query subject we have solved the loop by ensuring that whatever way cognos decides to choose for the loop the joins remain same .Better explained below with products example 

Notice that products is a combination of product type,product line and product .Its between sales target and sales .Now this is a loop as you can also travel from produt type , product and sales.(product line is hidden in diagram dont worry) .So actual any path it takes the join remain the same and so the outputwill be same and hence the loop is solved .

By creating products between sales target and product forecast we have ensured that the joins remain the same for both paths of the loop.

Please refer this diagram for my question 

Some basic java script

Hi Guys ,

Some basic java script

1) Take a text item

2)Put HTML items on both left and right of it

3) Start <div > tag on left and end on right HTML

4)Put the code in the left HTML

5)Note -Always try to write small pieces of code in java save them in notepad and then use this as parts later .Its difficult to track errors can be something very small .This will save you a lot of time.

<script language="javascript" type="text/javascript">
function f_aler()
{
  alert("this is a function call");
}
</script>
<div id="btn1"  onclick="f_aler();" >
<p>"this is text to click" </p>
</div>

Below is some script that will allow you to click on column title and assign value to prompt.Can be helpful for dynamic column sorting

<script language="javascript" type="text/javascript">
function f_aler(id)
{
  alert(id) ;
var form = getFormWarpRequest();
       var textB  = form._textEditBoxABC;
       textB.value = "Hello";
}
</script>
<div id="btn1"  onclick="f_aler(id);" >
</div>

For below code you require two text box prompts .For one of the prompt set the default value to asc

<script language="javascript" type="text/javascript">
function f_aler(id)
{
  alert(id) ;
var form = getFormWarpRequest();
       var textB  = form._textEditBoxABC;
       textB.value = "Hello";
       var textc  = form._textEditBoxascsel;
        if (textc.value =="asc")
       {
           textc.value ="dsc"; 
        }
      else
      {
         textc.value ="asc";
       }      
  
}
</script>
<div id="btn1"  onclick="f_aler(id);" >
</div>

Using Stored procedure in Cognos with Oracle

Hi Guys ,

Below is a stored procedure in oracle that i am using in cognos to return the output.Its a simple procedure to know basic of how to use procedure in oracle

CREATE OR REPLACE
PROCEDURE get_emp_rs (prod_line IN varchar2 ,
p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
SELECT product_line_code
FROM sls_product_line_lookup
WHERE product_line_en = prod_line;

END get_emp_rs;

Note - Though the procedure is returning the out parameter ,There is no need to define out parameter in framework while creating query subject from stored procedure .Cognos automatically detects the out parameter .


I have supplied the value camping equipement so that some output is returned .Usually this value will be passed from the prompts . Like a value prompt parameter .

Note -Here in oracle you will have to use a cursor to return the data not like ms sql where we just write a select statement .Notice that i have kept the curson open have not closed it .

stitched query and some questions

1) If you notice that query is joining on rsum then we know that its not correct
2) For a stitched query to work correctly both the facts should have one column in common , without that the stitched query that is formed will be wrongly joined
3) if you notice a query joined on RSUM you can put a filter and make one of the table that cognos is considering fact a non fact, this has to be so because if the table was a fact adn joined by confirmed dimension it will have one column that is common
4) combine the table which is acting as a fact into a single one .The way to do is i guess use a single folder put both the talbe in
the folder delete any joins to the tables and put a single join condition in the folder this make cognos treat this table as a
single table and join is always included ??

Not quite sure about this need to try --- refer page 329 cognos 10 user guide

order header and order method are put in a folder in busineess layer that is a relationship is created between their model query subjecsts .This will cause it to function as a view . so when you pick anything from any one of the tables the join between both the tables is taken into consideration and they function as a single table so they wont act as a fact but a single table .


If you create a model query subject and put the both teh tables into this query subject or you create a folder what is the differnece
in both these approaches and how the query formation will be different.

Folder creation has no effect on the query generation however if you create a join on model query subject it will override teh minimised sql property .
If you create model query subject which is a combination of differnet data source query subjects like in case of product line product type etc.This will still preserve your minimised sql property .Table will be joined only when required.

How to resolve fact less query issue ???? -------- by creating a star schema grouping ? what is other way of solving this ??
How to make a snowflaked schema into star schema???

What are stitched queries

Are they formed when two facts are joined on confirmed dimension .
If instead of two confirmed dimension and two facts in a framework we change teh relationship to 1 to 1 from 1 to n it will give rise to a loop . and how do we resolve the loop .How cognos automatically solves the loop .( Cognos will drop the last join that formed teh loop )

what woule happen if there was a loop in any sql statement ??? depends on data if the data keeps changing then the sql will run for long time as a = b =c =a
depending on c again a will change that will cause b to change and so on .

The standard example that we see that is two facts and two dimension for stiched query will fail in case of factless query so we need to go for star schema grouping .

How cogons ensures that the joins are made on lowest level of granularity .. consider the example of one fact on month grain and othe on day .How will the joine handled to take care of the granularity ?? (Note that result are always executed on lowest common level of granularity in this case that is month)

If a query subject participates in both 1 and n relationship its called ambiquosly defined

One case when we can see that a query subject is ambiquosly defined when it reprents a part of hierarchy.so in such case examine how the query is formed and what does cognos treats as a fact and what does it represents as a dimension.

------------How does the query join change when you join a dimensiotn to a fact .Does it represetn a left outer join or will it be a inner join
 .

How does a the query changes on addition of a non confimred dimension to multi fact multi grain query


what are governor setting and how does it help you to improve model performance??

What is a dispatcher

First you need to understand How a Web application works

1) Client(Web browser) and server interact through Http and Html.
2) Browser understands HTML and can display it on the screen
3) Web browser makes a Http request and gets Http response
3) So it either makes a Get or Post request. Get request is visible in browser
4) In Case of Static web pages. Get request directly tells us the location of the page
5) In case of Dynamic content web server forward request to Web application or CGI common gateway interface
6) In CGI performance can be a issue due to one process per request
7) For enabling Dynamic content we have Java classes called servlets which can accept Http request and Give Http response
8) A servlet will have Get method inside it which tells it what to do based on the request

Routing in IBM Cognos BI

Routing? Why?

IBM Cognos BI is based on a Service Oriented Architecture (SOA). This implies the product consists of a set of independent services which communicate via SOAP over a network. There are many different services which all implement different features of the product. Each service can only serve a certain type of request. This is one of the routing challenges in an SOA, to route a request to a service which can serve this type of request.

Another aspect of the routing challenges in an SOA is load balancing and/or fail-over. There can be multiple instances of the same type of service in an overall system. If there are multiple instances, then either load balancing (each instances get's assigned a configurable percentage of the requests of that type) or fail-over (requests for a certain type of service get re-routed to an active instance because another one failed) can happen.

The Dispatcher

IBM Cognos BI addresses both routing challenges by a software component called the Dispatcher. The Dispatcher, technically, is a Java Servlet which implies it handles HTML input and generates HTML output. In the case of IBM Cognos BI, the input and output payload is actually using the Simple Object Access Protocol (SOAP) which again, technically, is XML payload transported over the HTTP protocol.

Each Dispatcher hosts a set of services which are determined by the system components installed in this instance of IBM Cognos BI. The services get registered to the Dispatcher and it's the Dispatcher which controls them. At the same time the Dispatcher “knows” which service instances it hosts and hence which types of requests it can serve locally.

When a Dispatcher is started up, it will register itself with the active Content Manager (CM). It will report the services it hosts and will obtain information about the system from CM. Through this process, each Dispatcher gathers information about all other Dispatchers in the system and the services they host.

While a simple single server environment may be sufficient for testing, production systems usually consist of several installed instances, sometimes called nodes, each running a Dispatcher with it's own set of services registered. With multiple nodes, load-balancing and fail-over become possible. The IBM Cognos BI architecture implements this by a logical bus which exists between the Dispatchers on each node. On this logical bus requests get passed/routed between Dispatchers in a system and eventually to a specific service instance registered with one of the Dispatchers. This process will acknowledge load balancing and service availability as will be explained during the course of this document.

Clients send requests destined for a certain service to a Dispatcher to get them served. The Dispatchers of a system will ensure the request is routed to an available instance of the requested service which will handle the request and relay back the result to the client.

Entry Point Considerations

Because the Dispatcher is a Java Servlet, it must be deployed to a Servlet Container/Java Application Server which typically is located in the Application Tier of a classical 3-tier architecture. It's considered a potential risk to expose the Application Tier to external (e.g. from an uncontrolled or less controlled environment) clients for direct access, thus as a best practice, some other component in the web tier like a web server is used to handle the communication with the external clients. Only dedicated controlled and secured communication paths between Web Tier servers and Application Tier servers are allowed.

Entry Point Considerations

Because the Dispatcher is a Java Servlet, it must be deployed to a Servlet Container/Java Application Server which typically is located in the Application Tier of a classical 3-tier architecture. It's considered a potential risk to expose the Application Tier to external (e.g. from an uncontrolled or less controlled environment) clients for direct access, thus as a best practice, some other component in the web tier like a web server is used to handle the communication with the external clients. Only dedicated controlled and secured communication paths between Web Tier servers and Application Tier servers are allowed.

The IBM Cognos BI architecture supports this approach by supplying the IBM Cognos BI Gateway which exists in 6 different implementations (CGI, MOD, MOD2, MOD2_2, ISAPI) and an additional Servlet implementation. Typical installations use Gateway(s) to act as the entry point to the IBM Cognos BI system. The Gateway component, though, does nothing to the request but relay it to the first available Dispatcher in it's configured list of Dispatchers, it can be perceived as a proxy. The Servlet Gateway is a special implementation of a Gateway in such that it has to be deployed to a Java Application server or Servlet Container, typically located in the Application Tier. It is used for setups where other software is used to proxy requests from the Web Tier to the Application Tier. One example are Application Server Plug-Ins deployed to web servers for this particular purpose like they exist for IBM WebSphere or JBOSS.

The Gateway does not route, it's using a static connection to the first available Dispatcher in its configuration, which only changes if the first configured Dispatcher is not reachable. Only in this case the Gateway will try to forward the request to the second Dispatcher in its configured list of Dispatchers and so forth. That being said, routing only starts whenever a given request hits an IBM Cognos BI Dispatcher for the very first time. The Dispatcher which initially receives a client request is called the FRONT Dispatcher for this request. This becomes important for several specific request flows involved with authentication (refer Appendix A).

Technically though, it doesn't make a difference what the entry point is: a Dispatcher or a Gateway. This is remarkable because it implies that one can use application server features like web server plug-ins which proxy requests received in the web tier to the application tier in the same manner as the IBM Cognos BI Gateway does.

For the rest of the document no differentiation between Dispatcher or Gateway is made, whenever required it will reference to an entry point to simplify things.

The dispatcher starts all IBM Cognos 8 services configured and enabled on a computer, and routes requests. The dispatcher is a multithreaded application that uses one or more threads per request. Configuration changes are routinely communicated to all running dispatchers. The dispatcher includes IBM Cognos Application Firewall to provide security for IBM Cognos 8. For more information,

Each Dispatcher hosts a set of services which are determined by the system components installed in this instance of IBM Cognos BI. The services are registered to the Dispatcher and its the Dispatcher which controls them. At the same time the Dispatcher “knows” which service instances it hosts and hence which types of requests it can serve locally.

 The dispatcher is the entry point for IBM Cognos 8 service requests sent by a Web server gateway or other software. The dispatcher handles the routing requests and balances the load of user requests to the various IBM Cognos 8 services.

You can have more than one dispatcher in your IBM Cognos 8 environment. In such distributed installations one dispatcher is configured for every instance of the Content Manager or Application Tier Components that are installed and configured in your environment.

After you install and configure IBM Cognos 8, one dispatcher is available on each computer by default. Each dispatcher has a set of associated services, listed in the following table.


http://www.ibm.com/developerworks/data/library/cognos/infrastructure/cognos_specific/page510.html

Both routing challenges are addressed by a software component called the Dispatcher. The Dispatcher, technically, is a servlet which handles HTML input and generates HTML output. In the case of IBM Cognos BI, the input and output payload will actually be SOAP (XML via HTTP).


An application programming interface (API) is a source code-based specification intended to be used as an interface by software components to communicate with each other


A Servlet is a Java class in Java EE that conforms to the Java Servlet API, a protocol by which a Java class may respond to requests


Because the Dispatcher is a servlet, it must be deployed to a Servlet Container/Java Application Server, which implies it is located in the Application tier of a classical 3-tier architecture. It's is considered a potential risk to expose the application tier to end users, so usually as a best practice, some other component in the web tier like a web server is used to handle the communication with the external clients.


In the IBM Cognos BI architecture, this is done by the IBM Cognos BI Gateway which exists in 6 different implementations (CGI, MOD, MOD2, MOD2_2, ISAPI) and an additional servlet implementation. Typical installations use single or multiple Gateways to act as the entry point to the IBM Cognos BI system. The Gateway component, though, does nothing to the request but relay it to the first available Dispatcher in it's configured list of dispatchers. The Gateway does not route, it's a static link which only changes if the configured Dispatcher is not reachable. Only in this case the Gateway will try to forward the request to the second dispatcher in its list and so forth. That being said, routing only starts whenever a request reaches a dispatcher for the first time. The Dispatcher which first receives a request is called the FRONT Dispatcher. This is important for several specific request flows involved with authentication (refer Appendix A).

Technically though, it doesn't make a difference what the entry point is: a Dispatcher or a Gateway. This is remarkable because it implies that one can use application server features like web server plug-ins which proxy requests received in the web tier to the application tier in the same manner as the IBM Cognos BI Gateway does


How application server interacts with web server

http://www.theserverside.com/feature/Understanding-How-the-Application-Servers-Web-Container-Works

When a client makes a request for a JSP or a Servlet, the request initially goes to the Web server. The Web server reads the special XML file the application server provides, and realizes that the request that came in should be sent to the application server for processing.

The special XML file also provides the IP address/port combination of listening application servers. The Web server, using the http protocol, then sends the request to the Application server JVM listening on the appropriate port.

The JVM listening on the appropriate port represents our application server, and the port the JVM listens on can be configured through that JVM’s Web container.

The Web server handles the incoming request, and matches that request to the application server set up to handle the given Servlet or JSP.

CGI AND ISAPI FOR COGNOS10

Hi

CGI Common gateway interface and ISAPI - internet server application program interface are both ways in which a web server interacts with user .Like when you log in the CGI takes your info and does the processing .Java servlets are also similar .

CGI – opens up one process for each user so lot of process making it slower .Thats why they came up with ISAPI which has single process so faster .

The Internet Server Application Programming Interface (ISAPI) model was developed as a faster alternative to the Common Gateway Interface (CGI). ISAPI provides a number of advantages over CGI, including lower overhead, faster loading, and better scalability. The chief difference between the CGI and ISAPI programming models is how processing is handled.With CGI, the system creates a unique process for every request. Each time an HTTP server receives a request, it initiates a new process. Because the operating system must maintain all these processes, CGI requires many of resources. This inherent limitation makes it difficult to develop responsive Internet applications with CGI.With ISAPI, requests do not require a separate process. Threads are used to isolate and synchronize work items, resulting in a more efficient use of system resources. For more information, see ISAPI and Web Application Architecture.Multithreaded applications deliver their potent power by running many threads concurrently within a single program. From a logical point of view, multithreading means multiple lines of a single program can be executed at the same time, however, it is not the same as starting a program twice and saying that there are multiple lines of a program being executed at the same time. In this case, the operating system is treating the programs as two separate and distinct processes. Under Unix, forking a process creates a child process with a different address space for both code and data. However, fork() creates a lot of overhead for the operating system, making it a very CPU-intensive operation. By starting a thread instead, an efficient path of execution is created while still sharing the original data area from the parent. The idea of sharing the data area is very beneficial, but brings up some areas of concern that we’ll discuss later

I got the below screenshot from How stuff work > CGI .Below is the link .Please go through if you require additional information on working of CGI
http://computer.howstuffworks.com/cgi.htm

USER PERMISSIONS IN COGNOS 10

Im Cognos Roles and Group are parent for all permissions.Read,Write,execute,set policy,Traverse
After that comes public folder after that folders and packages.At each level you can overide
permission from parent

Roles have override access permissions.Who are parent for a roles??

I dont know.I think system administrator is parent for all.
In properties of a folder there are 3 options.General,Permissions,Capabilities
consumer has traverse access can only see general tab

Question is with traverse option why you can open report studio.Though traverse
ppl can see report studio they cannot execute is.The option for executing does not come it.Now
how to hide report studio for those
users — can it be done through capability

what is execute permission .What impact does it have— You can open report
studio but you cant create report.I think you can only execute reports.Now main
idea is to hide even the report studio option for those ppl
Grant allow external data property is not set in capabilities.Need to enable this.

When you publish a package.By default is has capabilities.That come from parent.Now you are
directly publishing the package in public folder.Who is the parent here??

Public folders has properties.Its one of the tabs that gets overlooked very easily.
How to assign capabilities to a role.Saying that consumers can use report studio.How to do this??

Ans- Capabilities can be set in public folder properties or they can be set in package or folder
properties.You can override permissions from parent entry.Setting it in public folders or
packager or folder properties does it.

When next to report you can see report studio icons,run button.That is due to permission
read,execute.If you have permission however your group does not have report studio capability you
cannot open report in report studio.
Go to properties , go to report studio.Click on properties.Here you can say which group can use
this capability.However deny has higher precedence over grant.So even if someone is included in
everyone group you can deny access by including him in other group and denying that group access.

Every group has properties?There are permissions there.What does those permissions mean??

Group properties , permission are used for specifying permission for the group that will be used
throught cognos when the group is mentioned.At any time we can override this permission.Most
common place to override group permissions are public folder properties.
—
Every capability has property.Those property say who has right to that thing.Like report studio c
capability has permissions .Suppose you deny any group execute and traverse right .They cannot
even see report studio icon.

But you can still see the report studio in launch option.How to handle this??How to hide cognos report studio option in launch explorer.?

Resolving the problem

On the Report Studio capability, set deny on the traverse and execute rights.Steps:Assuming users
belong to a group called for example, ReportStudioNoAccess
1. Log in as administrator in the Cognos Connection web portal
2. Click on Tools->Capabilities menu option (8.2 or lower). In 8.3, go to Launch->Cognos
Administration, click on the Security tab and then click on Capabilities link.
3. In 8.2, click on the Report Studio “Set Properties” icon and then the Permissions tab. In
8.3, click on the triangle to the right of the Report Studio capability, choose Set Properties
and click the Permissions tab.
4. From the Set properties – Report Studio page, add the ReportStudioNoAccess group and deny
them execute and traverse rights.
5. Click OK to save the settings
6. Log in as a user that belongs to the group that you denied execute/transverse rights to and
ensure they cannot see the report studio link next to reports or in the upper right of the Cognos
Connection page.

UNDERSTANDING GROUPS AND CAPABILITIES

Hi Guys,

Things discussed below

1)Groups and roles

2) Capabilities.

3)Read,write,traverse,set policy options

Note:-Below are list of Container Objects Folders,Packages,groups,roles and namespaces
What are Different permissions available?

1) Read
You can view all the properties of a entry and create a shortcut to that entry.
If you want to open a report in report studio you require read permission.
It allows you to copy a entry.

2) Write
You can delete a entry.
Make chagnes to it.

3)Execute
Means user can run a report.But cant open it in report studio unless he has read.

4)Set Policy
You can change security settings for a entry.Without set policy you cannot see security settings
of a entry
If person has set policy permission for a entry he can take ownership of the entry

5)Traverse
If a user has traverse permission to a package then he can see the reports in that package
traverse is must if you want to go inside a package or a folder
What is the differnece between write and execute??

If you do not have traverse option for parent entry container , you cannont access the entries below it

Most access permission are aquired from parent entries.If you do not explicitly specify properties for a entry.They are taken from parent entries.You can define permissions for entries which are different from parent permissions.

Trusted Credentials -Must be created when you want other ppl to use your credentials to certain
tasks because they do not have their own credential.I have never tried creating these.

Intial Security– When you first install Cognos

System administrator has access to all .Initially Group everyone is added to system adminstrator
that is the reason everyone can see administrative tasks.

Every person if he is there in any group or not there in anygroup he is automatically there in
everyone group.

Consider below Case:-

Now you want to create 3 roles

Developer,

Business user,

Advanced business user.

You create that group and add it to any existing role.According to capability and permission set
for that role the permissions of that group will be set.

Now what do you mean by setting capabilities for a role and assigning properties for a container.What is the difference???

What are capabilities?What do they allow you to do?

Example..You can specify if a user has read permission to reports in a package.Now this is
possible because user is part of a group and that group has read permissions to that report.Now
capabilities specify what a group can do once they are inside report studio.That is called
capability.
Below is the list of some report studio capabilities
1)Allow external data
2)Bursting
3)Usign HTML Items in report
4)Use Userdefined sql in reports

What are different roles in cognos administration and what capabilities they have??

Roles by default have the capabilities.Like suppose you have report administrator role and you
try to give only traverse permission to that role.By default it has capability to go inside and
change the role.

Notice that when you add a role some properties are set by default and you cant change them.If
you add report administator to report properties.It automatically assumes property of set policy.

Report adminstrator role capability does not allow it to see security tab.

How to show package only for limited groups and hide for all the others??

Just add the group that should have see the package simple.

HOW TO BUILD FRAMEWORK MODEL FLAWELESSLY

Hi Guys,

This is useful for those who have some idea how to create a model and are aware of all the terminologies used in cognos framework model.I have build 4 professional Framework models till now,However i always find it a challange when it comes to data correctness of the model.Below are the points that i have writen for my guidance going forward while building FM.

1) Import all the tables that you are going to use to build your model first.FM is the best place to analyse relationship.Keep option to auto detect relationship ON.

2) Whenever a key from one table is not used as foreign key in another table .Cognos treats them as Fact and dimension and creates a 1 to n relationship.

3) Understand what your project is all about first.Get a person from business to give you overall idea of business.Do some research on wikipedia to find how things work.Terminologies used.I believe its a waste to start building model without you having some proper idea of business. Once you have idea you know what you are trying to report.Going forward report development will be piece of cake for you and your team.

4) Understand the granularity by using FM diagrams .Make good use of hide dependencies option.

5) Get some key data that you are trying to output in a excel first.This will gurantee you that the hierarchies  that you are building are giving proper data.This is very important as in End mostly data issue comes. Check your data thoroughly for each dimension and measure you build.I know it will take some tim.But the rewards are huge.

6) Now Most important ans – Why is full outer join required when you are joining 2 facts.

the relationship between a fact and dimension is always 1 to n .This is not the reason why we have a full outer join.Remember full outer join usually has a confirmed dimension into picture that is there are 2 facts and one dimension .The relation between each fact and dimension is 1 to n .

Suppose you have sales and inventory stock and product and time (Which gives what stocks are there in store).There can be situation where a new product has come, so entry is there in product table and inventory level table and not a single has been sold so no entry in sales table.So without full outer join we cannot display product,inventory,sales that has all the product in store present in report. Now the question is in that case how can we say that the relationship is 1 to n we have to say its 0 to n(Absolutely correct). In our model(Go sales) we do not have thi
s condition.When we have 0 to n then its not a fact and dimension

However if you bring a time dimension into picture.Each inventory item is there in fact may be for this year or past 5 years.So the relationship 1 to n holds true.Remember that the product is the confirmed dimension over here and it has inventory and sales.However if you try to
show data of all the product that had inventory then with right outer ( sales-product <-> inventory-product).You will have all the product for the year specified that had inventory.If you do a left outer you will have all the products that had sales. If you want to show list of all the product,inventory,sales then you need to go for full outer join.

Conside the case of product, Inventory level and order details. Here product is the confirmed dimension and Inventory level and order details are facts. Now

Below is the actual query that is generated.Its result of a Minimized sql option setup.

select (coalesce(“D2″.”PRODUCT_NUMBER”, “D3″.”PRODUCT_NUMBER”)) “PRODUCT_NUMBER”,
“D3″.”ORDER_NUMBER” “ORDER_NUMBER”,
“D2″.”OPENING_INVENTORY” “OPENING_INVENTORY”
from (
select distinct “ORDER_DETAILS”.”PRODUCT_NUMBER” “PRODUCT_NUMBER”,
“ORDER_DETAILS”.”ORDER_NUMBER” “ORDER_NUMBER”
from “GOSALES”.”ORDER_DETAILS” “ORDER_DETAILS”) “D3″
FULL OUTER JOIN (
select “INVENTORY_LEVELS”.”PRODUCT_NUMBER” “PRODUCT_NUMBER”,
sum(“INVENTORY_LEVELS”.”OPENING_INVENTORY”) “OPENING_INVENTORY”
from “GOSALES”.”INVENTORY_LEVELS” “INVENTORY_LEVELS”
group by “INVENTORY_LEVELS”.”PRODUCT_NUMBER”) “D2″
on “D3″.”PRODUCT_NUMBER”=”D2″.”PRODUCT_NUMBER”

Lets look why a full outer join is required in the above case.

Here every product has order and every product is there in the inventory

7)If something is only joining on RSUM its a issue.RSUM is just sequence of numbers.when cognos cannot find any valid key to join on then it will use this.

8) Reading Cardinality- -

———————————————————————————————–
Sales staff(1:1) joined to order(0:n)

While reading always read opposite. each order has only one and only one sales staff
Each sales staff has zero or n orders

9) Determinants 

determinants come into picture if we have multiple facts at different granularity .. like one at month level and one at day level .Determinent identify set of database columns that uniquely identify set of database data … if the time has 2 joins one at month level with one fact and one at day level with another fact it comes into play .

Cognos get confused about the level of data .. that is whether month is the lowest level or day is the lowest level so to clear this we need to define determinants

In case of all our dimensional models we have defined time as year level month level and day level this makes sure that cognos know what is what level so we never need
determinents for time ..

Suppose we are joining 2 facts with one confirmed dimension in that case the data is grouped at lowest common level notice common so if one is at day and one is at month
the lowest common level should be month and not day … so when by mistake cognos takes day as lowest common level because it does not know the relation between day and month
we have a double counting

10)Different cases that i encountered.

1) If you pick product name from product name lookup , opening inventory from inventory level.You see that the total inventory repeats for each and every product name.If you pick product number from product table and opening inventory from inventory level.You see that opening inventory splits according to product number.What is the reason.

Probable– It treats product name lookup and inventory level as two separates facts and does not try to join them .In this case since its picking data from 2 facts without any dimensional info reuired in the report.It wont bring any dimension into the query ,It can directly show the data as 2 measures that are not related in any way.

11) First thing to consider whenever you look at any table is what is the primary key.That tells you what other tables are required to properly check data.Best place to look for primary keys is the determinant section of query item.

Thing to consider.

We are using a OLTP system only cognos sees it as a Fact and dimension .The Main idea is to protect dimensional information. We have to consider that all the products are present in product table .Take example of inventory level ,product and sales target and time .Entries for product are kept in inventory table only when the product is available. Similarly sales target is not defined for all the products for the year.

Now if when we have join between inventory product and then a join with sales target it has to be a full outer join.If we want all the products to be shown.This will show only those product that have sales target or inventory level or both.But not those products that are not in sales target and in inventory level.Like a new product that got just added. (Keep that in mind)
Now if you want to show how many orders were there for a country.We have country,branch,order header,order detail.You will observe that the relationships are all 1 to n in sequence.That is
country to branch t to n , branch to order header 1 to n and order header to order detail 1 to n.

When you pick data from country and branch with inner join all the data will come.as their relationship is 1 to n.That is a county is present for each and every branch. If the relationship was 0 to N. then outer join would have been used to get all the countries

How cognos decides which side is 1:1 and which side is 1:N

When product is joined to inventory level on product number.Product number is key for product table and is unique in it so its 1:1 whereas in inventory level.Product level repeats so its 1 to n.
What happens if we have a 1 to N relationship and we change it directly to 1 to 1.Will the data be affected and if yes why??

The data will come correct.The only thing that will be affected is aggregation.Cognos will not be able to aggregate properly.That is if you say product line has

Curious case of Unit cost and product name

This looks preety simple but takes long time to understand. Every product has unit cost . Unit cost looks like a measure , however it cannot be summed up across the product hierarchy like product line >product type > product . as it makes no sense .Similarly if you have time dimension and your product unit cost changes many times in a month base d on order , what do you do.One way is to make product unit cost as a attribute and put it in a dimension.Now this will create lot of problems as the product unit cost is not fixed set of values and so could not be treated as a dimension.

So either you will show all the product values,Now remember every product might have 2 values in a month so your data is repeating so you need to set the regular aggregate to average.The unit cost will only make perfect sense when all the dimension related to it are present .Like order , product and time. Otherwise it should be set to average

Understanding Scope in Cognos Framework

Scope does not govern where clause or how the query is formed.Very important its a common misconception .However if you have dimension and levels in your model.Scope does affect the drill up and drill down capabilities, if you change the scope of product hierarchy and inventory and set the scope of inventory for product line only instead of full hierarchy you will see that the
values will change drastically.This proves that scope affects the values while doing drill up and drill down

Star schema grouping

They are differnet from creating a shortcut.Its same as creating shortcuts and movign them to a new namespace

Where to create relationships .Business layer or Database layer.

If you create relationshp or determinants on model query subjects they wont go for minimized sql they will work as a view. It also effects how other joint to query item are treated– Very important Page 329