find better solutions for analytics world
Issue:
When a new column is added at database table, FM model query subject doesn’t reflect new column though SQL used – “select * from table”. Validating object doesnt work either.
Solution:
select the required query subject . At toolbar choose – “Tools” > “Update Object”. Framework Manager will start verifying the object with the database. As a result the new column will be added to the Query Object.
to change values of default date prompt dates
below excel sheet gives you the java script for below scenarios
Youtube video –
Formula to add time to a date
A1+TIME(1,0,0)
Determinants define functional dependencies between query items. Use determinants to avoid double-counting in denormalized data
Multi grain means having multiple granularities at FM i.e. we use determinants for this.
Example:
When we want to join 2 tables with different granularity we use determinants.
Determinants are required to avoid double counting of fact data when dimensions connect to fact tables at levels of granularity that have repeating keys. A simple example that illustrates this scenario is that of a time dimension that has months and days. The month will repeat for each day in the month. For example, May 1, May 2, May 3, et cetera. You wouldn’t want to aggregate the values of the month for each day in the month.
The second scenario is one where a query subject behaves as a dimension and has multiple levels of granularity and will be joined to fact data on different sets of keys. For example, the time dimension has multiple levels. It is joined to inventory on the month key because inventory is tracked by month. It is also joined to the sales fact on the day key because sales are tracked by day.
Another less common scenario includes the use of BLOB data types in a query subject. Query and BLOB data type values in a query subject require additional key and indexed information. If this information is not present in the data source, you can add it via determinants.
http://cognosteacher.blogspot.com/2012/01/determinants-explained-with-example.html
Sometimes you may need to pass the current date time value to the report.
This can be achieved by using following option
select “Date & Time Prompt” –> General –> Clock Mode – default value is “Static”, change this value to “Live”.
Now when you run the prompt page you will see current date time.
Tableau adjust tile width to max
Resize sheet to fit screen
This option is available at dashboard
select required tile at dashboard
go to drop down (more options)
Fit–> Fit width
Lets assume your temp table is #t :
use tempdb
exec sp_spaceused '#t'Same code can be used to identify space used by table as well
exec sp_spaceused 'schema.tablename'I used below query to get cognos user list and associated groups. I got this query for cognos version 10.2.2
If query doesn’t provide any data, change classid and mapdlocaleid values and try.
SELECT v_group.name AS group_name,
v_user.ldap_id AS user_name
, CLASSID
, MAPDLOCALEID
FROM dbo.cmreford1 AS v_group_user
JOIN (SELECT cmid user_id,
UPPER (NAME) ldap_id
FROM dbo.cmobjprops33) v_user
ON v_group_user.refcmid = v_user.user_id
JOIN (SELECT cmobjnames.cmid cmid,
UPPER(cmobjnames.NAME) name
, cmobjects.classid
, cmobjnames.mapdlocaleid
FROM dbo.cmobjects, dbo.cmobjnames
WHERE
cmobjects.classid IN (26, 54)
AND cmobjnames.mapdlocaleid = 92
AND
cmobjects.cmid = cmobjnames.cmid) v_group
ON v_group.cmid = v_group_user.cmid
order by 1,2
————————————————————————————————————————–
SELECT v_group.name AS group_name,
v_user.ldap_id AS user_name
FROM dbo.cmreford1 AS v_group_user
JOIN (SELECT cmid user_id,
UPPER (NAME) ldap_id
FROM dbo.cmobjprops33) v_user
ON v_group_user.refcmid = v_user.user_id
JOIN (SELECT cmobjnames.cmid cmid,
UPPER(cmobjnames.NAME) name
FROM dbo.cmobjects, dbo.cmobjnames
WHERE cmobjects.classid IN (26, 54)
AND cmobjnames.mapdlocaleid = 96
AND cmobjects.cmid = cmobjnames.cmid) v_group
ON v_group.cmid = v_group_user.cmid
order by 1,2
Data Engineering and BI BLOG 