B

When I'm viewing a measure spliced by all the members of a particular dimension, I get an error in regards to the member name's characters.

Here's how the error appears in the different clients -

SQL BI Studio:

TITLE: Microsoft Visual Studio

A list of actions for the selected items cannot be obtained because of the following error.
XML parsing failed at line 12, column 62: Illegal xml character.
.
This error may have occurred because the definition for at least one action is not valid. Verify the definition for each action using the Actions view.

ADDITIONAL INFORMATION:

XML parsing failed at line 12, column 62: Illegal xml character.
. (Microsoft SQL Server 2005 Analysis Services)

Reporting Service's VS query builder complains:
Error:
' ', hexadecimal value 0x08, is an invalid character. Line 1, position 94333. (System.Xml)

In SQL Management Studio:
When expanding the dimension's children, a message yields "Error occured

retrieving child nodes, The server sent an unrecognizable response"

3 free bugs for any SDETs out there :-)
Are there any known work arounds? What's the best way to filter out these characters (and which characters to we need to filter?)

Probably your dimension table contains invalid XML character as part of member name or key. You can go to the attribute which causes this problem and change the bindings on the NameColumn or KeyColumn (depending which one caused the problem) for InvalidXmlCharacters from the default Preserve to Remove or Replace. This will slow down processing of the attribute a little bit, but it should solve the problem.

HTH,

Mosha (http://www.mosha.com/msolap)

Avoid Cross Database Views in Data Warehouse

We have a relational database (rd) and a data warehouse (dw). This dw has a table (tw) with all key fields (dimension keys) and metric related (measures) fields. This table is populated with monthly data each month. The tw is joined to various look up views present in the dw to obtain name fields from rd. The DBA wants me to remove the look up views. I now have following 2 options that I can think of –

1) Further de-normalize the tw and store the name fields as well. However, there are two issues with this option –

a. The size of tw will grow tremendously.
b. We are storing monthly data and the values in the name fields may change after some time. Then we will have to put in additional views/objects to obtain the latest name.

2) Using ETL, obtain the copy of rd tables overnight in dw. We will then join tw with these tables and there will no longer be cross database joins. However, this will be a burden on maintenance and support.

As of now these are the possible options I can come up with. Which one would you suggest and why? If you have another option, please let me know.

Thank you all in advance,

sajmeraWe have a relational database (rd) and a data warehouse (dw).
No you don't. You have a datamart. If you had a real data warehouse schema you would not have these issues.

The DBA wants me to remove the look up views.
Why?

2) Using ETL, obtain the copy of rd tables overnight in dw. We will then join tw with these tables and there will no longer be cross database joins. However, this will be a burden on maintenance and support.
A data warehouse (or datamart) should be self-contained, and not rely upon pulling dynamic data from other systems. Maintenance and support are necessary requirements of a data stores.

AverageByChildren Function on a measure from a Fact and Dimension Table

Hi
I have a FactVendorPerformance table where in I have the CustomerID and the VendorID i.e. the Vendor who has serviced the Customer along with other details relating to the service quality of the vendor. I have also created KPIs in my cube for vendor service quality. I wanted a hierarchy - Customer-Vendor to filter the service quality KPIs since one customer could have been serviced my multiple vendors at different instances.
When I had the FactVendorPerformance table as only a FactTable, I was unable to create a Customer-Vendor hierarchy in it since it was not a dimension. So I made the FactVendorPerformance both a Dimension as well as a Fact table. However the measures have Sum as the default aggregate funtion. I changed this to AverageByChildren in the measure of FactVendorPerformance table. But this is not gettting reflected. Is there anything wrong in what I am doing? Is there any better way to implement the same?I want the Customer - Vendor hierarchy to filter the KPIs.

Thanks and Regards
Guruprasad Karnik

One customer could be serviced by multiple vendors - but, conversely, couldn't one vendor also service multiple customers? From a modelling perspective, customer and vendor would typically be separate dimensions - though they could be "stacked" for the purposes of reporting.

As far as the aggregate function, it's not clear how many fact records could exist for each combination of customer and vendor; but if you need to average a field value across all applicable fact records, you might try a calculated average wher you divide a "sum" aggregation on that value by a "count" measure for the same fact table/measure group.

|||Hi Deepak
Thanks for the reply:)! I did realise this Sum/Count workaround yesterday after going through some forums out here since AverageByChildren seems to average on only time and implemented the same. In my cube I do have Customer and Vendor as different dimensions. However the join or a relationship between the 2 exists only in the FactVendorPerformance table and all vendors need not have serviced all customers and conversly all customers need not have been serviced by all the vendors. It would be more meaningful to have a hierarchy in either ways (Customer-Vendor or Vendor-Customer) to filter the KPIs rather than having 2 different filters configured to 2 different dimensions. As you mentioned, conversely it can be a Vendor-Customer hierarchy as well. But the question is that since the relationship is existing only in the FactVendorPerformance table I had to make it a dimension table to get the hierarchy. I wanted to know as whether I am right in my approach. Is there any better way of designing or modeling the same?

Thanks and Regards
Guruprasad Karnik|||Not sure why you need to present 2 dimensions in a single hierarchy to users - what is the OLAP client tool? For example, with Reporting Services, if you wish to limit the selection list for vendors to those servicing a specific customer, you could use cascading parameters, where user selection of customer dynamically filters the selection list of vendors. In other words, can't you handle this navigation in the client, rather than in the cube?|||I am using MOSS 2007 KPI Lists to display the KPIs with the SSAS filter web part configured to the hierarchy and connected to the KPI list. If it was reporting services, then yes I could have had cascading parameters. But in MOSS 2007 I am not sure whether you can achieve this by interconnecting the filter web parts to have cascading parameters

AverageByChildren Function on a measure from a Fact and Dimension Table

Hi
I have a FactVendorPerformance table where in I have the CustomerID and the VendorID i.e. the Vendor who has serviced the Customer along with other details relating to the service quality of the vendor. I have also created KPIs in my cube for vendor service quality. I wanted a hierarchy - Customer-Vendor to filter the service quality KPIs since one customer could have been serviced my multiple vendors at different instances.
When I had the FactVendorPerformance table as only a FactTable, I was unable to create a Customer-Vendor hierarchy in it since it was not a dimension. So I made the FactVendorPerformance both a Dimension as well as a Fact table. However the measures have Sum as the default aggregate funtion. I changed this to AverageByChildren in the measure of FactVendorPerformance table. But this is not gettting reflected. Is there anything wrong in what I am doing? Is there any better way to implement the same?I want the Customer - Vendor hierarchy to filter the KPIs.

Thanks and Regards
Guruprasad Karnik

One customer could be serviced by multiple vendors - but, conversely, couldn't one vendor also service multiple customers? From a modelling perspective, customer and vendor would typically be separate dimensions - though they could be "stacked" for the purposes of reporting.

As far as the aggregate function, it's not clear how many fact records could exist for each combination of customer and vendor; but if you need to average a field value across all applicable fact records, you might try a calculated average wher you divide a "sum" aggregation on that value by a "count" measure for the same fact table/measure group.

|||Hi Deepak
Thanks for the reply:)! I did realise this Sum/Count workaround yesterday after going through some forums out here since AverageByChildren seems to average on only time and implemented the same. In my cube I do have Customer and Vendor as different dimensions. However the join or a relationship between the 2 exists only in the FactVendorPerformance table and all vendors need not have serviced all customers and conversly all customers need not have been serviced by all the vendors. It would be more meaningful to have a hierarchy in either ways (Customer-Vendor or Vendor-Customer) to filter the KPIs rather than having 2 different filters configured to 2 different dimensions. As you mentioned, conversely it can be a Vendor-Customer hierarchy as well. But the question is that since the relationship is existing only in the FactVendorPerformance table I had to make it a dimension table to get the hierarchy. I wanted to know as whether I am right in my approach. Is there any better way of designing or modeling the same?

Thanks and Regards
Guruprasad Karnik
|||Not sure why you need to present 2 dimensions in a single hierarchy to users - what is the OLAP client tool? For example, with Reporting Services, if you wish to limit the selection list for vendors to those servicing a specific customer, you could use cascading parameters, where user selection of customer dynamically filters the selection list of vendors. In other words, can't you handle this navigation in the client, rather than in the cube?|||I am using MOSS 2007 KPI Lists to display the KPIs with the SSAS filter web part configured to the hierarchy and connected to the KPI list. If it was reporting services, then yes I could have had cascading parameters. But in MOSS 2007 I am not sure whether you can achieve this by interconnecting the filter web parts to have cascading parameters

Autoslicing not working

I have around 36 partitions in the 2005 cube. Each partition is roughly 20M fact rows.

The partitions are differentiated by a date member dimension.

When I run a query which selects by a distinct date, SSAS tries to scan all partitions (for inclusions?). This introduces a huge performance impact. It takes many seconds to run a single query.

I have aggregations designed specifically for relevant dimensions. The Profiler shows that SSAS reads from aggregations. It does not read from facts.

The storage mode is MOLAP.

Do I need to use the partition slice property ? I am seeing on forums from time to time that this attribute is not used in MOLAP.

I just need to make sure that my queries are fast.

I don't see where it could hurt to set this property, though BOL indicates this is only used by SSAS for ROLAP partitions. I thought I had heard that with MOLOP and/or HOLAP, that SSAS maintained an internal set of information regarding the contents of a slice that would handle this function for you. Maybe someone else out there could chime in on this one.

Regarding the query you specify above, are you specifying a date member from the specific cube dimension used to partition your cube? You probably are but it wasn't explicitly stated. Date dimensions are often used in a role-playing capacity which introduces some confusion.

Thanks,
Bryan

|||

can anyone clarify if the Slice property on Partition is used in MOLAP or not?

I checked info....xml files for my slicing dimension min/max values and they seem correct, however with explicit queries, SSAS still reads a bunch of partitions...

Autorefresh in Microsoft Excel 2003 Cubeanalysis

Hi,

I play around with the Cubeanalysis Addin for Excel 2003. My Question: Every time, I drag a dimension, measure, etc. to an excelsheet, all data will be refreshed. This refreshment lasts often up to 1-2 minutes. I didn't need an refreshment at design-time. Is it possible to deactivate it for report design, and activate it when the report is ready for use?

Thanks
Hans

Sorry Hans. This is a wrong forum for this question.

Try posting on the Office forum.

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

|||

Hello Edward,

Thanks for your Information, but I didn't find any Office Forum here at MSDN Forums (?). And Cubanalysis is one of the Analysis Services 2005 Frontends, so I thought, thats the right forum.

Hans

|||

Try looking at the Office developers newsgroups like:

microsoft.public.officedev
microsoft.public.office.developer.vba

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