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.
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.
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)
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.
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.
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.
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.
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 ;)
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.
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 ;)
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.
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.
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 ???
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.
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?!!!
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.
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?)
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?
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.
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.
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.
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?
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
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.
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??
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.
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.
