HOLAP much faster then MOLAP !?

When I process a cube with the HOLAP (100% performance gain) storage option the querytime from Excel XP is very fast.
When I process the same cube with the MOLAP (100% performance gain) storage option the same query takes forever.
I'm puzzled. Can someone explain this?

Edwin

P.S. THe cube has a DistinctCount measure in it. Does this have anything to do with the poor MOLAP performance?You are correct - that is unusual. How big are both of the cubes ? Have you tried duplicating the same behavior with the foodmart sample ? Since you are suspicious of the distinctcount - have you eliminated that measure to see how it affects performance ?|||Have you tried it under excel 2000 ?|||Thank for the reply.

Excel 2000 gives me the same problem, as well as the cube data browser in the Analysis Manager itself.
I replaced the Distinct Count by a Sum. Performance was OK in both HOLAP and MOLAP, so the Distinct Count causes the pain (but I really need it).
I couldn't reproduce the same thing in the Foodmart cubes (size perhaps?).
Maybe I stumbled on some undocumented limit.
Another thing: In HOLAP mode with 100% performance gain it still computes aggregation data in Oracle. I know this, because when I shut Oracle I get #value errors. I thougt 100% means 'all aggregations in Analysis Services'. Also I'm often exactly 1 above the exact value with the Distinct Counts, because NULL seams to count a distinct value.
I rfead somewhere that Analysis Services transform a NULL into a 0 when one of the other measures in the source table is not NULL.
Is that correct, and if so, is there a patch for that? Otherwise I must define fact-views for each Distinct Count measure with "WHERE ... IS NOT NULL".

Development Server : PIII 500, 512 MB memory
(should eat this 7.5MB cube for breakfast)
Relational database: Oracle 8.1.7.3
Analysis Services : Service Pack 2
Fact table: : 69000 rows
Measures: : 1 Distinct Count
Dimension data : 1 3000 members, 1 level
2 69000 members, divided in 7 levels
3 Price-dimension (hierarchy 1)
4 Price-dimension (hierarchy 2)
Cube in MB 7.5 MB (in both HOLAP and MOLAP mode)

Edwin

Highlighting a row

Hi,
While previewing the report, Is there any way to highlight the selected row as we do in SpreadSheets (Like Excel)?
I need to hightlight the row which was selected by the user and not a particular row.
If you have any idea, pls help me
ThanksHi,

Yes, it's possible in RDC Method of Crystal Report Design Document Module.

you can catch the table.field click event at run time, in that event make code for change the field background color.

Thanks.

Originally posted by harmonycitra
Hi,

While previewing the report, Is there any way to highlight the selected row as we do in SpreadSheets (Like Excel)?

I need to hightlight the row which was selected by the user and not a particular row.

If you have any idea, pls help me

Thanks|||Thanks,

But I'm using ASP to run my report, using RDC Code and Crystal Report ActiveXViewer Control to display the report.

In this, how can I write the code?

Thanks,

hierarchy structure

hello!
I have a hierarchy structure copied from excel to a table in databse SQL.
Here is what i have in SQL table:
ContinentCountryCity
Europe
Norway
Oslo
Bergen
Trondheim
Sweden
Stockholm
Gotaborg
Denmark
Kopenhagen
Aalborg
Germany
Hamburg
Munchen
Hannover
Asia
Japan
Tokyo
Hirroshima
Kina
Shanghai
Africa
South-Africa
Pretoria
AND her is what i want
ContinentCountryCity
Europe
EuropeNorway
EuropeNorwayOslo
EuropeNorwayBergen
EuropeNorwayTrondheim
EuropeSweden
EuropeSwedenStockholm
EuropeSwedenGotaborg
EuropeDenmark
EuropeDenmarkKopenhagen
EuropeDenmarkAalborg
EuropeGermany
EuropeGermanyHamburg
EuropeGermanyMunchen
EuropeGermanyHannover
Asia
AsiaJapan
AsiaJapanTokyo
AsiaJapanHirroshima
AsiaKina
AsiaKinaShanghai
AfricaKina
AfricaSouth-Africa
AfricaSouth-AfricaPretoria
How do i that?
On Wed, 25 May 2005 02:23:02 -0700, CJ wrote:

>hello!
>I have a hierarchy structure copied from excel to a table in databse SQL.
>Here is what i have in SQL table:
(snip)
Hi CJ,
Do you mean that each row has either Continent, or Country, or City
populated, but none of the other columns? I guess you're out of luck
then. Since there is no inherent order in a relational table, there is
no difference between the table you posted and this one:
ContinentCountryCity
Africa
Asia
Europe
Denmark
Germany
Japan
Kina
Norway
South-Africa
Sweden
Aalborg
Bergen
Gotaborg
Hannover
Hamburg
Hirroshima
Kopenhagen
Munchen
Oslo
Pretoria
Stockholm
Shanghai
Tokyo
Trondheim
Besides - since this table has no key, it is officially not a relational
table at all. Of course, terminology is pretty much a question of
definitions, but a table without primary key is usually impossible to do
any serious operations on.

>AND her is what i want
>ContinentCountryCity
>Europe
>EuropeNorway
>EuropeNorwayOslo
>EuropeNorwayBergen
>EuropeNorwayTrondheim
>EuropeSweden
>EuropeSwedenStockholm
>EuropeSwedenGotaborg
>EuropeDenmark
>EuropeDenmarkKopenhagen
>EuropeDenmarkAalborg
>EuropeGermany
>EuropeGermanyHamburg
>EuropeGermanyMunchen
>EuropeGermanyHannover
>Asia
>AsiaJapan
>AsiaJapanTokyo
>AsiaJapanHirroshima
>AsiaKina
>AsiaKinaShanghai
>AfricaKina
>AfricaSouth-Africa
>AfricaSouth-AfricaPretoria
>How do i that?
You don't. The data above has no key either, so it's not a relational
table. Consider moving the data to some properly normalized tables:
CREATE TABLE Countries
(Country varchar(20) NOT NULL,
Continent varchar(10) NOT NULL,
PRIMARY KEY (Country)
)
CREATE TABLE Cities
(City varchar(25) NOT NULL,
Country varchar(20) NOT NULL,
PRIMARY KEY (City),
FOREIGN KEY (Country) REFERENCES Countries (Country)
)
INSERT INTO Countries (Country, Continent)
VALUES ('Norway', 'Europe')
INSERT INTO Countries (Country, Continent)
VALUES ('Sweden', 'Europe')
....
INSERT INTO Countries (Country, Continent)
VALUES ('South-Africa', 'Africa')
INSERT INTO Cities (City, Country)
VALUES ('Oslo', 'Norway')
INSERT INTO Cities (City, Country)
VALUES ('Bergen', 'Norway')
....
INSERT INTO Cities (City, Country)
VALUES ('Pretoria', 'South-Africa')
The input needed to get the listing you describe above would be returned
by
SELECT co.Continent, co.Country, ci.City
FROM Countries AS co
INNER JOIN Cities AS ci
ON ci.Country = co.Country
ORDER BY co.Continent, co.Country, ci.City
The final reformatting to get it displayed exectly as above (including
the repeating of some lines with partial blank data) should be handled
by the presentation tier.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Hiding Other options from Format Drop down in Report Viewer Control

Hi All
We have requirement where we need to hide some of the options which we have in Format drop down i.e we need only CSV,PDF and excel .
How can we do this?
Thanks in advance
Rehan Mustafa Khan
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.To disable export to a particular rendering format, add the string
Visible="false" to the end of the rendering extension entry in
%PROGRAMFILES%\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config file. For example, to disable
Excel from showing up in the Export dropdown in report manager, change
<Extension Name="EXCEL"
Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
osoft.ReportingServices.ExcelRendering"/>
to
<Extension Name="EXCEL"
Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
osoft.ReportingServices.ExcelRendering" Visible="false"/>
Note that disabling a rendering extension does not prevent users from
rendering to those extensions using SOAP API or use URL access to reports
like so:
http://localhost/reportserver?%2fSampleReports%2fCompany+Sales&rs:Command=Render&rs:Format=EXCEL
If you want to remove the rendering extension completely, just delete the
corresponding line from the <Render> section of the config file (in which
case URL access like shown above will throw rsRenderingExtensionNotFound
exception.)
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:uDqFX7%23YEHA.1264@.TK2MSFTNGP11.phx.gbl...
> Hi All
> We have requirement where we need to hide some of the options which we
have in Format drop down i.e we need only CSV,PDF and excel .
> How can we do this?
> Thanks in advance
> Rehan Mustafa Khan
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||Hi Ravi,
I tried ur Suggestion.Not Sure Y its not working for me'
Can u Plz Guide where im going wrong'
My Code is
<Extension Name="XML"
Type="Microsoft.ReportingServices.Rendering.XmlDataRenderer.XmlDataReport,Microsoft.ReportingServices.XmlRendering" Visible="false"//>
<Extension Name="NULL"
Type="Microsoft.ReportingServices.Rendering.NullRenderer.NullReport,Microsoft.ReportingServices.NullRendering"Visible="false"/ >
<Extension Name="CSV"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering" Visible="false"/>
<Extension Name="IMAGE"
Type="Microsoft.ReportingServices.Rendering.ImageRenderer.ImageReport,Microsoft.ReportingServices.ImageRendering"Visible="false"/>
<Extension Name="PDF"
Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport,Microsoft.ReportingServices.ImageRendering"Visible="false"/>
<Extension Name="HTML4.0"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExtension,Microsoft.ReportingServices.HtmlRendering"Visible="false"/>
<Extension Name="HTML3.2"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html32RenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>
<Extension Name="MHTML"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.MHtmlRenderingExtension,Microsoft.ReportingServices.HtmlRendering"Visible="false"/>
<Extension Name="EXCEL"
Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering"Visible="false"/>
<Extension Name="HTMLOWC"
Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.HtmlOWCRenderingExtension,Microsoft.ReportingServices.HtmlRendering"Visible="false"/>
Im waiting for ur response,
Thanks in Advance,
"Ravi Mumulla (Microsoft)" wrote:
> To disable export to a particular rendering format, add the string
> Visible="false" to the end of the rendering extension entry in
> %PROGRAMFILES%\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\RSReportServer.config file. For example, to disable
> Excel from showing up in the Export dropdown in report manager, change
> <Extension Name="EXCEL"
> Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
> osoft.ReportingServices.ExcelRendering"/>
> to
> <Extension Name="EXCEL"
> Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
> osoft.ReportingServices.ExcelRendering" Visible="false"/>
> Note that disabling a rendering extension does not prevent users from
> rendering to those extensions using SOAP API or use URL access to reports
> like so:
> http://localhost/reportserver?%2fSampleReports%2fCompany+Sales&rs:Command=Render&rs:Format=EXCEL
> If you want to remove the rendering extension completely, just delete the
> corresponding line from the <Render> section of the config file (in which
> case URL access like shown above will throw rsRenderingExtensionNotFound
> exception.)
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
> news:uDqFX7%23YEHA.1264@.TK2MSFTNGP11.phx.gbl...
> > Hi All
> >
> > We have requirement where we need to hide some of the options which we
> have in Format drop down i.e we need only CSV,PDF and excel .
> >
> > How can we do this?
> >
> > Thanks in advance
> >
> > Rehan Mustafa Khan
> >
> > --
> > Posted using Wimdows.net NntpNews Component -
> >
> > Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
> supports Post Alerts, Ratings, and Searching.
>
>

Hiding an SQL connection string in an Excel macro

Is there a technique for hiding the SQL connection string (ADO) in an Excel
macro? I think I already know the answer to that question (no), but I'm
just wondering if there's a trick that would accomplish it.
I'm authoring some macros for a client of mine and they need to communicate
with an SQL server account I have with my web hosting company and I
obviously don't want them to know my username and password.
Thanks in advance.
Hi Bob,
From your descriptions, I understood that you would like to "hide" SQL
Connection Strings in your macro to protect your SQL username and password.
Have I understood you? Correct me if I was wrong.
Based on my scope, yes, we are not able to "hide" SQL connection strings
directly, however, I think you could encrypt your macro as a workaround.
You can password protect the VBProject. In VBA, go to the Tools menu,
choose VBA Project Properties, then the Protection tab. There, check the
"Lock Project For Viewing" tab, and assign a password. Note, however, that
there are products available that can defeat the password protection. In
this way, you are able to protect your SQL username and password.
For more information about how to do this in encrypt this in Excel, I would
appreciated if you could create a new thread in the
microsoft.public.excel.programming (BTW, I am not sure whether it is a
managed newsgroup)
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Hiding an SQL connection string in an Excel macro

Is there a technique for hiding the SQL connection string (ADO) in an Excel
macro? I think I already know the answer to that question (no), but I'm
just wondering if there's a trick that would accomplish it.
I'm authoring some macros for a client of mine and they need to communicate
with an SQL server account I have with my web hosting company and I
obviously don't want them to know my username and password.
Thanks in advance.Hi Bob,
From your descriptions, I understood that you would like to "hide" SQL
Connection Strings in your macro to protect your SQL username and password.
Have I understood you? Correct me if I was wrong.
Based on my scope, yes, we are not able to "hide" SQL connection strings
directly, however, I think you could encrypt your macro as a workaround.
You can password protect the VBProject. In VBA, go to the Tools menu,
choose VBA Project Properties, then the Protection tab. There, check the
"Lock Project For Viewing" tab, and assign a password. Note, however, that
there are products available that can defeat the password protection. In
this way, you are able to protect your SQL username and password.
For more information about how to do this in encrypt this in Excel, I would
appreciated if you could create a new thread in the
microsoft.public.excel.programming (BTW, I am not sure whether it is a
managed newsgroup)
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Hiding an SQL connection string in an Excel macro

Is there a technique for hiding the SQL connection string (ADO) in an Excel
macro? I think I already know the answer to that question (no), but I'm
just wondering if there's a trick that would accomplish it.
I'm authoring some macros for a client of mine and they need to communicate
with an SQL server account I have with my web hosting company and I
obviously don't want them to know my username and password.
Thanks in advance.Hi Bob,
From your descriptions, I understood that you would like to "hide" SQL
Connection Strings in your macro to protect your SQL username and password.
Have I understood you? Correct me if I was wrong.
Based on my scope, yes, we are not able to "hide" SQL connection strings
directly, however, I think you could encrypt your macro as a workaround.
You can password protect the VBProject. In VBA, go to the Tools menu,
choose VBA Project Properties, then the Protection tab. There, check the
"Lock Project For Viewing" tab, and assign a password. Note, however, that
there are products available that can defeat the password protection. In
this way, you are able to protect your SQL username and password.
For more information about how to do this in encrypt this in Excel, I would
appreciated if you could create a new thread in the
microsoft.public.excel.programming (BTW, I am not sure whether it is a
managed newsgroup)
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!