This is what I do when I want to query OLAP from CF.
Make a LinkServer from MS SQL Server to MS OLAP Server. The setting are :
Provider Name : Microsoft OLE DB Provider for OLAP
Product Name : MSOLAP
Datesource : <OLAP SERVER NAME>
Provider string :
Location : <NAME OF OLAP DATABASE>
Catalog : <NAME OF OLAP DATABASE>
Onces that is done you can us the OPENQUERY from a normal SQL query. But you
must turn quted_identifier ON.
e.g.
-- START --
<CFQUERY >
set quoted_identifier on
SELECT o."[Measures].[Unit Sales]" as C, o."[Promotion Media].[Media
Type].[MEMBER_CAPTION]" AS R
FROM OPENQUERY(OLAP_TEST, '
select
{Measures.[Unit Sales]} on columns,
order(except([Promotion Media].[Media Type].members,{[Promotion
Media].[Media Type].[No Media]}),[Measures].[Unit Sales],DESC) on rows
from Sales
')
as o
set quoted_identifier off
</CFQUERY>
-- END --
>From Rif
PS I have not test this query!
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 17 November 2000 16:54
To: CF-Talk
Subject: PLEASE HELP--Querying Olap Cube Through MDX and CF--PLEASE HELP
Hello, I am attempting to query an Olap Cube through using MDX through CF.
I am using a com object and can see the cube but am unable to get any data
from it. I used a custom tag to see what was being returned and all that is
returned is a space. I was told by Ben Forta that I would need to play with
the com object so that CF would understand the output. Any suggestions?!?
The following code runs with no errors, (unless I have made an error in my
expression) but there is only a space in the output.
Thanks,
Eddie
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Cube Test</title>
</head>
<body>
<cfset adOpenStatic = 3>
<cfset adLockReadOnly = 1>
<cfset adCmdText = 1>
<!--- Grab the component--->
<cfobject type="COM"
name="cst"
class="ADOMD.Cellset"
action="CREATE">
<!---This is where I now write the mdx expression--->
<cfset sMDX="SELECT TopCount({[Product].[Product Name].Members}, 1,
Measures.[Unit Sales]) ON COLUMNS FROM [Sales]">
<!---<cfset sMDX="SELECT ({[Measures].[MeasuresLevel].Members}) ON COLUMNS
FROM [The Cube]"> --->
<cfset cst = cst.open(sMDX,
"Provider=MSOLAP;Datasource=NHCNTMSSQL2000;Catalog=FoodMart;")>
<!---I have just added this to make sure that the variable is not empty. If
it is the page will stop--->
<cfif "CST" IS " ">
The variable is empty.
<cfabort showerror="The Template was aborted because the variable is an
empty string">
</cfif>
<!---The values should be listed here--->
<cfform name="mdx">
<font face="arial" size=4>
Results:<br><br><br>
</font>
<font face="arial" size="3">
<cf_objectdump object="#cst#">
<cfoutput>
#cst#<br>
</cfoutput></font>
<font face="arial" size=4>
Your mdx query is:<br></font><br>
<font face="arial" size="3">
<cfoutput>
#smdx#
</cfoutput></font>
</cfform>
</body>
</html>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
