Cognos Measure Allocation / Scope In MSAS
May 5, 2005
I am a Cognos developer (10 years) and am frustrated with the apparent lack of MSAS ability to replicate Cognos measure scope / allocation features.
I have built three cubes, Sales Info, Store Count, and Date Count and wrapped these into a virtual cube (Sales Reporting). Both the Store Count and Date Count cubes have just a single dimension and a single measure each (Distinct counts of Stores and of Days respectively), this is my attempt at replicating the use of several fact queries with differing scope in Cognos Transformer. In my virtual cube I wish the distinct counts to be allocated constantly within dimensions that are out of scope. For instance if I have product categories as rows and my measures as columns then I wish to see the total number of stores on each row and the number of days in the date range so that I can calculate a few ratio's. I do not wish to see the number of stores that sold products in each category or the number of days products in that category were sold, which is what I see if I use distinct count measures in a cube with the exact same dimensionality.
All that I have been able to achieve is something similar to Cognos's 'do not allocate', zero's appear down the list of categories in out of scope dimensions.
TIA
View 4 Replies
ADVERTISEMENT
May 5, 2005
Good day all
I am unable to crosstab different alternate hierarchies of an MSAS cube from a single dimension, I'm using Cognos Powerplay (7.3 web and client) to browse the data. v 7.1 displays the same behaviour.
When I try to create the crosstab the display replaces both rows and columns to the alternate path, it overwrites the original path. Can anyone tell me if this is a Cognos issue or an Analysis services one and if there is any way around it. The only solution I have at the moment is to bodge it by creating seperate dimensions for each alternate hierarchy however this is ugly and difficult to use.
Regards
Mike
View 7 Replies
View Related
Mar 13, 2008
I have taken three dtsx files and re written them into one each in its own container. I use the XML Task task alot which the File connection is set by a variable and the variable value is evaluated by expression (the expression makes up the path/filename from other variable values). All the variables that make up the connection are at the container scope. The package will not run now because it is saying that the source (created by variables) for the file connection do not exist.
It seems the answer is that file connections exist at the package level therefore the variable has to be at the package level. This seems to be alot of variables i now have to move to package level to generate the XML source connection. Which in essence makes it confusing as to which variables operate in which container.
My question is can we easily move variable scope (Not ideal as we have alot of variables at package level) Or Can we do the same for connection managers as we do for variables and have them only used in a scope? (this will be ideal as some connections only need to be at a container scope)
View 1 Replies
View Related
May 13, 2015
In my cube there are two measures which are used in different calculations.Now I'm need to show in report if there any months in data when both or even another one of the measures is not updated (value = 0 or NULL).
how should I create the calculated measure for that?
I have tried in mgmt studio to plan this but I'm in a loop of errors.
View 4 Replies
View Related
Sep 25, 2007
Hi,
I am wondering how to create a matrix that contains 1 dimension for Top Label (Column), let's say "Year-Month"
and then 2 Measure to be in the row format rather than columnar format.
Example as below :
Year-Month on the column, and the measure is on the row :
2007-04
2007-05
2007-06
Amount Sales
1000
2000
3000
Unit Sales
10
20
30
Total
1010
2020
3030
Please share with me if you have this solution in Reporting services as it works in excel, hyperion brio, bo, cognos but somehow cannot see that function in Reporting Services.
Thanks
best regards,
Tanipar
View 1 Replies
View Related
Sep 15, 2015
In SSAS, I want to add a calculated measure and set that measure as the Parent of existing measure. For example I have the measures as A and B from the fact table. Now i want to add a calculated measure as C and set this measure as Parent for measures A and B. How to do that in SSAS.
View 4 Replies
View Related
May 3, 2015
I want to filter a measure based on another measure (both are measures on the same FACT table).Distinct Number of Users HAVING.User Cost above 0.
I tried doing having but because it’s two measures and not a tuple with a dimension it writes an eror.My query is something like:
SELECT
[Measures].[Distinct Number of Users ]
having [Measures].[User Cost] >0
ON
COLUMNS,
[code]....
Please note, that I want it in a MDX query not needing to change the cube or DWH table.
View 4 Replies
View Related
May 17, 2001
So far we are happy with MS OLAP 2000 service.
In the past we did cubes with aggregation method using summary.
MS OLAP server allows aggregate functions (Sum, Min, Max, Count, and Distinct Count).
But now the customer wants to know percentage of SUM(measure A) /SUM (measure B) in different levels and dimension combination.
Any idea?
View 1 Replies
View Related
May 5, 2006
Hi,
I have the following table:
OrderNumber varchar(15)
Completed int(1)
Certificate int(1)
ThroughputTime int(4)
With the following data:
OrderNumber Completed Certificate ThroughputTime
00001 1 1 1
00002 1 1 2
00003 1 0 1
00004 1 0 1
00005 0 1 2
00006 0 1 1
00007 0 0 4
00008 0 0 89
I have a dimension:
All Completed
- Completed
- Not Completed
and a dimension:
All Certificate
- No Certificate
- Certificate
I also have a measures:
TptSum which Sums ThroughputTime
TptCount which Counts OrderNumbers
TptAvg which divided TptSum by TptCount
My problem is with the next measure:
In MSAS I created a Cube which shows the measures on the columns and both Completed and Certificate on the rows. The OrderNumber selection is set to "All Ordernumbers". The measures a listed above are displayed exactly as i expect them to do. TptSum gives the sum for the subselection showed on the left. TptCount and TptAvg also show the expected values on their distinct rows.
I can't upload a screenshot but it looks like this:
TptSum TptCount TptAvg
All Certificate All Completed 101 8 12.63
Completed 5 4 1.25
Not Completed 96 4 24.00
No Certificate All Completed 95 4 23.75
Completed 2 2 1.00
Not Completed 93 2 46.50
Certificate All Completed 6 4 1.50
Completed 3 2 1.50
Not Completed 3 2 1.50
So far so good. Each datarow only takes the records that match the criteria at the left.
Now however, To exclude exceptions that have a huge impact on the average I want to create a trimmed average. That is, I want to exclude the top 25% values in my trimmed average measure, which are the 2 records with the highest ThroughputTime, being ordernumber 00007 and 00008 in the "All Certificate" & "All Completed" row, but different in the other rows. I also want the bottom 25% values to be trimmed, which are the 2 records with the lowest ThroughputTime, being ordernumber 00001 and 0003 in the "All Certificate" & "All Completed" row.
I tried a lot of things, but i can't get it working. Somehow all my statements don't take the subselections on the rows into account. Really frustrating.
What I want to achieve is the following:
TptSum TptCount TptAvg TrimmedAvg
All Certificate All Completed 101 8 12.63 1.50 ((2+1+2+1) / 4) (middle 4 records)
Completed 5 4 1.25 1.00 ((1+1) / 2) (middle 2 records)
Not Completed 96 4 24.00 3.00 ((2+4) / 2) (middle 2 records)
No Certificate All Completed 95 4 23.75 2.50 ((1+4) / 2) (middle 2 records)
Completed 2 2 1.00 1.00 ((1+1) / 2) (all 2 records)
Not Completed 93 2 46.50 46.50 ((4+89) / 2) (all 2 records)
Certificate All Completed 6 4 1.50 1.50 ((1+2) / 2) (middle 2 records)
Completed 3 2 1.50 1.50 ((1+2) / 2) (all 2 records)
Not Completed 3 2 1.50 1.50 ((1+2) / 2) (all 2 records)
I hope I describe my problem well... I also hope someone knows a solution. I can also post the things I tried, but I'm afraid that makes it more confusing, because nothing worked ;)
Thanks,
Edward
View 1 Replies
View Related
May 5, 2006
Hi,
I have the following table:
OrderNumber varchar(15)
Completed int(1)
Certificate int(1)
ThroughputTime int(4)
With the following data:
OrderNumber Completed Certificate ThroughputTime
00001 1 1 1
00002 1 1 2
00003 1 0 1
00004 1 0 1
00005 0 1 2
00006 0 1 1
00007 0 0 4
00008 0 0 89
I have a dimension:
All Completed
- Completed
- Not Completed
and a dimension:
All Certificate
- No Certificate
- Certificate
I also have measures:
TptSum which Sums ThroughputTime
TptCount which Counts OrderNumbers
TptAvg which divides TptSum by TptCount
My problem is with the next measure:
In MSAS I created a Cube which shows the measures on the columns and both Completed and Certificate on the rows. The OrderNumber selection is set to "All Ordernumbers". The measures as listed above are displayed exactly as I expect them to do. TptSum gives the sum for the subselection showed on the left. TptCount and TptAvg also show the expected values on their distinct rows.
I can't upload a screenshot but it looks like this:
TptSum TptCount TptAvg
All Certificate All Completed 101 8 12.63
Completed 5 4 1.25
Not Completed 96 4 24.00
No Certificate All Completed 95 4 23.75
Completed 2 2 1.00
Not Completed 93 2 46.50
Certificate All Completed 6 4 1.50
Completed 3 2 1.50
Not Completed 3 2 1.50
So far so good. Each datarow only takes the records that match the criteria at the left.
Now however, To exclude exceptions that have a huge impact on the average I want to create a trimmed average. That is, I want to exclude the top 25% values in my trimmed average measure, which are the 2 records with the highest ThroughputTime, being ordernumber 00007 and 00008 in the "All Certificate" & "All Completed" row, but different in the other rows. I also want the bottom 25% values to be trimmed, which are the 2 records with the lowest ThroughputTime, being ordernumber 00001 and 0003 in the "All Certificate" & "All Completed" row.
I tried a lot of things, but i can't get it working. Somehow all my statements don't take the subselections on the rows into account. Really frustrating.
What I want to achieve is the following:
TptSum TptCount TptAvg TrimmedAvg
All Certificate All Completed 101 8 12.63 1.50 ((2+1+2+1) / 4) (middle 4 records)
Completed 5 4 1.25 1.00 ((1+1) / 2) (middle 2 records)
Not Completed 96 4 24.00 3.00 ((2+4) / 2) (middle 2 records)
No Certificate All Completed 95 4 23.75 2.50 ((1+4) / 2) (middle 2 records)
Completed 2 2 1.00 1.00 ((1+1) / 2) (all 2 records)
Not Completed 93 2 46.50 46.50 ((4+89) / 2) (all 2 records)
Certificate All Completed 6 4 1.50 1.50 ((1+2) / 2) (middle 2 records)
Completed 3 2 1.50 1.50 ((1+2) / 2) (all 2 records)
Not Completed 3 2 1.50 1.50 ((1+2) / 2) (all 2 records)
I hope I describe my problem well... I also hope someone knows a solution. I can also post the things I tried, but I'm afraid that makes it more confusing, because nothing worked ;)
Thanks,
Edward
PS. newbie alert! :beer:
View 1 Replies
View Related
Feb 25, 2008
Dear all,
I am trying to connect Cognos to MSAS 2005 from an UNIX server (AIX). I am getting the following error:
QFS-ERR-0002 Unable to find or load the shared library '/cognos/c8/./bin/libykodp.so' (or one of its dependents) containing the provider 'MSAnalysis2005ODP'.
Is this file libykodp.so part of a SQL Server shared library (.o file) that must be installed on the UNIX server? Any pointers? Many thanks.
Best regards,
Pedro Martins
View 3 Replies
View Related
Dec 12, 2005
Hi friends,
I am new to MSAS world. I need help related to this. I want to pull data from MSAS cube programmatically. Only way I know is thru ODBO, but that won't help me in this case, cause I might have to drill down upto all possible intersections stored in MSAS (at least all of the stored members). Doing this thru MDX could be humongous thing, at least manipulating data taken out using ODBO.
I might be missing something out here. Can anybody help. It would also help if somebody can tell me if any other approach is poosible.
Thank you.
Abhijeet
View 3 Replies
View Related
Dec 14, 2005
Client have 2 servers, one with Sqlserver 2000 and one with Analysis Services.
Want to add a "Analysis Services Processing Task" to my DTS job on the SQLserver but the task does not exist since MSAS is not installed on the SQLserver...
Client does not want to install MSAS on the SQL Server....
Any ideas how to setup auto processing of the cubes ???
Regards Tale Liereng
View 3 Replies
View Related
Jul 30, 2015
We are experiencing authentication/ Kerberos issues after a password for the MSA's has changed. We use MSA for our SQL 2012 and windows 2012 combination Servers. This errors creates issues while backing up and Service Broker connectivity.We had to restart sql services to fix it, but this not seem to be a resolution for me because the next time the password changes on these MSA's we may have to restart sql services.There seems to be a known issue for windows 2008 R2 servers and fix is available and we incorporated it. But the issue we are facing is on windows 2012 Server.
View 3 Replies
View Related
Jul 23, 2005
Is it possible to exec a Cognos report from a SQL job?--Message posted via http://www.sqlmonster.com
View 1 Replies
View Related
Jul 20, 2005
Our disk drive died, and we were able to backup most of the files (allthe reports, the shuttled, pending, etc)But when we open the administrator, we get a blank. none of ourcatalogues are listed in there.I don't want to recreate all the catalogues and reports- does anyoneknow how we can "import" them in there? The database that contains allthe "data" was not backed up, sicne it was in use during backup (badNetwork admin)If anyone can help, PLEASE email. trying to get an answer from cognos,but who knows?!!!
View 2 Replies
View Related
Oct 25, 2007
Is it possible for RS to connect to a Cognos cube?
If so, where can I find information on it especially how security within cognos is evaluated/verified.
View 2 Replies
View Related
Nov 27, 2006
We are trying to investigate the ETL tools available in the market and have narrowed down to two
COGNOS - ETL (Data Manager Tool)
Microsoft - SSIS
Is there a comparison study out there which would help us in this process. Also a list of things we need to look out for in a ETL tool will also help.
Thanks
View 1 Replies
View Related
Jan 25, 2008
Hi
I have an installation of Cognos Controller 8.2. The installation of application seems to be all 100%.I can create blank SQL database use the application to upgrade the tables but as soon as I populate the database with data and try to optimise from the application there is an issue. It gives me the error below:
Standard error
Number 5
Source: Frango dircect.Optimise.DeleteTempFox#controllerProxyClie nt
Description: System.Web.Services.Protocols.SoapException
server was unable to process request-->System.Security.Security Exception: Permission Denied
I have checked permissions on the database and all is fine "fastnet" is added as the DB owner and the SQL instance is 100%, Can anyone please shed some light on this.
View 3 Replies
View Related
May 29, 2008
Hi all - just wondering if anyone could give me an idea of how arduous this task would be (migrating Cognos Data Manager scripts to SSIS). I realize it wont be a trivial endeavour, but wondered how much of a headstart having the initial validated Cognos catalog would be to this project (or would it be pretty much useless?)
Thanks
View 4 Replies
View Related
Jan 17, 2008
Hi,
We are having performance issue (too slow) compiling cubes utilizing Cognos over SqlServer database. The driver used is Microsoft Sql Server (OLE DB). Is that the right driver to be used?
Any idea what could be the problem?
Thanks
View 2 Replies
View Related
Apr 19, 2007
Hi. We've got hundreds of Cognos reports and catalogs and we'd like to convert them to SSRS reports and models. Is there a tool to do this? We're converting our reporting to SSRS and need help. Thanks.
View 2 Replies
View Related
Feb 11, 2002
Hi,
Can someone give me some help on find out the memory limit for SQL Server 7.0 and how to limit the memory being used by the SQL Server?
Thank you very much in advance!
Denise
View 1 Replies
View Related
Sep 14, 2000
Hi,
I need to create a 5GB database with 4GB for data and 1GB for log in v7.0. I know that in v6.5 I would have created five 1GB devices - to go easy on the backups.
Could someone please advise on how I should distibute allocation of space.
Should I allocate 1GB to the primary files and 1GB each to 3 secondary files?
Should I just allocate 4GB to primary?
I would really really appreciate any reponse? If there are articles I would appreciate links.
Thanks
kira
View 1 Replies
View Related
Jan 16, 2001
I have a small data warehouse which periodically has old data deleted.
However after a delete the free space within the database is not released. If I copy the tables, drop them, recreate, and copy back, the space is there.
Any ideas what is causing this anyone ?
Thanks
View 1 Replies
View Related
Nov 4, 2002
I was wondering, is there a way to allocate processors to SQL2000. I have a server that has 4 processors, I would like to leave one just for the operating system and have SQL2000 use the other 3. Is this possible and do you think it would be recomended to do this? Or should i just leave the 4 processors for everything?
thanks in advance
View 1 Replies
View Related
Aug 17, 2006
Dear Friends
I have one database with multipe MDF files.Normally when I am creating a new table it's going to primary MDF file.How I can allocate a new table to the MDF file which I am specifying when table is creating
Thanks in Advance
Filson Paul
View 1 Replies
View Related
May 21, 2007
hai,
once again pavan. i need some clarification on this issues
how sql server allocates memeory for the instances.
means
procedure cache
buffer cache
log cache
system data structure.
could you please provide information regarding this issue
regards,
pavan
pavan
Associate Oracle DBA
View 1 Replies
View Related
Mar 27, 2008
I have windows 2003 with ms sql 2000. The machine has 3.6 gigs of ram and only runs ms sql, nothing else.
Whenever the first query is made sql will allocate as much as 1.5 gigs of ram which is just killing the system. This system reboots nightly so this first query happens every morning.
We have tried setting the min and max memory of sql, as well as the reserver memory setting to see if it will preallocate all that memory but we've had no luck.
Is there a way to make SQL allocate all that memory ahead of time? maybe make it cache some tables or something?
I know very little about MS SQL so please be very descriptive about possible solutions or troubleshooting steps.
Thanks,
--Carlos
View 2 Replies
View Related
Dec 15, 2007
Hi,
can some one throw some light on how the DBAs calucaulate the space allocations?
For example I have 30000 records which has 30 columns each defined as varchar(100) and if the db is full and wants to increase the space. Then how much extrac space should be allocated??
View 5 Replies
View Related
Jan 18, 2008
How to find how much memory allocated to SQLCLR by sqlserver. Also is there any way to determine how much memory my code needs to run? Thanks in advance.
Regards,
Hari Prakash.
View 7 Replies
View Related
Jun 29, 2007
Any comments, please. I have a new server 5x160GB drives. It needs to run IIS for a web app and SQL Server 2005. One user database. I was thinking of dividing space as follows and wanted to get some thoughts from others.
2 drives = RAID 1 set, 160GB space, OS, IIS, SQL exe, tempDB, user database log file
3 drives = RAID 5 set, 320GB space, SQL data files, user database files
Thanks for looking...
View 5 Replies
View Related