Excel Data Mining Add-In Query Wizard Error Message

Hello Experts,

I have an error message I was wondering if anyone else has seen and resolved. When I open excel and click on the query wizard on the Data Mining Ribbon, I get an error message that says "Input String not in Correct Format"

After I close this dialog box the 'Welcome to Query .... ' wizard will open, but when I select the Advance button my process aborts due to the input string error.

I have only ran association models on the computer.

The Manage Models and Browse buttons work fine.

The error came up on Friday, but it wasn't there on Thursday. There were no changes done to the machine (no installation of new software).

I have tried reinstalling the add-in.

Thank you all for your help,

Davy

I have pasted the SQL syntax below that I get from the error dialog box after the query wizard closes.

See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.SqlServer.DataMining.Office.Excel.QueryBuilder.QueryBuilderParameters.UpdateNestedColumnName()
at Microsoft.SqlServer.DataMining.Office.Excel.XLClientUIManager.DisplayAdvancedQueryBuilder(Object sender, WizardPageEventArgs e)
at Microsoft.SqlServer.DataMining.Office.Excel.Wizard.WizardPageBase.OnWizardPageAdvanced(WizardPageEventArgs e)
at Microsoft.SqlServer.DataMining.Office.Excel.Wizard.WizardForm.btnAdvanced_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************
mscorlib
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
-
Microsoft.SqlServer.DataMining.Office.Excel.DMClient
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3154.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.DataMining.Office.Excel.DMClient/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.DataMining.Office.Excel.DMClient.dll
-
Extensibility
Assembly Version: 7.0.3300.0
Win32 Version: 7.00.9466
CodeBase: file:///C:/WINDOWS/assembly/GAC/Extensibility/7.0.3300.0__b03f5f7f11d50a3a/Extensibility.dll
-
office
Assembly Version: 12.0.0.0
Win32 Version: 12.0.4518.1014
CodeBase: file:///C:/WINDOWS/assembly/GAC/office/12.0.0.0__71e9bce111e9429c/office.dll
-
Microsoft.SqlServer.DataMining.Office.Common
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3154.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.DataMining.Office.Common/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.DataMining.Office.Common.dll
-
System.Windows.Forms
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
-
System
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
-
System.Drawing
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
-
Microsoft.SqlServer.DataMining.Office.Excel.DMClientControls
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3154.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.DataMining.Office.Excel.DMClientControls/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.DataMining.Office.Excel.DMClientControls.dll
-
System.Xml
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
-
System.Configuration
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
-
zd8aixcw
Assembly Version: 9.0.242.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
-
stdole
Assembly Version: 7.0.3300.0
Win32 Version: 7.00.9466
CodeBase: file:///C:/WINDOWS/assembly/GAC/stdole/7.0.3300.0__b03f5f7f11d50a3a/stdole.dll
-
Microsoft.Office.Interop.Excel
Assembly Version: 12.0.0.0
Win32 Version: 12.0.4518.1014
CodeBase: file:///C:/WINDOWS/assembly/GAC/Microsoft.Office.Interop.Excel/12.0.0.0__71e9bce111e9429c/Microsoft.Office.Interop.Excel.dll
-
Microsoft.SqlServer.DataMining.Office.Excel.TableAnalytics
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3154.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.DataMining.Office.Excel.TableAnalytics/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.DataMining.Office.Excel.TableAnalytics.dll
-
Microsoft.SqlServer.DataMining.Office.Excel.Wizard
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3154.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.DataMining.Office.Excel.Wizard/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.DataMining.Office.Excel.Wizard.dll
-
Microsoft.AnalysisServices.AdomdClient
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.AnalysisServices.AdomdClient/9.0.242.0__89845dcd8080cc91/Microsoft.AnalysisServices.AdomdClient.dll
-
System.Data
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
-
System.Web
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Web/2.0.0.0__b03f5f7f11d50a3a/System.Web.dll
-
Microsoft.mshtml
Assembly Version: 7.0.3300.0
Win32 Version: 7.0.3300.0
CodeBase: file:///C:/WINDOWS/assembly/GAC/Microsoft.mshtml/7.0.3300.0__b03f5f7f11d50a3a/Microsoft.mshtml.dll
-
Microsoft.DataWarehouse.Interfaces
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.DataWarehouse.Interfaces/9.0.242.0__89845dcd8080cc91/Microsoft.DataWarehouse.Interfaces.dll
-
Microsoft.AnalysisServices.Viewers
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20DM%20Add-Ins/Microsoft.AnalysisServices.Viewers.dll
-
Microsoft.DataWarehouse
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20DM%20Add-Ins/Microsoft.DataWarehouse.DLL
-
Microsoft.AnalysisServices.Graphing
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20DM%20Add-Ins/Microsoft.AnalysisServices.Graphing.DLL
-
Microsoft.AnalysisServices.Controls
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20DM%20Add-Ins/Microsoft.AnalysisServices.Controls.DLL
-
Microsoft.SqlServer.CustomControls
Assembly Version: 9.0.242.0
Win32 Version: 9.00.2047.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.CustomControls/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.CustomControls.dll
-
Microsoft.NetEnterpriseServers.ExceptionMessageBox
Assembly Version: 9.0.242.0
Win32 Version: 9.00.1399.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.NetEnterpriseServers.ExceptionMessageBox/9.0.242.0__89845dcd8080cc91/Microsoft.NetEnterpriseServers.ExceptionMessageBox.dll
-
Microsoft.AnalysisServices.OleDbDM
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20DM%20Add-Ins/Microsoft.AnalysisServices.OleDbDM.DLL
-
CustomMarshalers
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_32/CustomMarshalers/2.0.0.0__b03f5f7f11d50a3a/CustomMarshalers.dll
-

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
<system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.

Can you try creating another non-association model (e.g. classification) and see if this problem goes away? It won't solve the real problem, but it will give us more insight.

Thanks

-Jamie

|||

Hello, Davy

You said:"I have pasted the SQL syntax below that I get from the error dialog box after the query wizard closes.", but I cannot see the SQL statement in the post. Sorry if I just missed it, do you mind posting it again?

On another note:

- is there any model visible in your current connection?

- what kind of model is selected by default when launching the query editor?

|||

Sorry about the confusion, but there is no SQL syntax...only what i pasted above.

I tried creating a model with another algorithm, but that does not change anything.

There are models visible with the current connection.

The model selected by default depends on which model i clicked on last in the manage wizard.

I believe this problem stems from the .Net Framework since the error dialog says:

Microsoft .NET Framework

Unhandled exception has occurred in a component in you application. If you click Continue, the applicaiton will ignore this error and attempt to continue.

Object reference not set to an instance of an object.

Details Continue

I currently have .NET 2.0 and .NET 1.1 installed on the computer. According to Add/Remove Program in the Control Panel, the last time .NET 2.0 was used was sometime last year. The Add/Remove Program has an option to repair .NET 2.0 . Should I use this?

excel data mining add-in for categorical data

Hi my friends,

I do have a problem with results of clustering algorithm with my categorical data.
In reality I have a big table with one's and zero's and I try to cluster according to 60 attributes.
I tries to cluster to two categories many times but I get only one cluster. What do you suggest. Which values can I change to the algorithm? Is there anything particular for categorical data ?

Thank for your help in advance.

Manolis

First, check Properties of your clustering model then AlgorithmParameters ->Set algorithm parameter; look at CLUSTER_COUNT parameter- it define the number of cluster in model- default is 10, maybe it has value 1.

Second explain the significance of your data with a set o sample so we can make a suggest.

|||

The way the cluster algorithm works is it starts from a semi-random starting point (it uses the data distributions and randomly jitters them) using the number of clusters you choose (or the number of auto-detected clusters). If, during the clustering operation, two clusters are coincident, it merges the clusters and creates a new randomized starting point. Of course, this can end up coincident with another cluster as well. In the end, any remaining coincident clusters are merged.

I could imagine a scenario where the data is distributed such that a probabilistic model of two clusters always results in merging, yet a model of more clusters could produce an interesting model. So one thing to try would be to increase the number of clusters to see what you have.

Another option is to not use the default probabilistic clustering method and switch to K-means. You will not be able to access this parameter through the Table Analysis Tool "Detect Categories" button or the simple "Cluster" button on the DM ribbon. You need to use the advanced "Create Model Manually" option where you explicitly select an algorithm and there's a button for parameters. The appropriate parameter is "CLUSTERING_METHOD".

HTH

-Jamie

excel data mining add-in for categorical data

Thank you very much for your previous answers conserning clustering algorithm.

I change a bit my data and now I can get two and three clusters as I wish.

But now I have a different question. I wish to keep my Structure and Model for further trials with different algorithm values, like cardinality. I tried the following

I selected in excel Data Mining and then the option cluster button.

Then in the cluster wizard I chose analysis service data source and I reform the query as I need adding only at the end

where "STAFF_YES" = 1. Then I choose the needed columns and target value. Finally I select browse model, enable drill through only and I finish the wizard and get some results, rather good. But i need to try again with different parameters.

I tried Business intelligent Studio to check my Analysis service. The structure were here but when I changed any parameter and I tried to reprocess I got the following messages

Processing Mining Structure 'try Structure_1' completed successfully.
Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00
Processing Dimension 'try Structure_1 ~MC-__RowIndex' completed successfully.
Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00
Processing Dimension Attribute '(All)' completed successfully.
Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00
Processing Dimension Attribute 'EPI_CELL_SINO_GOOD' completed successfully.
Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00
Processing Dimension Attribute 'EPI_CELL_SINO_LOOSE' completed successfully.
Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00
Errors and Warnings from Response
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
Internal error: An unexpected error occurred (file 'pcprocbinding.cpp', line 6645, function 'PCDBProcBinding:electOrChangeCartridge').
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'try Structure_1 ~MC-__RowIndex', Name of 'try Structure_1 ~MC-__RowIndex' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'EPI_CELL_SINO_LOOSE' attribute of the 'try Structure_1 ~MC-__RowIndex' dimension from the 'DMAddinsDB2' database was being processed.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
Internal error: An unexpected error occurred (file 'pcprocbinding.cpp', line 6645, function 'PCDBProcBinding:electOrChangeCartridge').
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'try Structure_1 ~MC-__RowIndex', Name of 'try Structure_1 ~MC-__RowIndex' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'EPI_CELL_SINO_GOOD' attribute of the 'try Structure_1 ~MC-__RowIndex' dimension from the 'DMAddinsDB2' database was being processed.

And no change to the structure take place.

Please help me if you can

Thank you in advnce

Best regards.

Manolis

We discovered this issue and it will be addressed in a future release but currently there is no workaround for this problem using BI Dev Studio.

Here is an alternate way of doing the same thing:

- in Excel, under the Data mining tab, select the "Advanced\Add model to mining structure" option

- create a new model using whatever algorithm (Clustering, I assume) and setting any parameters you might want.

The wizard will create a new model inside the same structure and process it.

If having multiple models does not bother your, you can leave them all inside the structure. Otherwise, you can delete the old models using the Manage Models option in the Data Mining tab.

Also, after executing the steps you mentioned in the BI Dev Studio, chances are that your mining structure is in an incosistent state. You can restore its original (processed) state by invoking, under Manage Models, Process structure with new data then point to the data source you used in creating the structure the first time

Hope this helps

sql

Excel as Frontend to SSAS 2005 Cube

Hi, there,

I am working on Excel as a front-end to a SSAS2005 cube. I intended to create a simple add-in that allows users to write back to the cube. Users will first retrieve data from the cube to pivot table and write back to the cube from the pivot table. I can find some sample source based on adodb and AS2000 but not adomd .net and SSAS2005. For the time being, information on SSAS2005 write-back from Excel seems very little. Not much references that I can refer to.

Anyone can shed some light on this? I would appreciate it if you can provide me some sample source in VBA using adomd .net or others that can demonstrate the write-back to a SSAS2005 cube. It would be great if the sample will be based on the AdventureWorksDW cube.

Thanks in advance.

Regards,
Yong Hwee

You have code that connects Excel 2003 with SSAS2005 for writeback in this book: "MS SQL Server 2005 Analysis Services 2005, Step By Step", chapter 10.

The code saples for writeback I have seen are in books, like this one.

Regards

Thomas Ivarsson

|||

Hi, Thomas,

Thank you for your reply. I will try to look for the book in my local library. One more thing, do you come across any reference on the Excel add-in, Cube Analysis particularly in the What-if Analysis other than the published technical whitepaper?

Thank you.

Regards,

Yong Hwee

|||

Regarding the Excel Add-In. No! I have only downloaded and tried it.

Have you heard that MS is working on a new server for budgeting and financial modelling?

ProClarity and Business Scorecard server will also be a part in this solution.

Regards

Thomas Ivarsson

|||

Hi. Thomas is referring to the new PerformancePoint sevrer from Microsoft. Here's a public posting on the topic.

http://office.microsoft.com/en-au/FX101550371033.aspx

Thomas - we should catch up sometime.

PaulG

|||

Hi, Thomas and Paul,

Thanks for your replies. FYI, I am working on a DW/BI project on SQL Server 2005 Enterprise. Main deliverables are a number of monthly reports. I intended to make use of SSAS 2005 to deliver dynamic reports - users are able to produce reports on their own anytime and they are free to pivot. Of course, one of the requirements is to be able to write back from Excel frontend. I found that there are not much references on this subject.

BTW, thanks for your help and SQL Server 2005 is a great product.

Thank you.

Regards,

Yong Hwee

Excel as Frontend to SSAS 2005 Cube

Hi, there,

I am working on Excel as a front-end to a SSAS2005 cube. I intended to create a simple add-in that allows users to write back to the cube. Users will first retrieve data from the cube to pivot table and write back to the cube from the pivot table. I can find some sample source based on adodb and AS2000 but not adomd .net and SSAS2005. For the time being, information on SSAS2005 write-back from Excel seems very little. Not much references that I can refer to.

Anyone can shed some light on this? I would appreciate it if you can provide me some sample source in VBA using adomd .net or others that can demonstrate the write-back to a SSAS2005 cube. It would be great if the sample will be based on the AdventureWorksDW cube.

Thanks in advance.

Regards,
Yong Hwee

You have code that connects Excel 2003 with SSAS2005 for writeback in this book: "MS SQL Server 2005 Analysis Services 2005, Step By Step", chapter 10.

The code saples for writeback I have seen are in books, like this one.

Regards

Thomas Ivarsson

|||

Hi, Thomas,

Thank you for your reply. I will try to look for the book in my local library. One more thing, do you come across any reference on the Excel add-in, Cube Analysis particularly in the What-if Analysis other than the published technical whitepaper?

Thank you.

Regards,

Yong Hwee

|||

Regarding the Excel Add-In. No! I have only downloaded and tried it.

Have you heard that MS is working on a new server for budgeting and financial modelling?

ProClarity and Business Scorecard server will also be a part in this solution.

Regards

Thomas Ivarsson

|||

Hi. Thomas is referring to the new PerformancePoint sevrer from Microsoft. Here's a public posting on the topic.

http://office.microsoft.com/en-au/FX101550371033.aspx

Thomas - we should catch up sometime.

PaulG

|||

Hi, Thomas and Paul,

Thanks for your replies. FYI, I am working on a DW/BI project on SQL Server 2005 Enterprise. Main deliverables are a number of monthly reports. I intended to make use of SSAS 2005 to deliver dynamic reports - users are able to produce reports on their own anytime and they are free to pivot. Of course, one of the requirements is to be able to write back from Excel frontend. I found that there are not much references on this subject.

BTW, thanks for your help and SQL Server 2005 is a great product.

Thank you.

Regards,

Yong Hwee

Excel add-in: invisible measures

I've a cube in Analysis Services 2005 with two measuregroups. Each group contains about 4 measures. Those measures are only used for making some calculated measures. Only the calculated measures have to be visible to the user. Whenever I put the property 'visible' on false for al the normal measures in the two measuregroups, the retrieval of the measures (selecting measures in dimension-tab) in excel-add-in takes too lang (more then one minute). The retrieval of the measures works fine in other front-en tools like proclarity.

Does anyone knows about this problem and knows a solution or a workaround?

Thanks,

ADL

Try to hide the measures you dont like to see using perspectives feature of Analysis Services.

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

|||Does Excel 2003 with PTS9 support perspectives?|||

The excel 2003 add-in for analysis services supports perspectives, but that gives no alternative:

It seems that I can't disable all measuregroups in a perspective. When you uncheck all measuregroups, the excel add-in will still show those measuregroups. The unchecking of just one measuresgroups works fine ?

By further investigation and looking at the profiler I noticed that the retrieval of de measures in Proclarity works fine, but the soapcall is somewhat different :

PROCLARITY : event subclass:MDXSCHEMA_MEASURES

<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><CATALOG_NAME>Demo Emmaus</CATALOG_NAME><CUBE_NAME>DWH Emmaus</CUBE_NAME></RestrictionList>

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Demo Emmaus</Catalog><MdxMissingMemberMode>Error</MdxMissingMemberMode><LocaleIdentifier>2057</LocaleIdentifier><DbpropMsmdMDXCompatibility>2</DbpropMsmdMDXCompatibility></PropertyList>

EXCEL ADD-IN : event subclass:MDXSCHEMA_MEASURES

<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><CUBE_NAME>DWH Emmaus</CUBE_NAME></RestrictionList>

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Demo Emmaus</Catalog><SafetyOptions>2</SafetyOptions><LocaleIdentifier>2057</LocaleIdentifier></PropertyList>

Is this giving anyone a clue of what can cause the long response time of retrieval of measures metadata in excel-add-in whenever the measuresvisibility is set to false?

Excel add-in: invisible measures

I've a cube in Analysis Services 2005 with two measuregroups. Each group contains about 4 measures. Those measures are only used for making some calculated measures. Only the calculated measures have to be visible to the user. Whenever I put the property 'visible' on false for al the normal measures in the two measuregroups, the retrieval of the measures (selecting measures in dimension-tab) in excel-add-in takes too lang (more then one minute). The retrieval of the measures works fine in other front-en tools like proclarity.

Does anyone knows about this problem and knows a solution or a workaround?

Thanks,

ADL

Try to hide the measures you dont like to see using perspectives feature of Analysis Services.

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

|||Does Excel 2003 with PTS9 support perspectives?|||

The excel 2003 add-in for analysis services supports perspectives, but that gives no alternative:

It seems that I can't disable all measuregroups in a perspective. When you uncheck all measuregroups, the excel add-in will still show those measuregroups. The unchecking of just one measuresgroups works fine ?

By further investigation and looking at the profiler I noticed that the retrieval of de measures in Proclarity works fine, but the soapcall is somewhat different :

PROCLARITY : event subclass:MDXSCHEMA_MEASURES

<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><CATALOG_NAME>Demo Emmaus</CATALOG_NAME><CUBE_NAME>DWH Emmaus</CUBE_NAME></RestrictionList>

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Demo Emmaus</Catalog><MdxMissingMemberMode>Error</MdxMissingMemberMode><LocaleIdentifier>2057</LocaleIdentifier><DbpropMsmdMDXCompatibility>2</DbpropMsmdMDXCompatibility></PropertyList>

EXCEL ADD-IN : event subclass:MDXSCHEMA_MEASURES

<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><CUBE_NAME>DWH Emmaus</CUBE_NAME></RestrictionList>

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Demo Emmaus</Catalog><SafetyOptions>2</SafetyOptions><LocaleIdentifier>2057</LocaleIdentifier></PropertyList>

Is this giving anyone a clue of what can cause the long response time of retrieval of measures metadata in excel-add-in whenever the measuresvisibility is set to false?

excel add-in

Does the add in for excel work against RS reports?
Does it work against local cube files?You cannot use RS reports as a data source within the add-in, however, you
can connect to a local cube file.
RDA Corp
Business Intelligence Evangelist Leader
www.rdacorp.com
"swetha" wrote:

> Does the add in for excel work against RS reports?
> Does it work against local cube files?
>|||We have an Excel Addin for Reporting Services
Please mail us at www.gmsbv.nl and i will mail you fro free
Regards, Marco
Steve Mann schreef:
[vbcol=seagreen]
> You cannot use RS reports as a data source within the add-in, however, you
> can connect to a local cube file.
>
> --
> RDA Corp
> Business Intelligence Evangelist Leader
> www.rdacorp.com
>
> "swetha" wrote:
>

Excel 2007 DataMining Add-in Database on SQL Server 2005 destroyed by using SQL Server Managment

Hello,

i have made some Data Mining Model Examples in Excel 2007 (not temporarily!). They where there after leaving an re-opening Excel. I have used them several times. Then I want to look, if I can see them also via SQL Server Managment Studio in the Analysis Services. There where nothing in the DMAddInDB in Analysis Services.

And after this, in Excel my DataMining Models have disappeared and all Models i have made since this disappeared also.

Perhaps I have destroyed the database. But will this happen every time? Can I share Data Mining Models I have made with Excel with Projects in SQL Server Analysis Services?

Thanks

Berenice

This should not happen and the persistent models created in Excel should be accessible via any other clients (Management Studio, BI Dev Studio), assuming that the permission set is correct (a model may be hidden by the fact that the current user does not have read permissions on that model, but I assume it is not the case in your situation)

Temporary models (generated by Table Analysis Tools or by explicitly selecting Temporary in the Data Mining client add-in) will disappear when Excel is closed (or when the connection is changed). Again, if you found them after re-opening Excel, this is not the case.

So, it looks like either a permissions issue or the database was accidentally destroyed/cleared

|||

I have just spoken with another customer with the same issue. However, in their case, it turned out that they had used the (default) database for their Analysis Services connection in Excel rather than selecting DMAddInDB or another named database. As a result they were not seeing the models where they expected them.

Could this be the cause here? Let us know if you have been able to reproduce the problem, Berenice.

Thanks

|||

Hello,

good hint! When leaving Excel and re-opening Excel another time the connection is established automatically with the SQL Server 2005 AS and I don′t have looked at the connection properties which where changes from DMAddInDB to default (or "Standard" in the german version).

An with this hint I have finally found the solution for another problem I have searched for today. I was not able to construct a Mining Modell on Analysis Services Database, I was not able to choose the right database/datasource. I have seen another AS Database (perhaps the default one) an could not change to another database.

When I have changed the dataMining connection to the right database as actual I could use my analysis services database.

Really helpful, thanks a lot!

Berenice

Excel 2007 Data Mining Add-in Advance Create Mining Model Question

Hi,

I am trying to model data in analysis services with the Advance Create Mining Model function in the excel addin. I am having trouble creating an association model that works like the Associate button above the Advanced button.

The format of my data is like this

OrderID Product

100 Bike

100 Helmet

100 Shoes

200 Helmet

200 basketball

200 Bat

300 Shoes

300 Socks

The associate button works perfectly since it asks me which column is the transaction id (orderid) and which column I am trying to predict (product). The advanced create mining model asks me to determine what the columns are...

OrderID=key Product=Input+Predict?

When I run the advance create mining model associate, I get a browser that gives me no rules and the support for only one item itemset (each product but no combination of products).

Does anyone know what I have to do to get it to work like the associate button?

The Associate task performs a trick in defining the mining model. For data like yours, the model is defined as:

(

OrderID TEXT KEY,

Product_Table TABLE PREDICT

(

Product TEXT KEY

)

)

(it contains a nested Product_Table column, which contains all the products for one given order ID).

After that, in training, the Excel table is joined with itself to generate the shape required for model, which is :

OrderID Products (table)

100 Bike

Helmet

Shoes

The Advanced Create Mining Model functionality does not allow modeling of nested tables. Consequently, what you get by running the Advanced task is a model like:

(

OrderID TEXT KEY,

Product TEXT DISCRETE PREDICT

)

which cannot find any interesting rules.

Here is an easy workaround for this problem:

- start by using the Associate task to create a model with the appropriate modeling (nested table)

- use Advanced\Add Model To Structure to add a new model in the same mining structure (this allows you to customize your model, set different parameters etc.) on top of the same data as the first model.

- using the Manage Models task, delete the original model and use the newly created one

Alternatively, you could use DMX statements to define and train the model. The DMX statements would have to be entered manually (choose Query\Advanced then Edit Query to get to manual editing of the DMX statement). Please let me know if you need details for this

|||

Hello Bogdan,

I am still having difficulty connecting to analysis services data sourse. I have tried the easy workaround and I have done all of the steps above. I am a little confused as to what i should do after i have deleted the old model.

I went to manage models->Process this mining model with new data->Selected the data i wanted from analysis services

The dialog box prompted me to specify mapping between structure columns and input columns.

Mining Columns Table Columns

OrderID OrderID

Product_Table Choices: (OrderID, Product) No Product_Table

I tried only having the OrderID in the table column (leaving the other choice blank) and I ended up with "Error too many attributes for counting correlations"

Using OrderID and Product in the table column gave me: Error (Data Mining): INSERT INTO error: The '[Product_Table],[Product]' nested table key column is not bound to an input rowset column.

Can you give me the details for the DMX statements?

Thanks,

Davy

|||

First -- creating a new model in the same mining structure will process the new model with the structure data. There is no need to re-process it with new data (the model is already processed). Once you delete the (sibling) model generated by the Association task, you have the model you just created and you can start using it.

Now, the dialog for Process Model with new Data, like most of the features in the Excel add-in, is designed mostly for the tabular data in Excel (i.e. not for nested tables). If you need to process the model with new data, the easiest way is to run the workaround again (use Association task to get the model and structure, then add your own model to the structure, using the algorithm and parameters of your choice).

Do you want to get DMX statements to do this task manually?

|||

Dear Bogdan,

I am interested in using the association algorithm on data that has not been imported into Excel. The association algorithm is the only algorithm that doesn't allow me to mine data from a nested table on a server. The other algorithm buttons (Classify,Estimate,Forecast,Cluster) allow me to mine data that hasn't been imported into excel. (Will future versions of the add-in include this option for the associate algorithm?)

Right now, I am importing 1MM transactions into Excel (Due to the 1MM row limit) and using the association algorithm button to mine/browse the data. I want to test my hypothesis from this 1MM transactions with 100MM new transactions from a nested table in analysis services to confirm my hypothesis (My company has millions of transactions daily).

Is there a DMX code where I can input:

The Connection

The Command Query ie.
Select Top 1000000 "OrderID","Product","DateID"
From "dwMBA"."dbo"."vOrderItems"

and get the browse window (Support,Rules,Network)

I am unfamiliar with DMX

Davy

|||

Now I understand.

One solution is to create the model once using BI Developer Studio (the development tool coming with Analysis Services). Then, the model can be re-processed at any time even from Excel. You can use Managed to Clean the structure and reprocess the structure with original data. Further more, in your BI Development Studio solution you could:

- create a data source view with a named query which returns only the transactions in the last 10 days

(e.g SELECT * FROM vOrderItems WHERE DateID > DATEADD(day, -10, GETDATE()) )

- define a mining structure on top of this named query. The mining structure will use the named query as both case and nested table

- build an Association Rules mining model

- periodically, use Manage from Excel add-ins to clear the mining structure data (unprocess), then process with original data (effectively using the most recent data) or Browse to inspect the model's rules

Another possible solution, using only Excel add-ins: use the Associate task to create a model with necessary flags and parameters. After that, clear the mining structure/model and reprocess it using data from the relational database (any number of rows, because the data will nto be copied to Excel).

Here is the step-by-step solution

1. Make sure on your Analysis Services server, in the current database, there is a data source object pointing to your relational table (the one containing the transactions)

Creating such a datasource only happens once and establishes a connection between Analysis Services and your database. You could create such a data source object using the BI Dev Studio. If your database is SQL Server 2005, you could also create such a datasource from Excel add-ins. Anyway, let me know if you need help on this.

2. Just as you started, fetch a small sample of data (number of rows does not matter at this point) and copy this data in Excel. The data must contain, based on your query, at least the OrderID (transaction identifier) and Product (transaction item) columns

3. Use Associate to create an Association Rules model on top of your sample data. Let's assume you create a model named ARM in a structure called ARS.

4. Once the model is deployed on the server, use Manage Models\Clear this mining structure on the ARS structure to unprocess it.

5. Click Query

6. Click Advanced ... (bottom left of the dialog)

7. Click Edit Query (Click Yes when a warning asks you if you want to continue)

8. Paste the query below, after modifying it to include your data source name

INSERT INTO MINING STRUCTURE [ARS]
(

[OrderID],
[Product_Table]
(
SKIP,
[Product]
)
)
SHAPE {
OpenQuery([Adventure Works DW], 'SELECT OrderID
FROM vOrderItems ORDER BY OrderID') }
APPEND
( {
OpenQuery([Adventure Works DW], 'SELECT OrderID, Product
FROM vOrderItems ORDER BY OrderID') }
RELATE
[OrderNumber] TO
[OrderNumber]
) AS
[Model_Table]

Note the ORDER BY fragments on the relational queries (required for the statement to work correctly) and the SKIP keyword which indicates the statement that OrderID from the second query is only used to shape the rowsets

9. Click Finish

10. Choose New Worksheet or Existing Worksheet -- it does not matter (this query does not return results)

11. After the execution completes, use Browse to check your patterns

|||Do you have an email I can private message you?|||Sure: bogdanc at microsoft dot comsql

Excel 2002/2003 Add-in for SQL Server Analysis

Is "Excel 2002/2003 Add-in for SQL Server Analysis
Services" supported under Win XP SP2 ?
When I try to install it I get "The advertised application
will not be installed because it is unsafe. Contact your
administator to change the installation user interface
option of the package to basec".
TIA
Stephen
Try this server
privatenews.microsoft.com
and this newsgroup
microsoft.private.offsolaccelerators
...but I have XP and no problems - but perhaps there are different XP
versions - think I have professional.
Other than that it could sound like the security settings on your machine
that blocks it.
--Michael
"Stephen" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:199801c4abf8$f580baf0$a601280a@.phx.gbl...
> Is "Excel 2002/2003 Add-in for SQL Server Analysis
> Services" supported under Win XP SP2 ?
> When I try to install it I get "The advertised application
> will not be installed because it is unsafe. Contact your
> administator to change the installation user interface
> option of the package to basec".
> TIA
> Stephen
>