AS2005: write a cube to a local .cub via xmla?

Hi,

i wonder if it is possible to write a cube (in AS2005) to a local .CUB file using xmla instead of the "old" CREATE GLOBAL CUBE statement ...
I haven't found anything in the msdn ... perhaps I've been blind?

background of this question:
http://forums.microsoft.com/msdn/ShowPost.aspx?postid=463952&siteid=1

Thanks for your answers and for tolerating my bad english
best regards,
Sven

Yes, it is possible. The easiest way to get an example of how to do it is to run a CREATE GLOBAL CUBE statement on your cube while you have a Profiler trace running - the CREATE GLOBAL CUBE statement gets translated to AS's XML/A scripting language in the background and you'll see that script in the trace. You can then change it as you need and possibly fix your problem.

Chris

|||Thanks for your quick reply!

We used the Profiler before and found the xmla-statement that is used

to create the new cube ... no problem so far; it creates a new database

with the cube as we want it to be.

But we didn't find the xmla-statement that writes the cube into the .cub file.

There are 3 lines in the Profiler where the filename of the .cub

appears: 2 times in the context of a copy of the original CREATE GLOBAL

CUBE statement and the last time in the line where we found the

statement that creates the cube.

The Profiler says something like this:

"CREATE GLOBAL CUBE-Instruction | FILENAME=c:\newcube.cub | DDL | <batch>...</batch>"

The <batch> we can extract to create our cube, but the part before is no scripting-language we know.

Perhaps it makes sense for you? ;)

Greetings,

Sven|||I think it's just extra information flagging the fact that this is a local cube being created and where the .cub file is located, and not DDL. Certainly when I remove it and just run the Batch statement (in MDX Sample app - not SQLMS, where for some reason I can't connect to an empty .cub file to run the DDL to create the local cube) then it all works ok.|||

Hi, Can you explain how exactly you run the BATCH statement in order to get the .CUB file?

I am using ADOMD.NET.

When I run the statement using management studio, the query succeed but nothing is created and there is no place to set the local file location.

Thanks,

Ofer

|||Hi Chris,
I'm reading your book on MDX (MDX Solutions 2ndEd.) and am currently struggling with the creation of local cubes. I want to create a local cube in Excel via VBA, but notice that the CREATE CLOBAL CUBE statement doesn't correctly save all data, so I'm experimenting with the ASSL approach.
In your book you write (p. 528) "...to create a local cube using ASSL,...:You open a connection to your target (in this case a .cub file), then execute your ASSL statement."
I'm wondering how you do this, apparently not with the MDX Sample app.?
What kind of connection is that if I need to do this using VB(A), or should I use .NET?|||

Hi Christopher,

Take a look at:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!877.entry

Regards,

Chris

|||

in MDX Sample app - not SQLMS, where for some reason I can't connect to an empty .cub file to run the DDL to create the local cube

The reason is that local cubes don't support more than one connection to the cube file, and Management Studio usually wants to open two connections (one for AMO), even if you only want to send MDX queries.

AS2005: write a cube to a local .cub via xmla?

Hi,

i wonder if it is possible to write a cube (in AS2005) to a local .CUB file using xmla instead of the "old" CREATE GLOBAL CUBE statement ...
I haven't found anything in the msdn ... perhaps I've been blind?

background of this question:
http://forums.microsoft.com/msdn/ShowPost.aspx?postid=463952&siteid=1

Thanks for your answers and for tolerating my bad english
best regards,
Sven

Yes, it is possible. The easiest way to get an example of how to do it is to run a CREATE GLOBAL CUBE statement on your cube while you have a Profiler trace running - the CREATE GLOBAL CUBE statement gets translated to AS's XML/A scripting language in the background and you'll see that script in the trace. You can then change it as you need and possibly fix your problem.

Chris

|||Thanks for your quick reply!
We used the Profiler before and found the xmla-statement that is used to create the new cube ... no problem so far; it creates a new database with the cube as we want it to be.
But we didn't find the xmla-statement that writes the cube into the .cub file.
There are 3 lines in the Profiler where the filename of the .cub appears: 2 times in the context of a copy of the original CREATE GLOBAL CUBE statement and the last time in the line where we found the statement that creates the cube.
The Profiler says something like this:
"CREATE GLOBAL CUBE-Instruction | FILENAME=c:\newcube.cub | DDL | <batch>...</batch>"
The <batch> we can extract to create our cube, but the part before is no scripting-language we know.
Perhaps it makes sense for you? ;)
Greetings,
Sven
|||I think it's just extra information flagging the fact that this is a local cube being created and where the .cub file is located, and not DDL. Certainly when I remove it and just run the Batch statement (in MDX Sample app - not SQLMS, where for some reason I can't connect to an empty .cub file to run the DDL to create the local cube) then it all works ok.|||

Hi, Can you explain how exactly you run the BATCH statement in order to get the .CUB file?

I am using ADOMD.NET.

When I run the statement using management studio, the query succeed but nothing is created and there is no place to set the local file location.

Thanks,

Ofer

|||Hi Chris,
I'm reading your book on MDX (MDX Solutions 2ndEd.) and am currently struggling with the creation of local cubes. I want to create a local cube in Excel via VBA, but notice that the CREATE CLOBAL CUBE statement doesn't correctly save all data, so I'm experimenting with the ASSL approach.
In your book you write (p. 528) "...to create a local cube using ASSL,...:You open a connection to your target (in this case a .cub file), then execute your ASSL statement."
I'm wondering how you do this, apparently not with the MDX Sample app.?
What kind of connection is that if I need to do this using VB(A), or should I use .NET?|||

Hi Christopher,

Take a look at:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!877.entry

Regards,

Chris

|||

in MDX Sample app - not SQLMS, where for some reason I can't connect to an empty .cub file to run the DDL to create the local cube

The reason is that local cubes don't support more than one connection to the cube file, and Management Studio usually wants to open two connections (one for AMO), even if you only want to send MDX queries.

AS2005: write a cube to a local .cub via xmla?

Hi,

i wonder if it is possible to write a cube (in AS2005) to a local .CUB file using xmla instead of the "old" CREATE GLOBAL CUBE statement ...
I haven't found anything in the msdn ... perhaps I've been blind?

background of this question:
http://forums.microsoft.com/msdn/ShowPost.aspx?postid=463952&siteid=1

Thanks for your answers and for tolerating my bad english
best regards,
Sven

Yes, it is possible. The easiest way to get an example of how to do it is to run a CREATE GLOBAL CUBE statement on your cube while you have a Profiler trace running - the CREATE GLOBAL CUBE statement gets translated to AS's XML/A scripting language in the background and you'll see that script in the trace. You can then change it as you need and possibly fix your problem.

Chris

|||Thanks for your quick reply!

We used the Profiler before and found the xmla-statement that is used

to create the new cube ... no problem so far; it creates a new database

with the cube as we want it to be.

But we didn't find the xmla-statement that writes the cube into the .cub file.

There are 3 lines in the Profiler where the filename of the .cub

appears: 2 times in the context of a copy of the original CREATE GLOBAL

CUBE statement and the last time in the line where we found the

statement that creates the cube.

The Profiler says something like this:

"CREATE GLOBAL CUBE-Instruction | FILENAME=c:\newcube.cub | DDL | <batch>...</batch>"

The <batch> we can extract to create our cube, but the part before is no scripting-language we know.

Perhaps it makes sense for you? ;)

Greetings,

Sven|||I think it's just extra information flagging the fact that this is a local cube being created and where the .cub file is located, and not DDL. Certainly when I remove it and just run the Batch statement (in MDX Sample app - not SQLMS, where for some reason I can't connect to an empty .cub file to run the DDL to create the local cube) then it all works ok.|||

Hi, Can you explain how exactly you run the BATCH statement in order to get the .CUB file?

I am using ADOMD.NET.

When I run the statement using management studio, the query succeed but nothing is created and there is no place to set the local file location.

Thanks,

Ofer

|||Hi Chris,
I'm reading your book on MDX (MDX Solutions 2ndEd.) and am currently struggling with the creation of local cubes. I want to create a local cube in Excel via VBA, but notice that the CREATE CLOBAL CUBE statement doesn't correctly save all data, so I'm experimenting with the ASSL approach.
In your book you write (p. 528) "...to create a local cube using ASSL,...:You open a connection to your target (in this case a .cub file), then execute your ASSL statement."
I'm wondering how you do this, apparently not with the MDX Sample app.?
What kind of connection is that if I need to do this using VB(A), or should I use .NET?|||

Hi Christopher,

Take a look at:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!877.entry

Regards,

Chris

|||

in MDX Sample app - not SQLMS, where for some reason I can't connect to an empty .cub file to run the DDL to create the local cube

The reason is that local cubes don't support more than one connection to the cube file, and Management Studio usually wants to open two connections (one for AMO), even if you only want to send MDX queries.

AS2005: Replacing the sole data source for existing cube?

Does BI Dev Studio support (and if so, how) replacing the sole data source for an existing cube with a new data source? As a note, this new data source is really an enhanced version of the original, but with added fields, stored procedures, etc. I imagine that, if I can do what I'm asking about above, then the DSV Refresh function will either automate, or at least document, the changes needed at DSV level to keep the cube connection valid.

Question restated: DoesBI Dev Studio support (and if so, how) replacing the sole data source for an existing AS2005 cube with a new data source?

The best way to change the source of data for an existing OLAP or Data Mining object is simply to change the connection string of the Data Source object on which that object depends.After doing this, you may want to refresh the Data Source View object to confirm that the new relational source contains equivalent tables. (Right-click on the background of the DSV and choose the Refresh menu item.)

The connection string of Data Source objects is configuration dependent.This is handy as it allows you to have a single Data Source that will use one connection string when your project is in “Test” configuration and another connection string when it is in “Production” configuration. Click on the Properties menu item in the Project menu to see the active configuration and use the “Configuration Manager” button to create new configurations.

It also is possible to change the primary Data Source of a Data Source View by setting the Data Source property in the property panel.(Edit the DSV and click the background of the diagram to ensure that the DSV itself is selected.)However, changing the primary Data Source only changes the default Data Source for new tables added to the DSV.Since Data Source Views support tables from multiple Data Sources, each table already included in the DSV is associated with a specific Data Source.To change the Data Source for tables already in the DSV, you need to is replace each of the tables with an equivalent table from the Data Source of your choice (Right click on a table and choose Replace Table->With Other Table…).

AS2005 Slow initial cube opening

Hi

We are having a problem in our AS2005 cubes that the initial openeing of the cubes is exceptionally slow. When the user connects though ProClarity, or I open the cube in the designer, the first report or first display of data takes about 2 minutes to return. Once it is open, the next query that you run is fine. If you exit the application, and go back in, you have to wait again for the first report.

This is very annoying to the users and frustrating for me as I cannot see anything that could be causing this.
We are running Windows Server 2003 x64 edition SP1 with SQL AS2005 x64 SP1.

Has anyone seen this before or can point me in the right direction where to look to solve this issue.

Thanks
Michael

Are you running ProClarity Professional directly againts the cube or are you using http access though ProClarity Analytics Server?

Caching of data will take some time, after a full process or restart of the server(the first user will have to pay the price of waiting) but two minutes seems to be to much. My customer run on a less powerful server than yours and the client access is though Citrix. In the morning our responstime is 30 sec to one minute.

Can you run profiler on SSAS2005 and see what happens during these two minutes?

Regards

Thomas Ivarsson

|||

we are accessing the cube directly, not using http.

The issue is that The does not only occure on the first query of the morning after a process, but on every new login during the day. I did a trace on the AS DB and there were a lot of "Discover Begin" and "End" EventClass events occuring before the MDX query was run. The were doing a lot of "10 - MDSCHEMA_MEMBERS" EventSubclass.

Could this be caused by the way the Dimensions are setup?

Thanks

Michael

|||

It can be something regarding dimension design. Do you have attribut relations defined in user hierarchies and are you using rigid relationship types? Check this Blog for more information: http://blogs.conchango.com/christianwade/archive/2006/07/12/AS-2005-Optimization-Experiences.aspx

Regards

Thomas Ivarsson

|||

Thanks, we are busy looking at a redesign of this Cube. This was designed on the Sept CTP last year, before there was any blogs about optimisations or anything like that, hence the bad Dimension design. I really just wanted to ensure that there wasn't something else out there that could be causing this.

Regards
Michael

AS2005 September CTP Errors when Processing Cube

I have a cube which was originally created in the June 2005 CTP.

I have made no changes. I am just attempting to process it in the AS2005 September CTP.

I get random messages like this:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_PolicyFact, Column: PolicyDimKey, Value: 3135. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Policy Number of Dimension: PolicyA from Database: CDEV15D1, Cube: Cube, Measure Group: Policy Fact, Partition: Policy Fact, Record: 2920.

While trying to process various fact tables. Any ideas? I checked and the key relationships in the source store are all good.

Thanks,

ChrisI had the same problem. The cube processed fine on June CTP but would not process on the September CTP. I discovered my problem was due to the way dimension key errors were handled.

For the "key not found" option on the dimension key errors tab of the processing box, change the default setting from "Report and continue" to "Ignore".|||

Hi guys,

Thanks a ton for that input. Can you believe it! I am working on the Adventure works sample db and get the same error when processing a simple cube metioned in the tutorial.

I think this option was set to "Ignore" in the CTPs and then it gor switched back in the final release.

Thanks once again! Keep up the good work!!

AS2005 September CTP Errors when Processing Cube

I have a cube which was originally created in the June 2005 CTP.

I have made no changes. I am just attempting to process it in the AS2005 September CTP.

I get random messages like this:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_PolicyFact, Column: PolicyDimKey, Value: 3135. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Policy Number of Dimension: PolicyA from Database: CDEV15D1, Cube: Cube, Measure Group: Policy Fact, Partition: Policy Fact, Record: 2920.

While trying to process various fact tables. Any ideas? I checked and the key relationships in the source store are all good.

Thanks,

ChrisI had the same problem. The cube processed fine on June CTP but would not process on the September CTP. I discovered my problem was due to the way dimension key errors were handled.

For the "key not found" option on the dimension key errors tab of the processing box, change the default setting from "Report and continue" to "Ignore".|||

Hi guys,

Thanks a ton for that input. Can you believe it! I am working on the Adventure works sample db and get the same error when processing a simple cube metioned in the tutorial.

I think this option was set to "Ignore" in the CTPs and then it gor switched back in the final release.

Thanks once again! Keep up the good work!!

AS2005 September CTP Errors when Processing Cube

I have a cube which was originally created in the June 2005 CTP.

I have made no changes. I am just attempting to process it in the AS2005 September CTP.

I get random messages like this:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_PolicyFact, Column: PolicyDimKey, Value: 3135. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Policy Number of Dimension: PolicyA from Database: CDEV15D1, Cube: Cube, Measure Group: Policy Fact, Partition: Policy Fact, Record: 2920.

While trying to process various fact tables. Any ideas? I checked and the key relationships in the source store are all good.

Thanks,

ChrisI had the same problem. The cube processed fine on June CTP but would not process on the September CTP. I discovered my problem was due to the way dimension key errors were handled.

For the "key not found" option on the dimension key errors tab of the processing box, change the default setting from "Report and continue" to "Ignore".|||

Hi guys,

Thanks a ton for that input. Can you believe it! I am working on the Adventure works sample db and get the same error when processing a simple cube metioned in the tutorial.

I think this option was set to "Ignore" in the CTPs and then it gor switched back in the final release.

Thanks once again! Keep up the good work!!

AS2005 Redistributable DLLs

I am referencing Microsoft.AnalysisServices.dll in my C# project to get to the sweet AS2005 object model for automated cube processing among other things but know I need to start getting the deployment of these DLLs figured out for client releases. Currently our installs are deployed to relative paths on the client machines and we have avoided any GAC installations. So my questions revolve around - Are we free to redistribute the Microsoft.AnalysisServices.dll? What other dependencies does it have or redistribution requirements Microsoft may have for it

Thanks in advance for any help or if someone could point me into the correct direction

PS - I have been extremely impressed with the programmability and ease of deployment of AS2005 databases in these latest revs - well done, I am a huge fan of the toolset and the exposed technology.

Brad Osterloo

Probably Microsoft.AnalysisServices.XmlaClient.dll will be enough.|||

1. Microsoft.AnalysisServices.dll does't depends on Microsoft.AnalysisServices.XmlaClient.dll

2. If you want to have a compression in XMLA over TCP, your schould have msasxpress.dll

in %PROGRAMFILES%\Microsoft SQL Server\90\shared\

|||

It took me a bit while before I looked at your thread.

I would try and explan the general policy for redistibuting Analysis Services components:

You cannot redistibute any single file, or group of files with your application. It is going to create huge mess of files with different versions copied all over on user's machine. Also known as: "dll hell".

To redistribute Analysis Services functionlity you should use Redstributable packages found on http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

The one you need in this particular case is "Microsoft SQL Server 2005 Management Objects Collection "

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||OK, Thank you Edward for your response. Much appreciated.

AS2005 Member Properties - MDX Query

Hi,

I am having a bit of a problem with retreiving member properties from our AS2005 cube. I'm using MDX query's in a VBA Excel app via ADO (not ADOMD). I've been prototyping the queries in the SQL Server Management Studio.

I currently have the following query:

WITH

MEMBER [Measures].[Portfolio] AS '[Trade].[Trade By Source System].CurrentMember.Properties("Portfolio")'

SELECT NON EMPTY {

[Measures].[Portfolio],

[Measures].[CR 01 Net Skw Adj USD],

[Measures].[JTD 01 USD]

} ON COLUMNS,

NonEmpty(Exists({[Trade].[Trade By Source System].[Trade Id].MEMBERS},[Trade].[Trade By Source System].[Source System].&[Calypso]))

ON ROWS

FROM [GCD]

WHERE ([Close Date].[Close Date].[Day].[01 Dec 2006], [Trade].[Trade By Business Unit].[Book Group].[LNCT])

This works fine, however I now want to CrossJoin the measures [CR 01 Net Skw Adj USD] and [JTD 01 USD] with a dimension called CS Tenor in order to give me a seperate value for each Tenor.

So far the only way I can do this is with the following query:

WITH

MEMBER [Measures].[Portfolio] AS '[Trade].[Trade By Source System].CurrentMember.Properties("Portfolio")'

SELECT NON EMPTY {

[Measures].[Portfolio],

[Measures].[CR 01 Net Skw Adj USD],

[Measures].[JTD 01 USD]

} * {[CSTenor].[CS Tenor].MEMBERS}

ON COLUMNS,

NonEmpty(Exists({[Trade].[Trade By Source System].[Trade Id].MEMBERS},[Trade].[Trade By Source System].[Source System].&[Calypso]))

ON ROWS

FROM [GCD]

WHERE ([Close Date].[Close Date].[Day].[01 Dec 2006], [Trade].[Trade By Business Unit].[Book Group].[LNCT])

This has the unfortunate side effect of CrossJoining the Portfolio member property with Tenor as well, resulting in 9 unwanted Portfolio columns (there are 9 members in CS Tenor). In reality the problem is more severe as I have around 10 member properties in the query, I reduced it to 1 here for simplicity.

My question is: Can I bring back member properties on the Rows axis? and a seperate question is: Can I CrossJoin some measures and not other on the columns axis, for example, could I CrossJoin [CR 01 Net Skw Adj USD] with CS Tenor, but not [JTD 01 USD] ?

Many thanks!

I don't believe you can cross join some measures and not other on the column axis but assuminng portfolio is an attribute of your Trade dimension the you can try this on your rows axis:

NonEmpty(Exists({[Trade].[Trade By Source System].[Trade Id].MEMBERS},[Trade].[Trade By Source System].[Source System].&[Calypso],strtomember("[Trade].[Portfolio].[" + [Trade].[Trade By Source System].CurrentMember.Properties("Portfolio") + "]") ))

Hope this helps.

V

|||

Actually, you should be able to do what you want on the column axis by simply constructing the full set of tuples that you want back. Here's an example from Adventure Works:

select

{ [Date].[Calendar Year].Members } on rows,

{ { [Measures].[Internet Sales Amount] } * { [Product].[Category].Members }, ( [Measures].[Internet Gross Profit], [Product].[Category].[All Products] ) } on columns

from

[Adventure Works]

In this query, I'm just pulling back calendar years on rows. On the columns, I'm pulling back the [Internet Sales Amount] measure cross joined with the product categories along with the [Internet Gross Profit] measure -- but with it coming back only with [All Products], not all the product cateogries. The only caveat is that all the tuples returned have to have the same dimensionality. Thus the need to return [Internet Gross Profit] with a member (in this case, the [All Products] member) of the same hierarchy used in the cross join that generates the first set of tuples.

In your case, you'd want something like this (not knowing exactly what your [All] member is, I just called it [All CS Tenors]):

{ { [Measures].[CR 01 Net Skw Adj USD] * [CSTenor].[CS Tenor].Members }, ( [Measures].[JTD 01 USD], [CSTenor].[CS Tenor].[All CS Tenors] ) } on columns

HTH,

Dave Fackler

|||

Hi,

Sorry for the delay in replying, I've been on vaction.

Many many thanks for this solution, it works perfectly!

Happy New Year,

Stuart

AS2005 error

I was trying to browse a cube and just started getting the following error:
Attempted to read or write protected memory. This is often an indication
that other memory is corrupt.
SP2 has been installed more than two weeks ago and everything was working
since then.
Any help is appreciated.
GIlgameshHi! Gilgamesh
Were you able to come over this issue? If so can you please let me know why
this issue came up? I am facing the same problem now.
--thanks and regards,
Prasad

AS2005 error

I was trying to browse a cube and just started getting the following error:
Attempted to read or write protected memory. This is often an indication
that other memory is corrupt.
SP2 has been installed more than two weeks ago and everything was working
since then.
Any help is appreciated.
GIlgamesh
Hi! Gilgamesh
Were you able to come over this issue? If so can you please let me know why
this issue came up? I am facing the same problem now.
--thanks and regards,
Prasad

AS2005 Cube Processing

I have problems with processing cubes on AS2005. I get this error message while processing:

Memory error: Allocation failure: Not enough storage is available to process this command. Error in the OLAP storage engine.

This machine has 6GB of memory and once it hits 3.5GB the processing stops. AWE is turned on.

I would appreciate, if anyone can help on this.

Thanks,

Gopal

Analysis Services is not AWE aware. So it can only use 2GB on a 32bit box (3GB if you have /3GB switch in boot.ini). Looks like the processing operation is running out of memory.

What are the number and sizes of dimension and partition tables? Do you have aggregations? Look at the processing log (in the process dialog) and find out what is the operation that causes out of memory - dimension processing, partition processing, building aggregations, etc.

This could also happen due to excessive parallelism. Try processing the dimensions and partitions individually.

|||

Thanks for your reply.

I managed to process the partitions and dimensions individually. That worked.

Gopal

|||

Hi.

Just a short question:

Is it really true that SSAS can use only 2GB RAM on a 32bit system? The technical reference for SQL Server 2005 Standard and Enterprise states that SQL Server can use the amount of RAM supported by the operating system which is 4GB for a Server 2003 Standard edition and 32GB for a Server 2003 Enterprise edition, both on 32bit systems. Does this apply only to the SQL Server Database Engine then and not the Analysis Services Server?

Regards

Kjetil

T.K. Anand wrote:

Analysis Services is not AWE aware. So it can only use 2GB on a 32bit box (3GB if you have /3GB switch in boot.ini). Looks like the processing operation is running out of memory.

What are the number and sizes of dimension and partition tables? Do you have aggregations? Look at the processing log (in the process dialog) and find out what is the operation that causes out of memory - dimension processing, partition processing, building aggregations, etc.

This could also happen due to excessive parallelism. Try processing the dimensions and partitions individually.

|||

In the 64-bit versions of Analysis Services...has this memory use limit been removed?

...cordell...

|||Yes - 64bit version doesn't have these limitations.

AS2005 Cube Processed "Successfully", But Data Not Refreshed

We process the cube automatically every night but the data isn't up-to-date despite a "successful" processing.

However, if I go onto the server and process the cube directly there through the BI Dev Studio, the data will finally get updated.

Similarly, if I try to process the cube from a client using Management Studio, it processes "successfully" as well, but the data is not updated.

I have to do it on the server itself. I've noticed this behavior the last few weeks, but it could very well have been going on for quite some time.

Any ideas or thoughts on this behavior?

What method you use in your processing automation?
Are you using SSIS processing Task?

Try and run SQL Server Profiler trace to capture Command Begin/End requests only and try to see if one is issued during your nightly processing job.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||I was told that we do indeed using SSIS Processing Task for the nightly cube processing automation.

I just ran a profile trace and I don't think I see any real difference in the Command Events whether I process the cube in the BI Dev Studio directly on the server or from SQL Management Studio from a client. However, the data is usually only updated if I do it on the server directly (and I always use the BI Dev Studio).

What should I be looking for in the trace?
|||I think I know what's going on. When I do it in the BI Dev Studio on the server, I'm essentially processing the solution as a whole. As a result, when the process screen comes up, the Type is database. If I processed the cube and not necessarily the entire solution, the Type is Cube.

I hope that make sense.

Which "should" be the correct one to use and what's the difference? It appears in our case, the "Database" type is correct.

|||

Can you connect using MDX Sample application?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

AS2005 Cube Excel 2007 Filtering Issue

When browsing our AS2005 Cube we only have one filtering option. Top 10... under the Value Filters menu item. All other filtering options are disabled. I can create a pivot table manually from data in an excel worksheet and the filtering options work fine. Which leads me to believe it is something wrong with our cube design / metadata? Any ideas for what we might need to have the filtering options enabled.Have you installed SP2 on the AS server ? Some features of Excel 2007 are only enabled against AS SP2.|||No it doesn't, I have tested against my local copy of sql server which does have sp2 and it resolved the issue. Thanks for the help.|||

Hello Mosha. It would be interesting to know about the features in Excel 2007 that are disabled before SSAS2005 SP2?

Is this something that you have mentioned in your Blog(multiselect) or something else?

Do you mean internal features like the MDX being generated from Excel2007?

Are these new features being enabled by an update on the client?

Kind Regards

Thomas Ivarsson

|||

> It would be interesting to know about the features in Excel 2007 that are disabled before SSAS2005 SP2?

I agree that it would be interesting to know. This question belongs to the Excel forum, however. Also, in couple of months when everybody has upgraded to SP2, this will become a moot point.

> Are these new features being enabled by an update on the client?

I beleive it's enough to upgrade AS server only.

|||

From what you are saying I think the new features will be in an Office 2007 SP1, not in SSAS2005 SP2, even if SSAS2005 will provide the basis for that.

Writeback in Excel 2007 will be on my whish list.

I think we have a problem of where to post Excel 2007 and ProClarity questions regarding SSAS2005.

Regards

Thomas Ivarsson

|||

For Excel 2007 questions, the following forum seems to be appropriate: http://forums.microsoft.com/technet/showforum.aspx?forumid=544&siteid=17

AS2005 Cognos Power Play 8

Has anyone managed to use Cognos against Analysis Service 2005 cube? If so any help or point of reference welcome!

Thanks

Sutha

Hi Sutha,

No direct experience I'm afraid but I was talking to Allan Mitchell last night (and where were you, eh?) and he mentioned that he'd had Cognos people in to do a PoC on one of his AS2005 cubes and they were unable to get it to work properly either. Drop him a mail and he might be able to fill you in with the details.

Chris

|||

Since I'm also interested in finding out how well Cognos 8 leverages the AS 2005 feature set, I've Googled some relevent yet "only slightly better than nothing yet" findings this morning, and I'm posting summaries and links here...

Cognos Link to 7/11/07 article indicating Cognos 8 Controller 8.2 Software Environments
Updated: July 11, 2007 ... support of AS 2005 SP2, is listed by Cognos as "compatible", meaning partially tested by Cognos and/or tested or asserted by a Cognos Partner (vs. "Active" (fully tested and supported) or "not supported").

http://support.cognos.com/en/support/products/controller82_software_environments.html

Older Cognos Link to 7/11/2006 article indicating Cognos 8 MR2 does (at least at time of article) [or did] NOT SUPPORT SSAS 2005 cubes. http://support.cognos.com/en/support/products/cognos8mr2_software_environments.html#olap

Request Feedback from Anyone Directly Experienced in Attempting to Connect Cognos 8 with AS2005 Cubes.

As an AS2005 worker (not Cognos), I'm keen to hear how well Cognos' 8's reporting and/or "downstream-from-OLAP" analytics environment can leverage the AS2005 feature set. It's scary not seeing an "Active" (fully tested, Cognos-approved) rating by Cognos in the above link.

Daniel Upton

www.decisionlab.net

|||

Hello! I have came across some implementatiions of Cognos on top of SQL Server 2000 and SQL 2005.

My conclusions are:

Cognos work best with its own cubes, read Transformer. The support for AS2000 cubes, last year, did not include named sets, six years after the release of AS2000, The support for SSAS2005 was nonexistent.

Cognos supplies products on top of other suppliers RDBMS products and is very frequently bought by customers that do not know that SQL Server 2005 includes SSAS2005 and SSRS2005. I have seen customers who have bought SQL Server 2005 and wasted money on Cognos 8 on top of that.

|||I don't know if you mean how to use Powerplay against an AS Cube, but this is from a link from cognos support on how to use AS2k5 as a datasource.

I don't have a Cognos Series8 install here to test this with, and it surely didn't work in Series 7 . Not that i was expecting it to..

Title:

How to use Microsoft Analysis Services 2005 Cubes in Cognos 8.2

Document#:

1031921.1

Updated:

Jun 21, 2007

Applies To:

Cognos 8 BI Analysis Studio 8.2 | Cognos 8 BI Cognos Connection 8.1 | Cognos 8 BI Framework Manager 8.2

Cases:

3

Collection:

KB

---

Description:

How to setup Microsoft Analysis Services 2005 Cubes in Cognos 8.2 as Data Source

Environment:

Win 2003 Standard Edition/ Enterprise Edition, SQL Server 2005 (SP2), Cognos 8.2

Steps:

Run the Cognos 8 service as a Domain Account (ie Cognos_service)
Add (Cognos_service) to SQLServer2005MSOLAPUser$<server_name>$<instance_name> Group on the machine running SQL Server 2005
Add (Cognos_service) to the role for under the database running the Analysis Service Cube
(Open MS Server Managerment Studio > Login to Analysis Services Server > Open the Database running the cube > Create a Role and assign read access to at least one cube inside the database and add (Cognos-service) to the role).
Go to Cognos Connection > Tools > Data Source > New Data Source > Name the Data source > Type ( Analysis Service 2005 ) > Host Name <Enter the host name of SQL Server 2005> > Named Instance <Enter the Instance Name of SQL Server>

{ If you don't have a defined instance leave this field blank. To check the instance name logon to MS Server Management Studio. Expected format

<Host Name>\ <Instance Name> ( MS Analysis Server 9.00.XXX ) - <Host Name>\< user name>.}
Choose the connection Type ( Cognos8 Credentials or External Namespace)
Test the connection.
Finish|||Oh, scratch that, i guess you were looking to use PP, guess i should have read the topic, and not the post
|||

Chris,

Actually, if you were describing how to connect the Cognos reporting environment (with which I'm not familiar) to an AS2005 cube, then that is relevent. I don't currently plan to use a PP cube. Thx.

Of course, just connecting from Cognos does not mean that Cognos really leverages the AS2005 feature set, right? On that note, do you have any feedback on that question?

|||

OK. You're confirming my suspicions, which is helpful. Thanks, Thomas.

However, Cognos' support of AS2005 SP2 was quoted as "compatible" on the Cognos site (link is on my initial post on this thread.

Has anybody at MSDN has seen this alleged Cognos "compatible" support of AS2005 SP2 in action? If so, how did it do?

Daniel Upton

www.decisionlab.net

|||

According to this page AS2005 is still not supported:

http://support.cognos.com/en/support/products/cognos8mr2_software_environments.html

MSSQL Server Analysis Services 2005

Not Supported

Not Supported

I supect that the support, when it arrives, will be limited for business logic, like named sets, because PP have it own calculation engine for that. Their strategy is to use other suppliers cubes as nothing more than data stores and keep the business logic inside its own products.

Regards

Thomas Ivarsson

|||

Interesting. So, let's suppose for a moment that Cognos 8's interface has great capabilities to build, on a raw AS2005 cube, with queries, calculated members, named sets, KPI's, by visual interface and/or MDX -- and create dashboards, reports, etc. Finally, let's also assume that no cube transactions or actions are necessary. In this context, what really bad limitations will be encountered by, say a legacy Cognos Team newly implementing AS2005, with doing that biz logic outside of BIDS and Analysis Svcs? Let's also suppose here that no cube translations or actions are necessary.

|||

No limitations except that all your business logic will reside in Cognos and you are unable to use tools from different suppliers. This strategy is almost the same as ProClarity have used except that ProClarity supports business logic in AS2005(MDX scipts) and provide its own tools for that.

And when organizations and business migrate to Office 2007, Excel 2007 will support SSAS2005 features.

Regards

Thomas Ivarsson

|||

I totally agree with you on that. Thanks very much, Thomas, for the extended dialogue. Very helpful!

Daniel Upton

|||Cognos 8.2 supports AS2005 SP2 and AS2000 as a datasource. I have implemented that at our site. Yes you don't need cognos once you have SQL Server. But sometime politics plays a role.

Best Regards
|||

Hello Jason,

Yes Cognos8 work on SSAS, but performance are very slowly, and nothing to help for this problem, Cognos say look at MS, and Ms don't say nothing.

Why an Excel is 10 time faster than Cognos 8 analysis studio when we are querying an SSAS CUBE?

Customer are nos so happy at end !!!

pascal

|||

Hello Chris.

I use Cognos 8 with SQL2000 cube, and with SQL2005 cube,

the main problems are :

1 - to publish SSAS cube we use Framework Manager and we can't add filters, properties, calculations.

2 - Performance are incredible, i say 10 time slower than Excel or Cube Browser, and we have nothing to help .

3 - Sometime i try to play with Reporting Services, but customer's say "i'ts not standard !"

Any idea to check performance at all levels ?

Merci de Suisse .

AS2005 Cognos Power Play 8

Has anyone managed to use Cognos against Analysis Service 2005 cube? If so any help or point of reference welcome!

Thanks

Sutha

Hi Sutha,

No direct experience I'm afraid but I was talking to Allan Mitchell last night (and where were you, eh?) and he mentioned that he'd had Cognos people in to do a PoC on one of his AS2005 cubes and they were unable to get it to work properly either. Drop him a mail and he might be able to fill you in with the details.

Chris

|||

Since I'm also interested in finding out how well Cognos 8 leverages the AS 2005 feature set, I've Googled some relevent yet "only slightly better than nothing yet" findings this morning, and I'm posting summaries and links here...

Cognos Link to 7/11/07 article indicating Cognos 8 Controller 8.2 Software Environments
Updated: July 11, 2007 ... support of AS 2005 SP2, is listed by Cognos as "compatible", meaning partially tested by Cognos and/or tested or asserted by a Cognos Partner (vs. "Active" (fully tested and supported) or "not supported").

http://support.cognos.com/en/support/products/controller82_software_environments.html

Older Cognos Link to 7/11/2006 article indicating Cognos 8 MR2 does (at least at time of article) [or did] NOT SUPPORT SSAS 2005 cubes. http://support.cognos.com/en/support/products/cognos8mr2_software_environments.html#olap

Request Feedback from Anyone Directly Experienced in Attempting to Connect Cognos 8 with AS2005 Cubes.

As an AS2005 worker (not Cognos), I'm keen to hear how well Cognos' 8's reporting and/or "downstream-from-OLAP" analytics environment can leverage the AS2005 feature set. It's scary not seeing an "Active" (fully tested, Cognos-approved) rating by Cognos in the above link.

Daniel Upton

www.decisionlab.net

|||

Hello! I have came across some implementatiions of Cognos on top of SQL Server 2000 and SQL 2005.

My conclusions are:

Cognos work best with its own cubes, read Transformer. The support for AS2000 cubes, last year, did not include named sets, six years after the release of AS2000, The support for SSAS2005 was nonexistent.

Cognos supplies products on top of other suppliers RDBMS products and is very frequently bought by customers that do not know that SQL Server 2005 includes SSAS2005 and SSRS2005. I have seen customers who have bought SQL Server 2005 and wasted money on Cognos 8 on top of that.

|||I don't know if you mean how to use Powerplay against an AS Cube, but this is from a link from cognos support on how to use AS2k5 as a datasource.

I don't have a Cognos Series8 install here to test this with, and it surely didn't work in Series 7 . Not that i was expecting it to..

Title:

How to use Microsoft Analysis Services 2005 Cubes in Cognos 8.2

Document#:

1031921.1

Updated:

Jun 21, 2007

Applies To:

Cognos 8 BI Analysis Studio 8.2 | Cognos 8 BI Cognos Connection 8.1 | Cognos 8 BI Framework Manager 8.2

Cases:

3

Collection:

KB

---

Description:

How to setup Microsoft Analysis Services 2005 Cubes in Cognos 8.2 as Data Source

Environment:

Win 2003 Standard Edition/ Enterprise Edition, SQL Server 2005 (SP2), Cognos 8.2

Steps:

Run the Cognos 8 service as a Domain Account (ie Cognos_service)
Add (Cognos_service) to SQLServer2005MSOLAPUser$<server_name>$<instance_name> Group on the machine running SQL Server 2005
Add (Cognos_service) to the role for under the database running the Analysis Service Cube
(Open MS Server Managerment Studio > Login to Analysis Services Server > Open the Database running the cube > Create a Role and assign read access to at least one cube inside the database and add (Cognos-service) to the role).
Go to Cognos Connection > Tools > Data Source > New Data Source > Name the Data source > Type ( Analysis Service 2005 ) > Host Name <Enter the host name of SQL Server 2005> > Named Instance <Enter the Instance Name of SQL Server>

{ If you don't have a defined instance leave this field blank. To check the instance name logon to MS Server Management Studio. Expected format

<Host Name>\ <Instance Name> ( MS Analysis Server 9.00.XXX ) - <Host Name>\< user name>.}
Choose the connection Type ( Cognos8 Credentials or External Namespace)
Test the connection.
Finish|||Oh, scratch that, i guess you were looking to use PP, guess i should have read the topic, and not the post
|||

Chris,

Actually, if you were describing how to connect the Cognos reporting environment (with which I'm not familiar) to an AS2005 cube, then that is relevent. I don't currently plan to use a PP cube. Thx.

Of course, just connecting from Cognos does not mean that Cognos really leverages the AS2005 feature set, right? On that note, do you have any feedback on that question?

|||

OK. You're confirming my suspicions, which is helpful. Thanks, Thomas.

However, Cognos' support of AS2005 SP2 was quoted as "compatible" on the Cognos site (link is on my initial post on this thread.

Has anybody at MSDN has seen this alleged Cognos "compatible" support of AS2005 SP2 in action? If so, how did it do?

Daniel Upton

www.decisionlab.net

|||

According to this page AS2005 is still not supported:

http://support.cognos.com/en/support/products/cognos8mr2_software_environments.html

MSSQL Server Analysis Services 2005

Not Supported

Not Supported

I supect that the support, when it arrives, will be limited for business logic, like named sets, because PP have it own calculation engine for that. Their strategy is to use other suppliers cubes as nothing more than data stores and keep the business logic inside its own products.

Regards

Thomas Ivarsson

|||

Interesting. So, let's suppose for a moment that Cognos 8's interface has great capabilities to build, on a raw AS2005 cube, with queries, calculated members, named sets, KPI's, by visual interface and/or MDX -- and create dashboards, reports, etc. Finally, let's also assume that no cube transactions or actions are necessary. In this context, what really bad limitations will be encountered by, say a legacy Cognos Team newly implementing AS2005, with doing that biz logic outside of BIDS and Analysis Svcs? Let's also suppose here that no cube translations or actions are necessary.

|||

No limitations except that all your business logic will reside in Cognos and you are unable to use tools from different suppliers. This strategy is almost the same as ProClarity have used except that ProClarity supports business logic in AS2005(MDX scipts) and provide its own tools for that.

And when organizations and business migrate to Office 2007, Excel 2007 will support SSAS2005 features.

Regards

Thomas Ivarsson

|||

I totally agree with you on that. Thanks very much, Thomas, for the extended dialogue. Very helpful!

Daniel Upton

|||Cognos 8.2 supports AS2005 SP2 and AS2000 as a datasource. I have implemented that at our site. Yes you don't need cognos once you have SQL Server. But sometime politics plays a role.

Best Regards
|||

Hello Jason,

Yes Cognos8 work on SSAS, but performance are very slowly, and nothing to help for this problem, Cognos say look at MS, and Ms don't say nothing.

Why an Excel is 10 time faster than Cognos 8 analysis studio when we are querying an SSAS CUBE?

Customer are nos so happy at end !!!

pascal

|||

Hello Chris.

I use Cognos 8 with SQL2000 cube, and with SQL2005 cube,

the main problems are :

1 - to publish SSAS cube we use Framework Manager and we can't add filters, properties, calculations.

2 - Performance are incredible, i say 10 time slower than Excel or Cube Browser, and we have nothing to help .

3 - Sometime i try to play with Reporting Services, but customer's say "i'ts not standard !"

Any idea to check performance at all levels ?

Merci de Suisse .

AS2005 Cognos Power Play 8

Has anyone managed to use Cognos against Analysis Service 2005 cube? If so any help or point of reference welcome!

Thanks

Sutha

Hi Sutha,

No direct experience I'm afraid but I was talking to Allan Mitchell last night (and where were you, eh?) and he mentioned that he'd had Cognos people in to do a PoC on one of his AS2005 cubes and they were unable to get it to work properly either. Drop him a mail and he might be able to fill you in with the details.

Chris

|||

Since I'm also interested in finding out how well Cognos 8 leverages the AS 2005 feature set, I've Googled some relevent yet "only slightly better than nothing yet" findings this morning, and I'm posting summaries and links here...

Cognos Link to 7/11/07 article indicating Cognos 8 Controller 8.2 Software Environments
Updated: July 11, 2007 ... support of AS 2005 SP2, is listed by Cognos as "compatible", meaning partially tested by Cognos and/or tested or asserted by a Cognos Partner (vs. "Active" (fully tested and supported) or "not supported").

http://support.cognos.com/en/support/products/controller82_software_environments.html

Older Cognos Link to 7/11/2006 article indicating Cognos 8 MR2 does (at least at time of article) [or did] NOT SUPPORT SSAS 2005 cubes. http://support.cognos.com/en/support/products/cognos8mr2_software_environments.html#olap

Request Feedback from Anyone Directly Experienced in Attempting to Connect Cognos 8 with AS2005 Cubes.

As an AS2005 worker (not Cognos), I'm keen to hear how well Cognos' 8's reporting and/or "downstream-from-OLAP" analytics environment can leverage the AS2005 feature set. It's scary not seeing an "Active" (fully tested, Cognos-approved) rating by Cognos in the above link.

Daniel Upton

www.decisionlab.net

|||

Hello! I have came across some implementatiions of Cognos on top of SQL Server 2000 and SQL 2005.

My conclusions are:

Cognos work best with its own cubes, read Transformer. The support for AS2000 cubes, last year, did not include named sets, six years after the release of AS2000, The support for SSAS2005 was nonexistent.

Cognos supplies products on top of other suppliers RDBMS products and is very frequently bought by customers that do not know that SQL Server 2005 includes SSAS2005 and SSRS2005. I have seen customers who have bought SQL Server 2005 and wasted money on Cognos 8 on top of that.

|||I don't know if you mean how to use Powerplay against an AS Cube, but this is from a link from cognos support on how to use AS2k5 as a datasource.

I don't have a Cognos Series8 install here to test this with, and it surely didn't work in Series 7 . Not that i was expecting it to..

Title:

How to use Microsoft Analysis Services 2005 Cubes in Cognos 8.2

Document#:

1031921.1

Updated:

Jun 21, 2007

Applies To:

Cognos 8 BI Analysis Studio 8.2 | Cognos 8 BI Cognos Connection 8.1 | Cognos 8 BI Framework Manager 8.2

Cases:

3

Collection:

KB

---

Description:

How to setup Microsoft Analysis Services 2005 Cubes in Cognos 8.2 as Data Source

Environment:

Win 2003 Standard Edition/ Enterprise Edition, SQL Server 2005 (SP2), Cognos 8.2

Steps:

Run the Cognos 8 service as a Domain Account (ie Cognos_service)
Add (Cognos_service) to SQLServer2005MSOLAPUser$<server_name>$<instance_name> Group on the machine running SQL Server 2005
Add (Cognos_service) to the role for under the database running the Analysis Service Cube
(Open MS Server Managerment Studio > Login to Analysis Services Server > Open the Database running the cube > Create a Role and assign read access to at least one cube inside the database and add (Cognos-service) to the role).
Go to Cognos Connection > Tools > Data Source > New Data Source > Name the Data source > Type ( Analysis Service 2005 ) > Host Name <Enter the host name of SQL Server 2005> > Named Instance <Enter the Instance Name of SQL Server>

{ If you don't have a defined instance leave this field blank. To check the instance name logon to MS Server Management Studio. Expected format

<Host Name>\ <Instance Name> ( MS Analysis Server 9.00.XXX ) - <Host Name>\< user name>.}
Choose the connection Type ( Cognos8 Credentials or External Namespace)
Test the connection.
Finish|||Oh, scratch that, i guess you were looking to use PP, guess i should have read the topic, and not the post
|||

Chris,

Actually, if you were describing how to connect the Cognos reporting environment (with which I'm not familiar) to an AS2005 cube, then that is relevent. I don't currently plan to use a PP cube. Thx.

Of course, just connecting from Cognos does not mean that Cognos really leverages the AS2005 feature set, right? On that note, do you have any feedback on that question?

|||

OK. You're confirming my suspicions, which is helpful. Thanks, Thomas.

However, Cognos' support of AS2005 SP2 was quoted as "compatible" on the Cognos site (link is on my initial post on this thread.

Has anybody at MSDN has seen this alleged Cognos "compatible" support of AS2005 SP2 in action? If so, how did it do?

Daniel Upton

www.decisionlab.net

|||

According to this page AS2005 is still not supported:

http://support.cognos.com/en/support/products/cognos8mr2_software_environments.html

MSSQL Server Analysis Services 2005

Not Supported

Not Supported

I supect that the support, when it arrives, will be limited for business logic, like named sets, because PP have it own calculation engine for that. Their strategy is to use other suppliers cubes as nothing more than data stores and keep the business logic inside its own products.

Regards

Thomas Ivarsson

|||

Interesting. So, let's suppose for a moment that Cognos 8's interface has great capabilities to build, on a raw AS2005 cube, with queries, calculated members, named sets, KPI's, by visual interface and/or MDX -- and create dashboards, reports, etc. Finally, let's also assume that no cube transactions or actions are necessary. In this context, what really bad limitations will be encountered by, say a legacy Cognos Team newly implementing AS2005, with doing that biz logic outside of BIDS and Analysis Svcs? Let's also suppose here that no cube translations or actions are necessary.

|||

No limitations except that all your business logic will reside in Cognos and you are unable to use tools from different suppliers. This strategy is almost the same as ProClarity have used except that ProClarity supports business logic in AS2005(MDX scipts) and provide its own tools for that.

And when organizations and business migrate to Office 2007, Excel 2007 will support SSAS2005 features.

Regards

Thomas Ivarsson

|||

I totally agree with you on that. Thanks very much, Thomas, for the extended dialogue. Very helpful!

Daniel Upton

AS2005 Cognos Power Play 8

Has anyone managed to use Cognos against Analysis Service 2005 cube? If so any help or point of reference welcome!

Thanks

Sutha

Hi Sutha,

No direct experience I'm afraid but I was talking to Allan Mitchell last night (and where were you, eh?) and he mentioned that he'd had Cognos people in to do a PoC on one of his AS2005 cubes and they were unable to get it to work properly either. Drop him a mail and he might be able to fill you in with the details.

Chris

|||

Since I'm also interested in finding out how well Cognos 8 leverages the AS 2005 feature set, I've Googled some relevent yet "only slightly better than nothing yet" findings this morning, and I'm posting summaries and links here...

Cognos Link to 7/11/07 article indicating Cognos 8 Controller 8.2 Software Environments
Updated: July 11, 2007 ... support of AS 2005 SP2, is listed by Cognos as "compatible", meaning partially tested by Cognos and/or tested or asserted by a Cognos Partner (vs. "Active" (fully tested and supported) or "not supported").

http://support.cognos.com/en/support/products/controller82_software_environments.html

Older Cognos Link to 7/11/2006 article indicating Cognos 8 MR2 does (at least at time of article) [or did] NOT SUPPORT SSAS 2005 cubes. http://support.cognos.com/en/support/products/cognos8mr2_software_environments.html#olap

Request Feedback from Anyone Directly Experienced in Attempting to Connect Cognos 8 with AS2005 Cubes.

As an AS2005 worker (not Cognos), I'm keen to hear how well Cognos' 8's reporting and/or "downstream-from-OLAP" analytics environment can leverage the AS2005 feature set. It's scary not seeing an "Active" (fully tested, Cognos-approved) rating by Cognos in the above link.

Daniel Upton

www.decisionlab.net

|||

Hello! I have came across some implementatiions of Cognos on top of SQL Server 2000 and SQL 2005.

My conclusions are:

Cognos work best with its own cubes, read Transformer. The support for AS2000 cubes, last year, did not include named sets, six years after the release of AS2000, The support for SSAS2005 was nonexistent.

Cognos supplies products on top of other suppliers RDBMS products and is very frequently bought by customers that do not know that SQL Server 2005 includes SSAS2005 and SSRS2005. I have seen customers who have bought SQL Server 2005 and wasted money on Cognos 8 on top of that.

|||I don't know if you mean how to use Powerplay against an AS Cube, but this is from a link from cognos support on how to use AS2k5 as a datasource.

I don't have a Cognos Series8 install here to test this with, and it surely didn't work in Series 7 . Not that i was expecting it to..

Title:

How to use Microsoft Analysis Services 2005 Cubes in Cognos 8.2

Document#:

1031921.1

Updated:

Jun 21, 2007

Applies To:

Cognos 8 BI Analysis Studio 8.2 | Cognos 8 BI Cognos Connection 8.1 | Cognos 8 BI Framework Manager 8.2

Cases:

3

Collection:

KB

---

Description:

How to setup Microsoft Analysis Services 2005 Cubes in Cognos 8.2 as Data Source

Environment:

Win 2003 Standard Edition/ Enterprise Edition, SQL Server 2005 (SP2), Cognos 8.2

Steps:

Run the Cognos 8 service as a Domain Account (ie Cognos_service)
Add (Cognos_service) to SQLServer2005MSOLAPUser$<server_name>$<instance_name> Group on the machine running SQL Server 2005
Add (Cognos_service) to the role for under the database running the Analysis Service Cube
(Open MS Server Managerment Studio > Login to Analysis Services Server > Open the Database running the cube > Create a Role and assign read access to at least one cube inside the database and add (Cognos-service) to the role).
Go to Cognos Connection > Tools > Data Source > New Data Source > Name the Data source > Type ( Analysis Service 2005 ) > Host Name <Enter the host name of SQL Server 2005> > Named Instance <Enter the Instance Name of SQL Server>

{ If you don't have a defined instance leave this field blank. To check the instance name logon to MS Server Management Studio. Expected format

<Host Name>\ <Instance Name> ( MS Analysis Server 9.00.XXX ) - <Host Name>\< user name>.}
Choose the connection Type ( Cognos8 Credentials or External Namespace)
Test the connection.
Finish|||Oh, scratch that, i guess you were looking to use PP, guess i should have read the topic, and not the post
|||

Chris,

Actually, if you were describing how to connect the Cognos reporting environment (with which I'm not familiar) to an AS2005 cube, then that is relevent. I don't currently plan to use a PP cube. Thx.

Of course, just connecting from Cognos does not mean that Cognos really leverages the AS2005 feature set, right? On that note, do you have any feedback on that question?

|||

OK. You're confirming my suspicions, which is helpful. Thanks, Thomas.

However, Cognos' support of AS2005 SP2 was quoted as "compatible" on the Cognos site (link is on my initial post on this thread.

Has anybody at MSDN has seen this alleged Cognos "compatible" support of AS2005 SP2 in action? If so, how did it do?

Daniel Upton

www.decisionlab.net

|||

According to this page AS2005 is still not supported:

http://support.cognos.com/en/support/products/cognos8mr2_software_environments.html

MSSQL Server Analysis Services 2005

Not Supported

Not Supported

I supect that the support, when it arrives, will be limited for business logic, like named sets, because PP have it own calculation engine for that. Their strategy is to use other suppliers cubes as nothing more than data stores and keep the business logic inside its own products.

Regards

Thomas Ivarsson

|||

Interesting. So, let's suppose for a moment that Cognos 8's interface has great capabilities to build, on a raw AS2005 cube, with queries, calculated members, named sets, KPI's, by visual interface and/or MDX -- and create dashboards, reports, etc. Finally, let's also assume that no cube transactions or actions are necessary. In this context, what really bad limitations will be encountered by, say a legacy Cognos Team newly implementing AS2005, with doing that biz logic outside of BIDS and Analysis Svcs? Let's also suppose here that no cube translations or actions are necessary.

|||

No limitations except that all your business logic will reside in Cognos and you are unable to use tools from different suppliers. This strategy is almost the same as ProClarity have used except that ProClarity supports business logic in AS2005(MDX scipts) and provide its own tools for that.

And when organizations and business migrate to Office 2007, Excel 2007 will support SSAS2005 features.

Regards

Thomas Ivarsson

|||

I totally agree with you on that. Thanks very much, Thomas, for the extended dialogue. Very helpful!

Daniel Upton

|||Cognos 8.2 supports AS2005 SP2 and AS2000 as a datasource. I have implemented that at our site. Yes you don't need cognos once you have SQL Server. But sometime politics plays a role.

Best Regards
|||

Hello Jason,

Yes Cognos8 work on SSAS, but performance are very slowly, and nothing to help for this problem, Cognos say look at MS, and Ms don't say nothing.

Why an Excel is 10 time faster than Cognos 8 analysis studio when we are querying an SSAS CUBE?

Customer are nos so happy at end !!!

pascal

|||

Hello Chris.

I use Cognos 8 with SQL2000 cube, and with SQL2005 cube,

the main problems are :

1 - to publish SSAS cube we use Framework Manager and we can't add filters, properties, calculations.

2 - Performance are incredible, i say 10 time slower than Excel or Cube Browser, and we have nothing to help .

3 - Sometime i try to play with Reporting Services, but customer's say "i'ts not standard !"

Any idea to check performance at all levels ?

Merci de Suisse .

AS2005 Cognos Power Play 8

Has anyone managed to use Cognos against Analysis Service 2005 cube? If so any help or point of reference welcome!

Thanks

Sutha

Hi Sutha,

No direct experience I'm afraid but I was talking to Allan Mitchell last night (and where were you, eh?) and he mentioned that he'd had Cognos people in to do a PoC on one of his AS2005 cubes and they were unable to get it to work properly either. Drop him a mail and he might be able to fill you in with the details.

Chris

|||

Since I'm also interested in finding out how well Cognos 8 leverages the AS 2005 feature set, I've Googled some relevent yet "only slightly better than nothing yet" findings this morning, and I'm posting summaries and links here...

Cognos Link to 7/11/07 article indicating Cognos 8 Controller 8.2 Software Environments
Updated: July 11, 2007 ... support of AS 2005 SP2, is listed by Cognos as "compatible", meaning partially tested by Cognos and/or tested or asserted by a Cognos Partner (vs. "Active" (fully tested and supported) or "not supported").

http://support.cognos.com/en/support/products/controller82_software_environments.html

Older Cognos Link to 7/11/2006 article indicating Cognos 8 MR2 does (at least at time of article) [or did] NOT SUPPORT SSAS 2005 cubes. http://support.cognos.com/en/support/products/cognos8mr2_software_environments.html#olap

Request Feedback from Anyone Directly Experienced in Attempting to Connect Cognos 8 with AS2005 Cubes.

As an AS2005 worker (not Cognos), I'm keen to hear how well Cognos' 8's reporting and/or "downstream-from-OLAP" analytics environment can leverage the AS2005 feature set. It's scary not seeing an "Active" (fully tested, Cognos-approved) rating by Cognos in the above link.

Daniel Upton

www.decisionlab.net

|||

Hello! I have came across some implementatiions of Cognos on top of SQL Server 2000 and SQL 2005.

My conclusions are:

Cognos work best with its own cubes, read Transformer. The support for AS2000 cubes, last year, did not include named sets, six years after the release of AS2000, The support for SSAS2005 was nonexistent.

Cognos supplies products on top of other suppliers RDBMS products and is very frequently bought by customers that do not know that SQL Server 2005 includes SSAS2005 and SSRS2005. I have seen customers who have bought SQL Server 2005 and wasted money on Cognos 8 on top of that.

|||I don't know if you mean how to use Powerplay against an AS Cube, but this is from a link from cognos support on how to use AS2k5 as a datasource.

I don't have a Cognos Series8 install here to test this with, and it surely didn't work in Series 7 . Not that i was expecting it to..

Title:

How to use Microsoft Analysis Services 2005 Cubes in Cognos 8.2

Document#:

1031921.1

Updated:

Jun 21, 2007

Applies To:

Cognos 8 BI Analysis Studio 8.2 | Cognos 8 BI Cognos Connection 8.1 | Cognos 8 BI Framework Manager 8.2

Cases:

3

Collection:

KB

---

Description:

How to setup Microsoft Analysis Services 2005 Cubes in Cognos 8.2 as Data Source

Environment:

Win 2003 Standard Edition/ Enterprise Edition, SQL Server 2005 (SP2), Cognos 8.2

Steps:

Run the Cognos 8 service as a Domain Account (ie Cognos_service)
Add (Cognos_service) to SQLServer2005MSOLAPUser$<server_name>$<instance_name> Group on the machine running SQL Server 2005
Add (Cognos_service) to the role for under the database running the Analysis Service Cube
(Open MS Server Managerment Studio > Login to Analysis Services Server > Open the Database running the cube > Create a Role and assign read access to at least one cube inside the database and add (Cognos-service) to the role).
Go to Cognos Connection > Tools > Data Source > New Data Source > Name the Data source > Type ( Analysis Service 2005 ) > Host Name <Enter the host name of SQL Server 2005> > Named Instance <Enter the Instance Name of SQL Server>

{ If you don't have a defined instance leave this field blank. To check the instance name logon to MS Server Management Studio. Expected format

<Host Name>\ <Instance Name> ( MS Analysis Server 9.00.XXX ) - <Host Name>\< user name>.}
Choose the connection Type ( Cognos8 Credentials or External Namespace)
Test the connection.
Finish|||Oh, scratch that, i guess you were looking to use PP, guess i should have read the topic, and not the post
|||

Chris,

Actually, if you were describing how to connect the Cognos reporting environment (with which I'm not familiar) to an AS2005 cube, then that is relevent. I don't currently plan to use a PP cube. Thx.

Of course, just connecting from Cognos does not mean that Cognos really leverages the AS2005 feature set, right? On that note, do you have any feedback on that question?

|||

OK. You're confirming my suspicions, which is helpful. Thanks, Thomas.

However, Cognos' support of AS2005 SP2 was quoted as "compatible" on the Cognos site (link is on my initial post on this thread.

Has anybody at MSDN has seen this alleged Cognos "compatible" support of AS2005 SP2 in action? If so, how did it do?

Daniel Upton

www.decisionlab.net

|||

According to this page AS2005 is still not supported:

http://support.cognos.com/en/support/products/cognos8mr2_software_environments.html

MSSQL Server Analysis Services 2005

Not Supported

Not Supported

I supect that the support, when it arrives, will be limited for business logic, like named sets, because PP have it own calculation engine for that. Their strategy is to use other suppliers cubes as nothing more than data stores and keep the business logic inside its own products.

Regards

Thomas Ivarsson

|||

Interesting. So, let's suppose for a moment that Cognos 8's interface has great capabilities to build, on a raw AS2005 cube, with queries, calculated members, named sets, KPI's, by visual interface and/or MDX -- and create dashboards, reports, etc. Finally, let's also assume that no cube transactions or actions are necessary. In this context, what really bad limitations will be encountered by, say a legacy Cognos Team newly implementing AS2005, with doing that biz logic outside of BIDS and Analysis Svcs? Let's also suppose here that no cube translations or actions are necessary.

|||

No limitations except that all your business logic will reside in Cognos and you are unable to use tools from different suppliers. This strategy is almost the same as ProClarity have used except that ProClarity supports business logic in AS2005(MDX scipts) and provide its own tools for that.

And when organizations and business migrate to Office 2007, Excel 2007 will support SSAS2005 features.

Regards

Thomas Ivarsson

|||

I totally agree with you on that. Thanks very much, Thomas, for the extended dialogue. Very helpful!

Daniel Upton

|||Cognos 8.2 supports AS2005 SP2 and AS2000 as a datasource. I have implemented that at our site. Yes you don't need cognos once you have SQL Server. But sometime politics plays a role.

Best Regards
|||

Hello Jason,

Yes Cognos8 work on SSAS, but performance are very slowly, and nothing to help for this problem, Cognos say look at MS, and Ms don't say nothing.

Why an Excel is 10 time faster than Cognos 8 analysis studio when we are querying an SSAS CUBE?

Customer are nos so happy at end !!!

pascal

|||

Hello Chris.

I use Cognos 8 with SQL2000 cube, and with SQL2005 cube,

the main problems are :

1 - to publish SSAS cube we use Framework Manager and we can't add filters, properties, calculations.

2 - Performance are incredible, i say 10 time slower than Excel or Cube Browser, and we have nothing to help .

3 - Sometime i try to play with Reporting Services, but customer's say "i'ts not standard !"

Any idea to check performance at all levels ?

Merci de Suisse .