Applied Microsoft Analysis Services 2005

OLAPQueryLog - Unable to run Aggregation usage wizard

rambal — Thu, 02 Sep 2010 18:39:02 GMT

Hi Teo, I took your SSAS online class last month, from SAIF corporation

I have a wealth of data in this table OLAPQueryLog from the SSAS queries in production.

I tried to create a new project in BIDS with datasources pointing to TEST server, but the SSAS service pointing to the production OLAP query table.

However when I run the Aggregation usage wizard in BIDS , I get

"There are no queries in the log for selected measure group."

Question - how can I run Aggregation usage wizard against production OLAPquerylog data while in BIDS connected to a test/dev server?

I am nervous about connecting directly to the production SSAS database and running the OLAP design usgae wizard

Ramesh Bala

Suppressing Small values

Acius — Thu, 26 Aug 2010 06:14:40 GMT

I'm trying to suppress small values for a measure.

Using a scope and format string appears to work

Scope ([Measures].[Actual]);

format_string(This)=IIf( [Measures].[Actual]<5,"LT 5","#,#");

END SCOPE;

but the users can get at the real values from the client by hovering and waiting for the tool tip.

If I replace the value

Scope ([Measures].[Actual]);

This=IIf( [Measures].[Actual]<5,NULL,[Measures].[Actual]);

END SCOPE;

The measure is aggregating up the hierarchies so the values at higher levels are incorrect.

One of the hierarchies is parent-child so the level the measure hits the limit can vary.

Any ideas?

Thanks

SCD 2 and Attribute relations.

vovasurkov — Sun, 08 Aug 2010 18:28:52 GMT

Hi Teo.
I have some problems with SCD 2 and Attribute relations.

We have
ScdProductId   AlternateProductId   SubCategory   Category
1           50       bread        food
2           100       milk       food
And we have attribute relationship:
ScdProductId -> AlternateProductId -> SubCategory -> Category

AlternateProductId many to one SubCategory/

And we have one new row ScdProductId = 3

ScdProductId   AlternateProductId   SubCategory   Category
1           50       bread        food
2           100       milk       food
3           100       drink       food

AlternateProductId is the same = 100 but SubCategory has changed.

After changing we get Error
“A duplicate attribute key has been found when processing: Column: 'AlternateProductId , Value: 100”.

AlternateProductId have two different parents milk and drink it is wrong
because of attribute relationship AlternateProductId -> SubCategory. (many to one ).

I see than in Product Dimension in the Adventure Works we haven’t AlternateProductId at all.
And we don’t get such Errors. But we also haven’t possibility to group sales by AlternateProductId(this is requirement of our project)

What is the best way of modeling (Especially attribute relationship and hierarchy)
in this case ?.

Issues implementing many-to-many currency conversion

Sandy — Fri, 23 Jul 2010 10:03:00 GMT

Hello,

I am relatively new to Analysis Services and am having some issues implementing many-to-many currency conversion in SSAS 2005.

I have created a "proof of concept" solution (with core components based upon the solution in Teo's book "Applied Microsoft Analysis Services 2005") which seems to confirm that the proposed architecture delivers the functionality we require.

However I have noticed that when the same components are integrated into our development solution (where there is a larger number of dimensions, currencies and rates, etc) there are certain scenarios where building queries through the cube browser causes the Analysis Services process to consume a large amount of memory and hang the machine until such a point that the process is restarted. The same queries seem to function as expected whenever the currency conversion MDX is commented out from the cube.

I have a couple of questions that I was looking for some advice on:

1) The issues I have encountered is making me question whether the goal I am trying to achieve is actually functionally correct.

To support currency conversion I have created and populated three tables:
   DimCalendar - holding date records on a day granuraity
   DimCurrency - holding currencies supported in our application
   FactCurrencyRate - holding conversion rates per currency per calendar date.

Currently we have multiple dimensions which have financial values associated with them (stored in the relevant fact tables).
Each fact table contains a CurrencyKey and numerous date keys, one of which is the 'date created'.

I am attempting to convert the financial values for each fact to a currency specified by the user based upon the rate corresponding to the 'date created'.
Does this sound logical/correct?

I looked at the Adventure Works solution and it has a similar architecture where each Sales fact table has an OrderDateKey.

2) The issue seems to be caused whenever I try and report on any date from the fact table other than the 'date created'.

In looking at a possible cause I stripped out a large number of the unused entries in the FactCurrencyRate table and this did seem to cause the issue to be rectified. However I believe that this may just be indicating that the MDX is incorrect or I haven't configured the Dimension Usage correctly.
Have you any advice on how I can best set about tracking down the possible cause?

Many thanks,

Sandy

OWC dynamic pivot charts

mial14 — Thu, 22 Apr 2010 15:41:52 GMT

Hi Teo

I have the following issue which is in a way related to my previous post: A similar situation that you solved on Ch19 solution, Interative Reporter application , page 645 in your book.

I have just a windows form app. with four tab pages : 3 different pivot tables on the first three tab pages and now I have to dynamically load a chart report based on my last pivot tabel (pivottabel3).

1) Did you set a chart at design time on your tabchart ? If yes, how ? My attempt to load from the COM tab fails .

2) I use your LoadChart() . But When I run the first time it gets an exception on line:

ChartSpace.DisplayFieldList =

True and I really do not understand why.

3) If I close the debugger and run again and again it , I did not get any exception more, the code runs, but there is no chart displayed on my tab page. Can you please help me find out why the chart is not displayed.

Thank you again a lot for your answers.

Best regards,

Mihai

An untraditional way of datamart modelling

mial14 — Fri, 11 Jun 2010 19:34:44 GMT

Hi Teo

I was presented with an 'untraditional' approach of building a dimensional data model for a cube.

That is why I try to briefly present it to you and like to 'hear' whether you have or had knowledge about it and all in all how you find it. I will exemplify with just a fact table Fact1 and a Date dimension but imagine the 'extension' applying for all your dimensions and fact tables.

1) Let's say you have a Date table with just one column [datetime] type [datetime] ( clustered index on it ) and a fact table Fact1 with two columns a DateKey and Measure1.

2) You create a Keys table with a statement like : CREATE TABLE [dbo].[Keys]( [sk_key] [int] IDENTITY(1,1) NOT NULL, [Dimension] [nvarchar] (50) NOT NULL, [Attribute] [nvarchar] (50) NOT NULL, [AttributeValue] [nvarchar] (250) NULL, [ValidFrom] [int] NULL, [ValidTo] [int] NULL ) ON [PRIMARY]

You create a clustered index covering all columns and an nonclusted index on sk_key. The Keys table will contain all the attributes and their values for all dimensions and a track of their changes in order to support evt. SCD type2 dimensions. You find a way - by writing a stored procedure - to populate the keys table for all your dimensions and adapt your ETL flows to 'target' this table.

3) Your dimensions will be based on views which basically join the 'background' table with the keys table or are entirely based on the 'background' table. For the Date dimension the view may look like : CREATE VIEW [dbo].[DateView] AS SELECT cast (convert(nvarchar, [datetime], 112) as int) as DateKey, convert (nvarchar, [datetime], 112) as DateName, case when DATEPART(D, [datetime]) < 10 then '0' + DATENAME(D, [datetime]) else DATENAME(D, [datetime]) end as DateShortName, etc. , etc, etc...... from dbo.Date

but for an Organisation dimension the statement may look like : CREATE view [dbo].[EmployeeView] as select k1.sk_key as EmployeeKey, d.EmployeeName, d.EmployeeBnr, d.EmployeeTitle, k2.sk_key as EmployeeGroupKey, EmployeeGroup from Employee d inner join Keys k1 on d.EmployeeBnr=rtrim(k1.AttributeValue) inner join Keys k2 on d.EmployeeGroup=rtrim(k2.AttributeValue) where k1.Dimension='Employee' and k1.Attribute = 'EmployeeBnr' and k1.Dimension = 'Employee' and k2.Attribute = 'EmployeeGroup'

4) You are almost done. You add to the Data source view all the views you created - one for each 'dimension' table - DataView in our example- and the fact tables - Fact1 in our example - as well.

Have you seen this approach before ? If yes, please link me to the source of information. Can you see pros and cons ? Will you considere using it ?

Thank you a lot for your feedback .

Best regards,

Mihai

Time Dimension

sash56 — Wed, 19 May 2010 10:08:26 GMT

Hi Teo, I am trying to work on 2008 AS using your AS 2005 book. ( Since you have ignored a million requests from us to write AS 2008 book :) ) So far its good but i have been stuck now at one point and need your help.

I am trying to create the SOS cube ( chapter 3) and i am unable to find the " selecting Time Dimensions" and " time Table Mappings" in the wizard when i am trying to create the cube. Therefore i am unable to configure the Date Dimension as suggested by you in the book.

Is this something different in AS 2008?

Can you please help and explain how best i can go about with this issue?

Thanks

Applied Analysis Services 2008 Online Class on May 17th

Teo Lachev — Thu, 06 May 2010 12:58:54 GMT

There is still time to register! Join Teo Lachev as he presents his online Applied Analysis Services 2008 course on May 17th. No travel, no hotel expenses, just 100% content delivered right to your desktop! This intensive 3-day online class (14 training hours) teaches you the knowledge and skills to master Analysis Services to its fullest. Use the opportunity to ask questions and learn best practices.

For more information or to register click here!

Retrieve field values from owc pivot table

mial14 — Wed, 21 Apr 2010 14:39:23 GMT

Hi Teo

I'm developing an an application that uses owc pivot table on a windows form and that reminds of Writeback demo application you wrote.( The pivot table has a cube as a data source. The cube has some actions, which I try to retrieve and execute some update cube statements.

1) My problem is, that I do not know how I can retrieve the values for the members used for filtering : Imagine that I have an attribute hierarchy [Dim Date].[Day Name Of Week] in the filter pane whose value is checked to Friday. How can I retrieve the unique member name for that member, that is:[Dim Date].[Day Name Of Week] &[Friday]. I tried different solutions and I found out that by using : ( cell is declared as PivotCell) cell.Data.FilterAxis.FieldSets.Item(0).Member.UniqueName I got [Dim Date].[Day Name Of Week][All] and the same by using DefaultMember in stead of Member.

2) If I have more than one field on rows or columns how can I retrieve them ? I can only retrieve one by using cell.RowMember.UniqueName like you did.

Thank you a lot for your answer.

Best regards, Mihai

date picker param in SSRS report off a cube

furmangg — Mon, 17 Apr 2006 20:45:45 GMT

Teo-

Have you had any luck creating a report param which uses the date picker and feeds info to an MDX query? Here's as far as I got...

1. Using the MDX query designer for your main query, define a date parameter.

2. Edit the report parameter it created. Make it DateTime type. Don't query the database. Uncheck multi-value. Default it to null.

3. Delete the extra dataset it created which would have driven the date parameter.

4. Edit the query parameter and write an expression which converts the DateTime from the parameter into an MDX member name... such as:
="[Date].[Date].&[" & Year(Parameters!DateDate.Value) & Right("0" & Month(Parameters!DateDate.Value),2) & Right("0" & Day(Parameters!DateDate.Value),2) & "]"

All that works fine. However, now go add a field to your main dataset and report designer trashes all the work you've just done. (Don't you hate it when tools think they're smarter than you!)

Is there any way you know of to tell SSRS not to mess with a particular query/report parameter again? Or is there another way you could think of to use the date picker with report on a cube?

Thanks!

Scope All except ROOT

Acius — Tue, 30 Mar 2010 07:14:44 GMT

Is there an efficient way to scope all members of a dimension (for all hierarchies) ?

i.e. the dimension has a lot of attributes and hierarchies and is likely to change so would like to avoid listing all attributes and hierarchies

something like

Scope ([Dim1]-ROOT([Dim1]));

Scope [Measures].[Hideme];

Format_string(this)=" ";

End Scope;

SSAS 2008R2 vs PowerPivot - business capabilities / value proposition

harshb — Mon, 22 Mar 2010 21:18:35 GMT

I have a question regarding your blog post on SSAS wish list.

http://prologika.com/CS/blogs/blog/archive/2009/12/07/analysis-services-top-10-wish-list.aspx

You mention here that "the business problems that I solve with UDM exceed by far Gemini's capabilities."

Would it possible for you to give examples of such business problems?

The few main things I can point out are: security, manageability, hierarchy creation and scalability.

Any suggestion on the business problem solving capability differences you have would be very useful!! Any calculation capability differences that you know of? What can SSAS do that Excel-PowerPivot can't?

MDX: Is there something like an "IN" operator I can use when filtering by member properties?

wgpubs — Tue, 16 Mar 2010 23:25:24 GMT

Hi Teo,

What I'm trying to accomplish looks something like this (though "this" aint working). Hopefully the idea is clear though:

with set [Filtered Groups] as
filter ([Group].[Group Hierarchy].allmembers ,
[Group].[Group Hierarchy].Properties( "Group Full Name" ) = STRTOSET(@CustomSet))

... where @CustomSet is a string that looks like:
'{[Group].[Group Full Name].&[1 - Group A], '[Group].[Group Full Name].&[2 - Group B] ... }'

Is this possible? If so ... what exactly is the syntax?

Thanks -wg

Can't figure out why this query is so slow

wgpubs — Tue, 09 Mar 2010 23:18:15 GMT

This is probably easier to explain in pictures so ...

1. I have a QuestionAnswer dimension (hooks into a fact table for analyzing survey data) that looks like this:

2. But when I try to use the "Ans Value" member from the Question Answer dimension as pictured in the report below it takes ~ 60 seconds for the report to finish (whereas if I don't include it it runs in < 1 second). My question is ... WHY????

3. As an alternate solution I tried throwing in a calculated member defined as such:

This *almost* works except that I now get a totally unrelated row that shouldn't be there given my constraints when I include this calculated member. If I don't include it ... then I get the expected rows. See ...

Any help would be appreciated. This is probably something stupid that I'm overlooking ... but after a few hours I'm just not getting it.

Thanks -wg

New features in SQL 2008 R2 BIDS

Abdul Hai Al-Atassi — Sat, 13 Mar 2010 09:28:48 GMT

Dear Sir,

I installed SQL Server 2008 R2 to have some exercises on SSAS 2008. I found that it seems that there are some changes between what I have in my book examples (SSAS 2008) and what in DIBS 2008 R2, especially in Aggregations and Partitions tabs in Cube Design. Is that right?

Please advice, and if there is some materials about 'What's New' in BIDS 2008 R2 in details.

Best Regards,

Use a DisplayOrder attribute for multiple Attributes in a Dimension?

wgpubs — Thu, 11 Mar 2010 23:06:41 GMT

This was working in ssas 2005 ... but not in ssas 2008 where i changed my error config so that i no longer ignore duplicate key exceptions.

My Dimension looks like this:

QuestionAnswer (dim key)
AnsLabel
AnsValue
DisplayOrder

AnsLabel, AnsValue and DisplayOrder are related directly to the dimension key. However, I'd like to use the DisplayOrder to order not just the QuestionAnswer attribute ... but also AnsLabel and AnsValue.

Unfortunately, whenever I adjust the relationships so that DisplayOrder is related to QuestionAnswer, AnsLabel and AnsValue (so that I can use it for ordering) ... I now get duplicate key exceptions.

Any ideas on how to resolve?

Thanks -wg

How to create "composite" KPI?

light_wt — Mon, 08 Mar 2010 15:09:52 GMT

A company has several stores. so, the requirement is to show the KPI based on

1) KPI of income of the company as a whole

2) KPI of income the store as a percentage of the company. And each store KPI has its own percentage to use red/yellow/green.

Greatly appreciate for samples.

Thanks.

Migrated SSAS database to a VMWare virtual machine and queries are slloooooowwww

wgpubs — Fri, 05 Mar 2010 02:45:00 GMT

Hi Teo,

I'm not sure if it has something to do with having migrated my 2005 project to 2008 ... or having moved my olap db to a virtual machine ... BUT queries which ran fairly fast are now really really slow. So a few questions:

1. Are there changes in SSAS 2008 that might have a negative effect on MDX queries that worked fine in 2005?

2. Are there particulars I should consider when hosting my SSAS db on a virtual machine that will help increase performance? The whole VM environment runs on a substantial SAN and the operations team managing is open to suggestions.

3. Any recommendations on how to figure out what the bottleneck is? Like I said, it could be the query ... it could be the VM ... I dunno.

Here is one such query as an example (sorry if it sucks as I'm no MDX guru):

SELECT

NON EMPTY {

[Measures].[Average],

[Measures].[Top Bottom Ratio],

[Measures].[Answer Count]

} ON COLUMNS,

NON EMPTY {

[Survey].[Survey Year].[Survey Year].ALLMEMBERS *

[Question Answer].[Question Number].[Question Number].ALLMEMBERS *

[Question Answer].[Question Report Abbr].[Question Report Abbr].ALLMEMBERS *

{[Question Category].[Question Category].&[1]

, [Question Category].[Question Category].&[2]

, [Question Category].[Question Category].&[3]

, [Question Category].[Question Category].&[4]

} *

(filter([Group].[Group Hierarchy].ALLMEMBERS

, [Group].[Group Hierarchy].Properties("Group Full Name") = strtomember(@GroupGroupFullName).MemberValue))

} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM (

SELECT ( STRTOSET(@ResponseStatusResponseStatus, CONSTRAINED) ) ON COLUMNS

FROM (

SELECT ( STRTOSET(@QuestionAnswerAnsLabel, CONSTRAINED) ) ON COLUMNS

FROM (

SELECT ( STRTOSET(@SurveySurveysByYear, CONSTRAINED) ) ON COLUMNS

FROM [SysSurveyDW])))

WHERE (

IIF( STRTOSET(@SurveySurveysByYear, CONSTRAINED).Count = 1,

STRTOSET(@SurveySurveysByYear, CONSTRAINED), [Survey].[Surveys By Year].currentmember ),

IIF( STRTOSET(@QuestionAnswerAnsLabel, CONSTRAINED).Count = 1,

STRTOSET(@QuestionAnswerAnsLabel, CONSTRAINED), [Question Answer].[Ans Label].currentmember ),

IIF( STRTOSET(@ResponseStatusResponseStatus, CONSTRAINED).Count = 1,

STRTOSET(@ResponseStatusResponseStatus, CONSTRAINED), [Response Status].[Response Status].currentmember ),

[Survey Type].[Survey Type Hierarchy].&[9]

)

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Rank MDX

gmoleres — Wed, 03 Mar 2010 02:36:00 GMT

Hi Teo,

I developed a script that calculates the ranking of a measure for
members of a dimension. This dimension has access restrictions
according to certain roles, when I run the script with the user whose
role has access to all members. It's works well.
But when I execute this script with an user whose role has access only such
a dimension member, does not consider the other members to define
ranking therefore always returns the value one (because it is the only member
he considers to rank?).

Do you have any idea to fix this problem when the script is run by a user with restricted access?

Thanks in advance.

Distinct Count Measure Group Error

Chris — Mon, 01 Mar 2010 18:42:50 GMT

I keep getting an error When I try and process my unique count measure group:

Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.

Errors in the OLAP storage engine: An error occurred while processing the 'Distinct UPC Count' partition of the 'Distinct UPC Count' measure group for the 'Operations' cube from the Operations database.

I am trying to get the distinct count of my UPC codes from my Sales Fact table.

I've checked that the collation matches and I do have distinct records in my DW for the UPC and Transaction Date. Can someone please give me some idea of what I should check next?

Thanks

Finding the Current Day, Week, Month and Year.

light_wt — Wed, 17 Feb 2010 16:31:01 GMT

In a Date dimension, I have 2 hierarchies. Year-Week-Date and Year-Quarter-Month-Date.

What is the name set is to find the current week.
what is the name set to find the current Month?

can i use either of the above name query to find the Current Year?

thanks.

Extracting metadata from MSAS 2005

AKirillov — Mon, 08 Feb 2010 15:55:35 GMT

Hi, All.

I have serious problem with subject.

So, I want to extract cube' metadata from MSAS (dimensions, attributes, hierarchies etc.). I've tried XMLA, but it is too slow. Are there any ways to extract metadata with MDX for example, or with something else that can extract this data faster than XMLA (in my case it took about 10 minutes, I've used Discover method).

I've seen MDX' local cube copies methods but they're rather slow too (for tiny subcube of AdventureWorks it took about 30 seconds to create local copy).

Thanks for answers.

Scope MDX

Chris — Wed, 27 Jan 2010 21:56:06 GMT

Hello

I'm hoping someone can clear up my understanding of the Scope statement in the MDX script. The high level overview of my project is to determine the Weeks On Hand (WOH) for every finished goods and raw material used in our manufacturing across all countries in all plants. We use a comparison of actual inventory amounts separately compared to the original budget, revised budget and current budget. So in turn I have about 21 MDX expressions (Calculated Members) for each of the three budgets totaling 63 to ultimately get me the totals I am looking for.

A business requirement now is to only calculate WOH on inventory based on an "active" status, a set of "obsolete codes" and a "demand status". I want to use the Scope statement to encapsulate/ filter my expressions with the correct codes. I wrote a Scope statement without a "this" statement in my MDX script where these expressions start. I wrote an End Scope statement after my expressions.

My understanding is that a sub cube would be created and that my calculated members would calculate against a "pre-filtered" sub cube only containing the measures I wanted. Some measures that don't fit the correct criteria are getting calculated and I am wondering if I don't understand how to use the Scope statement correctly.

Thanks

Chris

SSAS 2005 Chapter 6-Building cube Top Down

sash56 — Wed, 20 Jan 2010 15:55:02 GMT

Hi Teo, The book has been a great read and very clear in its contents. I have one question regarding chapter 6. When you explain about bulding the cube "Top Down" i am kinda confused if it is to do with Inmon menthodology if any. You clarification would be helpfull.

Thank you

Time Dimensions

TobyR — Tue, 19 Jan 2010 00:03:04 GMT

Hi, back again, sorry!

I'm just going over Chapter 4.3 Time Dimensions. What is the recommend way of creating a Time Dimension? Using a Server time Dimension or creating a Table for the Time Data from the wizard?

Last time I did this I created DimDate and then instead of using Date as the primary key I used an sequential integer value as they datekey and the Date as an FullDateAlternateKey. When loading the dimension and fact data I replaced my dates with the Datekey in the DimDate Table making the appropiate joins in the schema and dsv.

Is there any performance benefit doing this or I'm I just as well using the server time dimension and following your example in the book and defining the relationship directly in the cube without worrying about dates?

Regards

Toby.