ProClarity Slicersel parameters

Hi,

I am using ProClarity 6.1 and Enterprise version of analysis server 2005. (not sure if I need an proclarity update or a fix for ProClarity because i guess there is a newer version 6.2)

Because of one of the performance optimizations that I did after following some blogs on the net, I can't specify slicerSel parameters to my proclarity graphs! Let me explain:

I have a slicer in Proclarity graph for which I used to specify a url querystring parameter as slicerSel=xxxxxx. This was working until I modified the dimension.

The modification: initially my dimension had the following attributes: Key, Name. Now I just have one attribute Name, whose key column points to KEY (db) and Name column points to NAME (db). because of this my slicerSel parameters do not work.

I checked the html of proclarity by doing an view souce and found the following difference.

before:

<option value ="[CLIENTNAME].[NAME].&amp;[ABCDE FGHIJK]">ABCDE FGHIJK<option>

After modifing the Keycolumn and Namecolumn:

<option value ="[CLIENTNAME].[NAME].&amp;[4.94735E6]">ABCDE FGHIJK</option>

Now I can't specify the slicer using the number 4.94735E6 which i dont think is the key (its in scientific format).

My question is, Is there any way out to specify the slicer as ABCDE FGHIJK (Name) as before ?

If it is not possible, shall I add another attribute to the dimension called Key and then seperate the Key and Name from the single attribute? will this impact the performance? especially because the dimension will have another attribute which contains just the key, which will not be displayed but used in the relationships. Is there any specific way to design this? (Key is the primary key column of the database table, which is used for storing dimension values and referenced in the Fact tables).

Regards

Your key is 494735.

The only way you will get the name there is to set-up the name only as a separate attribute. Then the name will be used as a key, slightly less efficient. ProClarity generates the MDX with the key element of the attribute value that is selected from the slicer.

You can also change the data type of the key when creating the attributes in Visual Studio. Sometimes it defaults to a double if the data is sourced from a view rather than a table.

ProClarity Expert Needed for our BI Team - Texas Job Opportunity

Hi All,

We are starting on a tool called Proclarity, which is used on top of Microsoft SQL Server Analysis Services. Having this as the basic idea, I’m not clear on some information as listed below: Any clarification would be very helpful.

1 Having SSRS (SQL Server Reporting Service) available to perform reports, what is Proclarity for Reporting Services. What it does by integrating with SSRS and its advantages. How is it different from Proclarity Web/Desktop professional.

2 Having Business Scorecard manager from Microsoft, what is the purpose of Proclarity KPI Designer, its advantages/disadvantages over BSM? Still proclarity has one more add-on Proclarity for BSM (not sure why we need this)

3 Proclarity Selector – Purpose of this tool?

4. Why is that Proclarity Dashboard server? We can create a view of dashboard using Sharepoint. Not sure about the advantage of this.

Regards, Kart

Hello!

1. This is a project that is added to the SSRS designer in BI-DEV studio. It will start up a ProClarity professional interface in Reporting services and also help you with formatting the report.

2. The KPI-designer is a tool for building and saving KPI:s in ProClarity Analytics server. Its main use is to build KPI:s on top of SSAS2005 cubes. Business scorecard manager is a tool for building balanced scorecards. The main difference is that BSM can use weights for KPI:s and also create hierarchies for KPI:s. BSM can do this with more data sources than ProClarity.

3. This tool helps you with building named sets, like the top 10 best selling products.

4. PDS is a dashboard server. Sharepoint can do a little more than that. The main advantage with PDS is that it is easy to create and visualize KPI:s from Analytics server, without having to write MDX.

HTH

Thomas Ivarsson

|||

Hi Thomas,

Thanks for the Info.

1. When you say Proclarity for Reporting Services.is a project used for formatting reports, how it benifits us. We can do formatting in BI studio of SSRS.

2. When you say BSM can create Hierarchies for KPIs, it is like by adding a KPI to scorecard and adding Row Members (in scorecard view). Is my understanding correct. Doesn't this come automatically in proclarify when we define a KPI using a measure - because the we would have defined hierarchies in cube itself.

3. I hope it is added as a add-on (menu item in toolbar) of Proclarity Designer

4. Got it.

Regards, kart

|||

1. The ProClarity designer in SSRS2005 is better than Reporting Services own. It is a matter of taste.

2. This is from memory, but in BSM you create objectives and place one or serveral KPI:s in that objective. This objective is a KPI that is defined by the KPI:s mapped to it. In ProClarity and SSAS2005(the KPI-tab) the KPI:s you define are not presented in hierarchies but flat. Measures do not have hierarchies in SSAS2005.

3. Selector is placed on the toolbar. It is a part of the Business Logic Server if you install that.

Regards

Thomas Ivarsson

|||

Hi Thomas,

Thanks again and I got good clarity on Proclarity now.

2. Can I conclude like -KPI definition through BSM is better than doing the same using Proclarity KPI Designer and Defining KPI in the Cube itself. (But, still go ahed using Proclarity for Detailed analysis of any value that is defined by KPI)

Regards, Kart

|||

Hi,

In my humble opinion, you should not prefer to use BSM KPIs when you plan to use SSAS2005 as target.

BSM KPIs have many advantatges if you need to define KPIs based on ODBC datasources, Excel or just type the values manually.

However, if the source for the KPI data is SSAS, I will define the KPI at SSAS, making it centrally managed and reusable in other places (like SSRS), and import the KPI tp BSM (using the KPIUtil tool).

HTH,

Jordi Rambla

Solid Quality Learning

|||

I found your blog/contact information searching Google.com for BI Consultants with ProClarity expertise. Are you looking for a new opportunity? If so, we would like to consider you for a Senior BI job opportunity. If you are not looking at this time, then I will continue to save your contact information and re-contact you at a later date. We are not a staffing company, we are the employer.

Catapult Systems is a Microsoft Gold Partner with locations in Austin, Dallas, Houston, San Antonio and in Tampa, Florida. We were named the #2 BEST COMPANY TO WORK FOR in Texas by TEXAS MONTHLY Magazine (January 2006) and named the #1 Medium size COMPANY TO WORK for in Austin by the Austin Business Journal for 2005. Please visit our website to learn more about us – www.catapultsystems.com.

If hired fulltime, we pay you $500 for any referrals that are hired.

I hope to hear from you soon!

Best Regards,

Lynsey Moore

Lynsey Moore

Recruiting Specialist

713.395.7150 | Phone

713.395-7130 | Fax

www.catapultsystems.com

CATAPULT SYSTEMS INC.

ENABLING BUSINESS THROUGH TECHNOLOGY

|||

I found your blog/contact information searching Google.com for BI Consultants with ProClarity expertise. Are you looking for a new opportunity? If so, we would like to consider you for a Senior BI job opportunity. If you are not looking at this time, then I will continue to save your contact information and re-contact you at a later date. We are not a staffing company, we are the employer.

Catapult Systems is a Microsoft Gold Partner with locations in Austin, Dallas, Houston, San Antonio and in Tampa, Florida. We were named the #2 BEST COMPANY TO WORK FOR in Texas by TEXAS MONTHLY Magazine (January 2006) and named the #1 Medium size COMPANY TO WORK for in Austin by the Austin Business Journal for 2005. Please visit our website to learn more about us – www.catapultsystems.com.

If hired fulltime, we pay you $500 for any referrals that are hired.

I hope to hear from you soon!

Best Regards,

Lynsey Moore

Lynsey Moore

Recruiting Specialist

713.395.7150 | Phone

713.395-7130 | Fax

www.catapultsystems.com

CATAPULT SYSTEMS INC.

ENABLING BUSINESS THROUGH TECHNOLOGY

|||

Hello Jordi,

I found your blog/contact information searching Google.com for BI Consultants with ProClarity expertise. Are you looking for a new opportunity? If so, we would like to consider you for a Senior BI job opportunity. If you are not looking at this time, then I will continue to save your contact information and re-contact you at a later date. We are not a staffing company, we are the employer.

Catapult Systems is a Microsoft Gold Partner with locations in Austin, Dallas, Houston, San Antonio and in Tampa, Florida. We were named the #2 BEST COMPANY TO WORK FOR in Texas by TEXAS MONTHLY Magazine (January 2006) and named the #1 Medium size COMPANY TO WORK for in Austin by the Austin Business Journal for 2005. Please visit our website to learn more about us – www.catapultsystems.com.

If hired fulltime, we pay you $500 for any referrals that are hired.

I hope to hear from you soon!

Best Regards,

Lynsey Moore

Lynsey Moore

Recruiting Specialist

713.395.7150 | Phone

713.395-7130 | Fax

www.catapultsystems.com

CATAPULT SYSTEMS INC.

ENABLING BUSINESS THROUGH TECHNOLOGY

Proclarity and SQL Server 2005

Hi All,

We are starting on a tool called Proclarity, which is used on top of Microsoft SQL Server Analysis Services. Having this as the basic idea, I’m not clear on some information as listed below: Any clarification would be very helpful.

1 Having SSRS (SQL Server Reporting Service) available to perform reports, what is Proclarity for Reporting Services. What it does by integrating with SSRS and its advantages. How is it different from Proclarity Web/Desktop professional.

2 Having Business Scorecard manager from Microsoft, what is the purpose of Proclarity KPI Designer, its advantages/disadvantages over BSM? Still proclarity has one more add-on Proclarity for BSM (not sure why we need this)

3 Proclarity Selector – Purpose of this tool?

4. Why is that Proclarity Dashboard server? We can create a view of dashboard using Sharepoint. Not sure about the advantage of this.

Regards, Kart

Hello!

1. This is a project that is added to the SSRS designer in BI-DEV studio. It will start up a ProClarity professional interface in Reporting services and also help you with formatting the report.

2. The KPI-designer is a tool for building and saving KPI:s in ProClarity Analytics server. Its main use is to build KPI:s on top of SSAS2005 cubes. Business scorecard manager is a tool for building balanced scorecards. The main difference is that BSM can use weights for KPI:s and also create hierarchies for KPI:s. BSM can do this with more data sources than ProClarity.

3. This tool helps you with building named sets, like the top 10 best selling products.

4. PDS is a dashboard server. Sharepoint can do a little more than that. The main advantage with PDS is that it is easy to create and visualize KPI:s from Analytics server, without having to write MDX.

HTH

Thomas Ivarsson

|||

Hi Thomas,

Thanks for the Info.

1. When you say Proclarity for Reporting Services.is a project used for formatting reports, how it benifits us. We can do formatting in BI studio of SSRS.

2. When you say BSM can create Hierarchies for KPIs, it is like by adding a KPI to scorecard and adding Row Members (in scorecard view). Is my understanding correct. Doesn't this come automatically in proclarify when we define a KPI using a measure - because the we would have defined hierarchies in cube itself.

3. I hope it is added as a add-on (menu item in toolbar) of Proclarity Designer

4. Got it.

Regards, kart

|||

1. The ProClarity designer in SSRS2005 is better than Reporting Services own. It is a matter of taste.

2. This is from memory, but in BSM you create objectives and place one or serveral KPI:s in that objective. This objective is a KPI that is defined by the KPI:s mapped to it. In ProClarity and SSAS2005(the KPI-tab) the KPI:s you define are not presented in hierarchies but flat. Measures do not have hierarchies in SSAS2005.

3. Selector is placed on the toolbar. It is a part of the Business Logic Server if you install that.

Regards

Thomas Ivarsson

|||

Hi Thomas,

Thanks again and I got good clarity on Proclarity now.

2. Can I conclude like -KPI definition through BSM is better than doing the same using Proclarity KPI Designer and Defining KPI in the Cube itself. (But, still go ahed using Proclarity for Detailed analysis of any value that is defined by KPI)

Regards, Kart

|||

Hi,

In my humble opinion, you should not prefer to use BSM KPIs when you plan to use SSAS2005 as target.

BSM KPIs have many advantatges if you need to define KPIs based on ODBC datasources, Excel or just type the values manually.

However, if the source for the KPI data is SSAS, I will define the KPI at SSAS, making it centrally managed and reusable in other places (like SSRS), and import the KPI tp BSM (using the KPIUtil tool).

HTH,

Jordi Rambla

Solid Quality Learning

|||

I found your blog/contact information searching Google.com for BI Consultants with ProClarity expertise. Are you looking for a new opportunity? If so, we would like to consider you for a Senior BI job opportunity. If you are not looking at this time, then I will continue to save your contact information and re-contact you at a later date. We are not a staffing company, we are the employer.

Catapult Systems is a Microsoft Gold Partner with locations in Austin, Dallas, Houston, San Antonio and in Tampa, Florida. We were named the #2 BEST COMPANY TO WORK FOR in Texas by TEXAS MONTHLY Magazine (January 2006) and named the #1 Medium size COMPANY TO WORK for in Austin by the Austin Business Journal for 2005. Please visit our website to learn more about us – www.catapultsystems.com.

If hired fulltime, we pay you $500 for any referrals that are hired.

I hope to hear from you soon!

Best Regards,

Lynsey Moore

Lynsey Moore

Recruiting Specialist

713.395.7150 | Phone

713.395-7130 | Fax

www.catapultsystems.com

CATAPULT SYSTEMS INC.

ENABLING BUSINESS THROUGH TECHNOLOGY

|||

I found your blog/contact information searching Google.com for BI Consultants with ProClarity expertise. Are you looking for a new opportunity? If so, we would like to consider you for a Senior BI job opportunity. If you are not looking at this time, then I will continue to save your contact information and re-contact you at a later date. We are not a staffing company, we are the employer.

Catapult Systems is a Microsoft Gold Partner with locations in Austin, Dallas, Houston, San Antonio and in Tampa, Florida. We were named the #2 BEST COMPANY TO WORK FOR in Texas by TEXAS MONTHLY Magazine (January 2006) and named the #1 Medium size COMPANY TO WORK for in Austin by the Austin Business Journal for 2005. Please visit our website to learn more about us – www.catapultsystems.com.

If hired fulltime, we pay you $500 for any referrals that are hired.

I hope to hear from you soon!

Best Regards,

Lynsey Moore

Lynsey Moore

Recruiting Specialist

713.395.7150 | Phone

713.395-7130 | Fax

www.catapultsystems.com

CATAPULT SYSTEMS INC.

ENABLING BUSINESS THROUGH TECHNOLOGY

|||

Hello Jordi,

I found your blog/contact information searching Google.com for BI Consultants with ProClarity expertise. Are you looking for a new opportunity? If so, we would like to consider you for a Senior BI job opportunity. If you are not looking at this time, then I will continue to save your contact information and re-contact you at a later date. We are not a staffing company, we are the employer.

Catapult Systems is a Microsoft Gold Partner with locations in Austin, Dallas, Houston, San Antonio and in Tampa, Florida. We were named the #2 BEST COMPANY TO WORK FOR in Texas by TEXAS MONTHLY Magazine (January 2006) and named the #1 Medium size COMPANY TO WORK for in Austin by the Austin Business Journal for 2005. Please visit our website to learn more about us – www.catapultsystems.com.

If hired fulltime, we pay you $500 for any referrals that are hired.

I hope to hear from you soon!

Best Regards,

Lynsey Moore

Lynsey Moore

Recruiting Specialist

713.395.7150 | Phone

713.395-7130 | Fax

www.catapultsystems.com

CATAPULT SYSTEMS INC.

ENABLING BUSINESS THROUGH TECHNOLOGY

Proclarity and SQL Server 2005

Hi All,

We are starting on a tool called Proclarity, which is used on top of Microsoft SQL Server Analysis Services. Having this as the basic idea, I’m not clear on some information as listed below: Any clarification would be very helpful.

1 Having SSRS (SQL Server Reporting Service) available to perform reports, what is Proclarity for Reporting Services. What it does by integrating with SSRS and its advantages. How is it different from Proclarity Web/Desktop professional.

2 Having Business Scorecard manager from Microsoft, what is the purpose of Proclarity KPI Designer, its advantages/disadvantages over BSM? Still proclarity has one more add-on Proclarity for BSM (not sure why we need this)

3 Proclarity Selector – Purpose of this tool?

4. Why is that Proclarity Dashboard server? We can create a view of dashboard using Sharepoint. Not sure about the advantage of this.

Regards, Kart

Hello!

1. This is a project that is added to the SSRS designer in BI-DEV studio. It will start up a ProClarity professional interface in Reporting services and also help you with formatting the report.

2. The KPI-designer is a tool for building and saving KPI:s in ProClarity Analytics server. Its main use is to build KPI:s on top of SSAS2005 cubes. Business scorecard manager is a tool for building balanced scorecards. The main difference is that BSM can use weights for KPI:s and also create hierarchies for KPI:s. BSM can do this with more data sources than ProClarity.

3. This tool helps you with building named sets, like the top 10 best selling products.

4. PDS is a dashboard server. Sharepoint can do a little more than that. The main advantage with PDS is that it is easy to create and visualize KPI:s from Analytics server, without having to write MDX.

HTH

Thomas Ivarsson

|||

Hi Thomas,

Thanks for the Info.

1. When you say Proclarity for Reporting Services.is a project used for formatting reports, how it benifits us. We can do formatting in BI studio of SSRS.

2. When you say BSM can create Hierarchies for KPIs, it is like by adding a KPI to scorecard and adding Row Members (in scorecard view). Is my understanding correct. Doesn't this come automatically in proclarify when we define a KPI using a measure - because the we would have defined hierarchies in cube itself.

3. I hope it is added as a add-on (menu item in toolbar) of Proclarity Designer

4. Got it.

Regards, kart

|||

1. The ProClarity designer in SSRS2005 is better than Reporting Services own. It is a matter of taste.

2. This is from memory, but in BSM you create objectives and place one or serveral KPI:s in that objective. This objective is a KPI that is defined by the KPI:s mapped to it. In ProClarity and SSAS2005(the KPI-tab) the KPI:s you define are not presented in hierarchies but flat. Measures do not have hierarchies in SSAS2005.

3. Selector is placed on the toolbar. It is a part of the Business Logic Server if you install that.

Regards

Thomas Ivarsson

|||

Hi Thomas,

Thanks again and I got good clarity on Proclarity now.

2. Can I conclude like -KPI definition through BSM is better than doing the same using Proclarity KPI Designer and Defining KPI in the Cube itself. (But, still go ahed using Proclarity for Detailed analysis of any value that is defined by KPI)

Regards, Kart

|||

Hi,

In my humble opinion, you should not prefer to use BSM KPIs when you plan to use SSAS2005 as target.

BSM KPIs have many advantatges if you need to define KPIs based on ODBC datasources, Excel or just type the values manually.

However, if the source for the KPI data is SSAS, I will define the KPI at SSAS, making it centrally managed and reusable in other places (like SSRS), and import the KPI tp BSM (using the KPIUtil tool).

HTH,

Jordi Rambla

Solid Quality Learning

|||

I found your blog/contact information searching Google.com for BI Consultants with ProClarity expertise. Are you looking for a new opportunity? If so, we would like to consider you for a Senior BI job opportunity. If you are not looking at this time, then I will continue to save your contact information and re-contact you at a later date. We are not a staffing company, we are the employer.

Catapult Systems is a Microsoft Gold Partner with locations in Austin, Dallas, Houston, San Antonio and in Tampa, Florida. We were named the #2 BEST COMPANY TO WORK FOR in Texas by TEXAS MONTHLY Magazine (January 2006) and named the #1 Medium size COMPANY TO WORK for in Austin by the Austin Business Journal for 2005. Please visit our website to learn more about us – www.catapultsystems.com.

If hired fulltime, we pay you $500 for any referrals that are hired.

I hope to hear from you soon!

Best Regards,

Lynsey Moore

Lynsey Moore

Recruiting Specialist

713.395.7150 | Phone

713.395-7130 | Fax

www.catapultsystems.com

CATAPULT SYSTEMS INC.

ENABLING BUSINESS THROUGH TECHNOLOGY

|||

I found your blog/contact information searching Google.com for BI Consultants with ProClarity expertise. Are you looking for a new opportunity? If so, we would like to consider you for a Senior BI job opportunity. If you are not looking at this time, then I will continue to save your contact information and re-contact you at a later date. We are not a staffing company, we are the employer.

Catapult Systems is a Microsoft Gold Partner with locations in Austin, Dallas, Houston, San Antonio and in Tampa, Florida. We were named the #2 BEST COMPANY TO WORK FOR in Texas by TEXAS MONTHLY Magazine (January 2006) and named the #1 Medium size COMPANY TO WORK for in Austin by the Austin Business Journal for 2005. Please visit our website to learn more about us – www.catapultsystems.com.

If hired fulltime, we pay you $500 for any referrals that are hired.

I hope to hear from you soon!

Best Regards,

Lynsey Moore

Lynsey Moore

Recruiting Specialist

713.395.7150 | Phone

713.395-7130 | Fax

www.catapultsystems.com

CATAPULT SYSTEMS INC.

ENABLING BUSINESS THROUGH TECHNOLOGY

|||

Hello Jordi,

I found your blog/contact information searching Google.com for BI Consultants with ProClarity expertise. Are you looking for a new opportunity? If so, we would like to consider you for a Senior BI job opportunity. If you are not looking at this time, then I will continue to save your contact information and re-contact you at a later date. We are not a staffing company, we are the employer.

Catapult Systems is a Microsoft Gold Partner with locations in Austin, Dallas, Houston, San Antonio and in Tampa, Florida. We were named the #2 BEST COMPANY TO WORK FOR in Texas by TEXAS MONTHLY Magazine (January 2006) and named the #1 Medium size COMPANY TO WORK for in Austin by the Austin Business Journal for 2005. Please visit our website to learn more about us – www.catapultsystems.com.

If hired fulltime, we pay you $500 for any referrals that are hired.

I hope to hear from you soon!

Best Regards,

Lynsey Moore

Lynsey Moore

Recruiting Specialist

713.395.7150 | Phone

713.395-7130 | Fax

www.catapultsystems.com

CATAPULT SYSTEMS INC.

ENABLING BUSINESS THROUGH TECHNOLOGY

processing speed, optimizations for AMD Opteron

Does anyone know if Analysis Services has binaries optimized for AMD Opteron? The reason I ask is that I am seeing a dramatic performance difference between the 2 systems:

System: Dual Xeon Irwingdale 3.4 GHz, Chipset E7525, 4GB Ram
OS: Windows XP Pro SP2
Fact Rows: 20,000,000
Aggregations: 1300
Processing Time: 3 Hrs

System: Dual AMD Opteron 246 (2.0 GHz), AMD Chipset 8000 (MB Tyan S2885). 4 GB RAM
OS: Windows 2003 Enterprise (SP1)
Fact Rows: 20,000,000 (same data set as 1st case)
Aggregations: 1300
Processing Time: 6-7 Hrs

Both test environments have the:
Same RAM speed
Same model HDDs

Is this difference due to any lack of AMD-specific optimizations in SSAS binaries? or some other reason?

Also is there any way to reduce the thread priority on SSAS binary during optimization? It seems to almost completely hose the machine (windows 2003 ent.) during processing. When attempted to change processing priority through task manager I got access denier error (logged in as admin)Did you test under the same OS and 100% equial another options?

Such difference as you have acounted seems to be too screwy.|||

Vladimir Chtepa wrote:

Did you test under the same OS and 100% equial another options?

Such difference as you have acounted seems to be too screwy.

The operating systems are different - Windows XP SP2 vs. Windows 2003 SP1.

SSAS options are all same.

Also when its processing on Windows 2003 SP1/AMD Opteron test environment it is much harder to use any other applications (open windows, task manager, etc) than on the Windows XP. The AMD/Windows2003 gets very hosed/hung up when processing at 100% CPU.

On other hand the IntelXeon/Win XP case it still responsive even when processing faster and at 100% CPU for hours.

Processing Mining Models & Structures via SSIS

Still new to DM and SSIS...anyand all help is greatly appreciated!

In SSIS they say that you can use the Analysis Services Processing Task to process a mining model/mining structure, however, I do not see where you can give it a relational table to work off of. I know that I can use a data flow to do this but I wanted to go a different route if I could to process my models as I don't really necessarily need the data flow as what I am tring to do is pretty simple.

That brings me to a more general question, what is the best method for training your models using SSIS? I am building a new model everytime the package runs using some variables and the DDL task, running a query on it, and destroying it at the end of the package but I am having logistical problems training it outside of the data flow. I tried using the DM Query task but it requires that you output a result set and I am not sure if I can use it to create and process models.

I would think that they would just give you a DMX task similar to the SQL task but that does not seem to be the case. Also, when I browse the AS objects via the processing task I can only see the mining structures and not the mining models.

Am I just missing something here?

Cheers,

Dan Meyers

If you just want to process the mining models/structure, you should use the Analysis Services processing task. If you wnat more flexibility, you can use the Analysis Services Execute DDL task (you can do pretty much anything there). There is a feature called "out of line bindings" that you can specify if you want to proecess against different data than the model was bound to in the DSV - I'm not sure of the documentation level on this feature though.

You are right that you can't use the DM Query task for the reason you stated - you can use the DDL task as in this tip http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1443.aspx to process using DMX, or execute any other non-rowset returning DMX statement.

On the point of what happens during the data flow execution, the model/structure is only created at design time. The processing happens at run time. This means that if you wanted to run this package against another server, you would first have to create the model on that server. The easiest method may be to use SSMS to script the structure to create and then use the DDL task again

HTH

-Jamie

|||

Interesting...I did not realize that you could use the DDL task to execute DMX statments. Makes perfect sense since it all gets broken down to XMLA anyway. This is exactly the answer I was looking for. You are like the Yoda of SQL DM. Once again, thanks for the help.

-Dan Meyers

|||

Jamie,

I have another more question on this...Do I have to build a new DSV for my mining model or can I just use the same one that I am using for my OLAP cubes?

|||You can use the same DSV - the DSV is simply a collection of tables/named queries that don't necessarily have to be related

Processing cubes on a different server, using SSIS package run by a SQL Agent job from another s

Hi,

I am faced with this issue in a production environment. I have to implement an architecture where the Analysis Services database is in a server different from the server on which the SQL Serever database it accesses for data is.

I have changed the connection string for the data source in the Analysis Services database to point to the other server's SQL Server database. Log on to this server has been set to use SQL Server Authentication.

Then I created an SSIS package on the server containing SQL Server database, to process a dimension in the other server's Analysis Services database. The Analysis Services connection manager in this package was set to log on to the Analysis Services database on the other server using 'Specific User name and Password'. This user is a part of the administrator group on the server containing Analysis Services database. The package executed fine when executed from the Business Intelligence Studio.

The problem came when I tried to execute this package through a SQL agent job. The job is created on the server containing the SQL Server database. The step in the job meant to execute the package uses 'SQL Agent Service Account' for the 'Run as' option. The package was deployed as 'File System'.

I missed out posting the error message I am getting. It is as follows:

Code: 0x00000000 Description: A connection cannot be made. Ensure that the server is running.

Hope this helps to give a better understanding of the problem.

Thanks.

|||

Hi,

The problem was resolved by changing the LOG ON for the SSIS service to that of an user who is administrator on both the machines.

|||

Did you have to create a Credential and Proxy? I had to create one and set the Run As parameter for the step in Agent to the Proxy name. The job has to write a file to a network directory and from what I read it seems that the only way for an SSIS job to access anything outside SQL Server was by using a proxy. I am not sure if the same applied in your situation.

|||

Hmm.... I was helped by the fact that the OS was installed with a network administrator id. So no new credentials or proxy was required. The run as parameter for my job's step remained as "SQL Agent Service Account" and changed the Log On for SSIS service to use network administrator account. Hope this helps.

I would like to know how you created a proxy and made the Run As parameter to point to it.

Regards,

Emil

Processing cubes on a different server, using SSIS package run by a SQL Agent job from another s

Hi,

I am faced with this issue in a production environment. I have to implement an architecture where the Analysis Services database is in a server different from the server on which the SQL Serever database it accesses for data is.

I have changed the connection string for the data source in the Analysis Services database to point to the other server's SQL Server database. Log on to this server has been set to use SQL Server Authentication.

Then I created an SSIS package on the server containing SQL Server database, to process a dimension in the other server's Analysis Services database. The Analysis Services connection manager in this package was set to log on to the Analysis Services database on the other server using 'Specific User name and Password'. This user is a part of the administrator group on the server containing Analysis Services database. The package executed fine when executed from the Business Intelligence Studio.

The problem came when I tried to execute this package through a SQL agent job. The job is created on the server containing the SQL Server database. The step in the job meant to execute the package uses 'SQL Agent Service Account' for the 'Run as' option. The package was deployed as 'File System'.

I missed out posting the error message I am getting. It is as follows:

Code: 0x00000000 Description: A connection cannot be made. Ensure that the server is running.

Hope this helps to give a better understanding of the problem.

Thanks.

|||

Hi,

The problem was resolved by changing the LOG ON for the SSIS service to that of an user who is administrator on both the machines.

|||

Did you have to create a Credential and Proxy? I had to create one and set the Run As parameter for the step in Agent to the Proxy name. The job has to write a file to a network directory and from what I read it seems that the only way for an SSIS job to access anything outside SQL Server was by using a proxy. I am not sure if the same applied in your situation.

|||

Hmm.... I was helped by the fact that the OS was installed with a network administrator id. So no new credentials or proxy was required. The run as parameter for my job's step remained as "SQL Agent Service Account" and changed the Log On for SSIS service to use network administrator account. Hope this helps.

I would like to know how you created a proxy and made the Run As parameter to point to it.

Regards,

Emil

Processing cubes on a different server, using SSIS package run by a SQL Agent job from another s

Hi,

I am faced with this issue in a production environment. I have to implement an architecture where the Analysis Services database is in a server different from the server on which the SQL Serever database it accesses for data is.

I have changed the connection string for the data source in the Analysis Services database to point to the other server's SQL Server database. Log on to this server has been set to use SQL Server Authentication.

Then I created an SSIS package on the server containing SQL Server database, to process a dimension in the other server's Analysis Services database. The Analysis Services connection manager in this package was set to log on to the Analysis Services database on the other server using 'Specific User name and Password'. This user is a part of the administrator group on the server containing Analysis Services database. The package executed fine when executed from the Business Intelligence Studio.

The problem came when I tried to execute this package through a SQL agent job. The job is created on the server containing the SQL Server database. The step in the job meant to execute the package uses 'SQL Agent Service Account' for the 'Run as' option. The package was deployed as 'File System'.

I missed out posting the error message I am getting. It is as follows:

Code: 0x00000000 Description: A connection cannot be made. Ensure that the server is running.

Hope this helps to give a better understanding of the problem.

Thanks.

|||

Hi,

The problem was resolved by changing the LOG ON for the SSIS service to that of an user who is administrator on both the machines.

|||

Did you have to create a Credential and Proxy? I had to create one and set the Run As parameter for the step in Agent to the Proxy name. The job has to write a file to a network directory and from what I read it seems that the only way for an SSIS job to access anything outside SQL Server was by using a proxy. I am not sure if the same applied in your situation.

|||

Hmm.... I was helped by the fact that the OS was installed with a network administrator id. So no new credentials or proxy was required. The run as parameter for my job's step remained as "SQL Agent Service Account" and changed the Log On for SSIS service to use network administrator account. Hope this helps.

I would like to know how you created a proxy and made the Run As parameter to point to it.

Regards,

Emil

Processing cubes in Analysis Services 2000

I'm working with Analysis Services 2000 (SP4) on a 64 bit Windows Server 2003 where I have installed the framework 2.0.
I need to write an application to process Analysis Services 2000 cubes but I can't use DSO because there isn't a 64 bit version available of this library.
Can I use AMO (Analysis Management Objects) for administering Analysis Services 2000? Is there another solution?
Thanks in advance
Daniele

Unfortunatlely you cannot.
Your only option is to find a 32-bit workstation and run your DSO application from there. This is a restriction of AS2K. Sorry.

_-_-_ Dave

Processing cubes in Analysis Services 2000

I'm working with Analysis Services 2000 (SP4) on a 64 bit Windows Server 2003 where I have installed the framework 2.0.
I need to write an application to process Analysis Services 2000 cubes but I can't use DSO because there isn't a 64 bit version available of this library.
Can I use AMO (Analysis Management Objects) for administering Analysis Services 2000? Is there another solution?
Thanks in advance
DanieleUnfortunately you cannot use AMO against Analysis Services 2000.
You can use DSO to remotely process Analysis Services object from another 32bit machine.
By the way you can use separate Redistributable component to install DSO
http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
Look for
"Microsoft SQL Server 2005 Backward Compatibility Components "

Edward Melomed (MSFT)

---

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

Processing Cube

Hi,

Everyday I have a schedule job to processing the cubes, but I always receive the same error and then the Analysis Service will be stop. Here is the error msg from the Event Viewer:

Event Type: Error
Event Source: MSSQLServerOLAPService
Event Category: (256)
Event ID: 22
Date: 9/18/2007
Time: 5:03:38 AM
User: N/A
Computer: HODB04
Description:
The description for Event ID ( 22 ) in Source ( MSSQLServerOLAPService ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: File system error: The following error occurred during a file operation: The process cannot access the file because it is being used by another process. . (\\?\z:\OLAP\data\DM_POS_V2.0.db\POS DM.558.cub\Fact Sales.591.det\FACT SALES F2008 P2.7.prt\55.agg.flex.data)..

Thanks,

I would reccomend you to investigate the storage subsystem (RAID).

I have almost the same problem - the source of it was in the configurationof the RAID controller.

|||

What modifications did you made to the raid config to make it run? Whe are just running two mirrored disks on a HP so there's not much to modify.

Process Cube using Integration Services

Hi,

I want to process my cube using Process Data and Process Index instead of the Process Full. However, after configuring the 2 Analysis Services Processing Tasks (one for process data and the other for process index) and were executed sequentially (process data first then process index), I got this error:

Errors in the metadata manager. The process type specified for the CASES cube is not valid since it is not processed

Have I done the right thing?

The reason why I prefer using the Process Data and then Process Index, it's because it is much faster than the latter.

cherriesh

It's interesting that you're seeing an error, because 'Process Full' is nothing more than 'Process Data' and then 'Process Index' under the covers.

I'll admit that I was skeptical that executing the two sequentially was faster for you than process full, but I found an Analysis Services best practices whitepaper that confirmed this could happen.

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/ssaspbpr.mspx

Are you sure you are using 'Process Data' on the cube object itself and not a measure group or partition?

Also, you may want to post this question in the Analysis Services forum.

|||

Hi,

I did the process data for both the dimensions and cube. and did also process index for the two.

am i right there?

cherriesh

|||

I think that might be your issue. If it processes a dimension after the cube that would explain the error message you received on the step to process the index. Try doing process data for just the cube (it should do the dimensions as part of this).

process cube

I am using analysis server 2000. I have a cube on one of the databases which i am trying to process through the Business Intelligence Development studio.

i have specified the Server and the database name in the 'connection managers' through 'New Analysis Services Connection'

I added the 'Analysis Services Processing Task' to the Control flow but get the following error when i double click on it:

"A connection cannot be made. Ensure that the server is running'

Does anyone know what am i doing wrong?

Any help is appreciated.

Unfortunately you will not be able to use AS 2005 tools against AS2000.

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

|||

Edward,

Are you from Microsoft's SQL Server Team.

Just wanted to know if the answer was official :)

thanks

Process Cube

Hi,

I want to process my cube using Process Data and Process Index instead of the Process Full. However, after configuring the 2 Analysis Services Processing Tasks (one for process data and the other for process index) and were executed sequentially (process data first then process index), I got this error:

Errors in the metadata manager. The process type specified for the CASES cube is not valid since it is not processed

Have I done the right thing?

The reason why I prefer using the Process Data and then Process Index, it's because it is much faster than the latter.

I'm not sure though what Process Index is for. As far as I'm concerned, I have done partitions (I used performance gains reaches ) but I don't remember to explicitly declare indexes.

Please help...

cherriesh

Hi,

In theory what you did to me seems as if it should work. Have you tried doing it outside SSIS? So in managment studio, process data and then index, might be a problem with the cube not actually processing the data step properly.

but here is a description of what the different process types do

http://technet.microsoft.com/en-us/library/ms174774.aspx

Hope that helps

Matt

|||

Hi,

The ProcessFull is working fine. I dunno how to do this outside SSIS. Am I right to have Process Data for both Dimension and Cube and to have another Analysis Services Process Task for Process Index for both (Dimension and cube)

cherriesh

|||

Hi,

I found a better link:

http://msdn2.microsoft.com/en-us/library/ms345142.aspx

For the dimensions:

A process full will reprocess the dimension completely

Never done a process index on a dimension, but i would assume process data then process index.

Once the dimensions are processed then process the cube

Process Full

Process data and process index (two seperate tasks I am guessing)

You could try

(task 1) Is to process the structure of the cube (processes all the dimensions)

(task 2) Process full of cube

OR

task 1, process data of dims

task 2, process indexes

task 3, process data of cube

task 4, process indexes of cube

OR

task 1, process structure of cube

task 2, process data of cube

task 3, process indexes of cube

Sure there are lots of different ways.

With reference to aleternative ways of processing:

http://technet.microsoft.com/en-us/library/aa337509.aspx

Hope that helps,

Matt

Process AS2000 Cubes in SSIS

Hi,

I would like to process an AS 2000 cube in SSIS.

I'm using the 'Analysis Services Processing Task', but I just get the error 'A connection cannot be made. Ensure the server is running'.

It would be an unfortunate limitation if you can only process 2005 cubes in SISS. Does anyone know if its possible to process 2000 cubes in SSIS?

Many thanks

Jeremy

'Analysis Services Processing Task' works against AS 2005 only. There is no built-in task to process AS 2000 objects.

You can try the Script Task and write code that will send the process request to a AS 2000 server

Regards,
Ovidiu Burlacu

|||

Its quite a clunky, fiddly way to go about things, but if its the only way to do it then so be it

Many thanks

Jeremy

|||I am trying to do the same thing! - we have a 3rd part dw app that uses sql 2000 olap, and will not support an upgrade to 2005 olap. Did you find anything new?

process analysis server objects from web via ASP.NET + C#

Hi!

We are running several Analysis Services Cubes on a Database Server (SQL 2005) machine.

Another machine is our web server (IIS, ASPX) which displays cube

content and allows drill down to several levels. Everything works fine

and we are looking forward to optimizing the whole data management

process. At the moment we try to develop DTS packeges (SQL 2005).

Is it possible to initiate a cube processing (without DTS in the background) from eg an administration section in the web page?

TIA and kind regards from Vienna

JohnnyR

You can use AMO (Microsoft.AnalysisServices.dll from "%ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies"). If IIS impersonates the current user, then AMO code from the administration aspx page would also connect to AS2005 as that user. The user needs read permission for the Database and read+process permissions for the Cube.

Sample code:

using Microsoft.AnalysisServices;

...

Server server = new Server();
server.Connect("localhost");
try
{
server.Databases["the ID of the database"].Cubes["the ID of the cube"].Process();
}
finally
{
server.Disconnect();
}

You can also get processing notifications with AMO, to display progress on the administration page; more details here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=75148&SiteID=1

Adrian Dumitrascu

|||

Hi,

I have created an analysis services project using SQL Server 2005, in which I have included a cube, dimension, data source and data view. Now using the Analysis services Browser tab of cube I can see the Pivot table exactly the way I want where I can drag & drop table fields as per my requirements. But I am struggling to display the same cube over the Web. Can any please tell me that how I can publish the contents of Cube i.e. Pivot table on a web application so that end user can use this. I want the same functionality, which I can see in the Browser tab of Cube in AS2005 i.e. user should have freedom to drag & drop the fields exactly like in Excel Pivot table.

Any help would be highly appreciated.

Thanks in advance.

|||

The control used in the browser is the Office Web Components 11.0 PivotTable control.

You can use this control in your asp.net app by referencing this control in the COM tab.

As to how to use it.. documentation is sparse, so I'd just do a search on the web.

Alternatively, you could host thepivot table in an Excel spread sheet and publish that as a web page... use Excel 2007 for the best experience... again, do a search on Excel services.

Otherwise, there are true .NEt controls, such as those produced by RadarSoft

process analysis server objects from web via ASP.NET + C#

Hi!

We are running several Analysis Services Cubes on a Database Server (SQL 2005) machine.

Another machine is our web server (IIS, ASPX) which displays cube

content and allows drill down to several levels. Everything works fine

and we are looking forward to optimizing the whole data management

process. At the moment we try to develop DTS packeges (SQL 2005).

Is it possible to initiate a cube processing (without DTS in the background) from eg an administration section in the web page?

TIA and kind regards from Vienna

JohnnyR

You can use AMO (Microsoft.AnalysisServices.dll from "%ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies"). If IIS impersonates the current user, then AMO code from the administration aspx page would also connect to AS2005 as that user. The user needs read permission for the Database and read+process permissions for the Cube.

Sample code:

using Microsoft.AnalysisServices;

...

Server server = new Server();
server.Connect("localhost");
try
{
server.Databases["the ID of the database"].Cubes["the ID of the cube"].Process();
}
finally
{
server.Disconnect();
}

You can also get processing notifications with AMO, to display progress on the administration page; more details here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=75148&SiteID=1

Adrian Dumitrascu

|||

Hi,

I have created an analysis services project using SQL Server 2005, in which I have included a cube, dimension, data source and data view. Now using the Analysis services Browser tab of cube I can see the Pivot table exactly the way I want where I can drag & drop table fields as per my requirements. But I am struggling to display the same cube over the Web. Can any please tell me that how I can publish the contents of Cube i.e. Pivot table on a web application so that end user can use this. I want the same functionality, which I can see in the Browser tab of Cube in AS2005 i.e. user should have freedom to drag & drop the fields exactly like in Excel Pivot table.

Any help would be highly appreciated.

Thanks in advance.

|||

The control used in the browser is the Office Web Components 11.0 PivotTable control.

You can use this control in your asp.net app by referencing this control in the COM tab.

As to how to use it.. documentation is sparse, so I'd just do a search on the web.

Alternatively, you could host thepivot table in an Excel spread sheet and publish that as a web page... use Excel 2007 for the best experience... again, do a search on Excel services.

Otherwise, there are true .NEt controls, such as those produced by RadarSoft

process add and configuration

SSIS Destination Adapter ProcessADD does not correctly use the Analysis Services Connection with and dynamic connection string.

I use process add for cube Processing - we use Production and Development environment. The analysis Services Connection is parameterized with a differnt Database name.

The Process add - does not like all other analysis services processing tasks, allow you to use an expression for the processing command - it only gives you the UI with design time tools.

But I guess like all other SSIS Components with SSAS the Database Name is hardcoded in the prcessing command. For the Processing Task I could change this with an expression.

But Howto change the Processing Command withing the Process ADD Destination -

or how do I get it working while the database name changes (at the moment if the database name changes by configuration all mappings are lost and execution fails)

Best Regards

HANNES

I am doing something wrong? or is a configuration for data datasource of the olap destination is ssis not supported?

Best regards, HANNES

|||

I got it

The Database is always part of the XMLA command and therefore not affected by the connection manager initial catalog property.

You always have to code the database name in the XML Command

HANNES

process add and configuration

SSIS Destination Adapter ProcessADD does not correctly use the Analysis Services Connection with and dynamic connection string.

I use process add for cube Processing - we use Production and Development environment. The analysis Services Connection is parameterized with a differnt Database name.

The Process add - does not like all other analysis services processing tasks, allow you to use an expression for the processing command - it only gives you the UI with design time tools.

But I guess like all other SSIS Components with SSAS the Database Name is hardcoded in the prcessing command. For the Processing Task I could change this with an expression.

But Howto change the Processing Command withing the Process ADD Destination -

or how do I get it working while the database name changes (at the moment if the database name changes by configuration all mappings are lost and execution fails)

Best Regards

HANNES

I am doing something wrong? or is a configuration for data datasource of the olap destination is ssis not supported?

Best regards, HANNES

|||

I got it

The Database is always part of the XMLA command and therefore not affected by the connection manager initial catalog property.

You always have to code the database name in the XML Command

HANNES