using String col from fact tables

[hemantkanchan]

Hi
I have a fact table in which i have a String column now i want to show it in the mdx queries..when i add it as a measure it shows some numerical values in the cube i cannot even add it as a member propeties due to some datwarehouse design constraints..so can anyone out there help me please....it urgent
Thankx in advance
regards
Hemant

[Island1]

I may be able to help ... although it is delivered within a context that is somewhat different than what appears to be your case, I do something like you describe in an article that is set to be published the first Monday in June or thereabaouts ... it's MDX Essentials Article No. 32. The general index is here:

http://www.databasejournal.com/article.php/1459531

The aforementioned article will appear shortly, and be readily available, as I say, on or about 6-6-2005.

Let me know if this helps, or if you have further questions. I hope you obtain relief for what appears to be an urgent need!

Bill

[hemantkanchan]

Hi Bill
Thankx for the reply, I solved it by making a new dimension, the article which you advised doesn't deal with that string col, as it was showing the creation of virtual cube, anyway I have one more strange Problem:

There is one more problem that of hardcoding of dimension hierarchy. We have a report in SQL Reporting services where we want to show differences of two dates(i.e dd-mmm-yyyy) data on a single row for which i am fetching data from the fact tables ...The query which i have used is like this



with member [Measures].[Empname] as '[PersonDirects].currentmember.name' member [Measures].[ManagerName] as '[PersonDirects].currentmember.parent.name' select crossjoin(Descendants([PersonDirects].currentmember,,leaves),Descendants([PersonGroup].[Area].members)) on rows,Union(crossjoin({[Time].[All Time].[FY02].[H2].[Q3].[Jan].[01 Jan 2002]},{[Measures].members}),crossjoin({[Time].[All Time].[FY05].[H2].[Q4].[Apr].[25 Apr 2005]},{[Measures].members})) on columns from useranalysis



Result:

This is fetching me the data on a single row by showing the diffences of those two dates on a single row, but when you check in the Reporting services Fields collection you can see that it has actually hardcoded all the fields with the dimension hierarchy so if i pass a dynamic value to the dates it doesn't display values for it



I have used various ways like putting those datevalue into set

another way i tried is like this



with member [Measures].[Empname] as '[PersonDirects].currentmember.name' member [Measures].[ManagerName] as '[PersonDirects].currentmember.parent.name'

set Dateset as 'crossjoin(Descendants([PersonDirects].currentmember,,leaves),Descendants([PersonGroup].[Area].members))' set compareset as 'crossjoin(Descendants([PersonDirects].currentmember,,leaves),Descendants([PersonGroup].[Area].members))'
select union(compareset,Dateset) on rows,{[Measures].[Total Na Rated]} on columns,{[Time].[All Time].[FY02].[H2].[Q3].[Jan].[01 Jan 2002],[Time].[All Time].[FY05].[H2].[Q4].[Apr].[25 Apr 2005]} on pages from useranalysis



Result:

This is fetching me the data and not hardcoding those field which i want to display like the measures but it is not displaying them in the single row as the previous query does.



I have read your article which shows how to deal with those duplicated measures. That doesn't fit into my scenario as there we are dealing with only two stores here i have to deal with all the areas,subregions,districts and so on so how can I solve that problem...right now I have made sets
by making a crossjoin with those dates area and measure field but it's taking a lot of time so can you suggest some solution..This is also urgent as i have spent several days on this problem but unable to come to some solution
Thankz in advance
regards
Hemant

[Island1]

Thanks for the response.

The link I provided was not to an article, but to an index of articles (interesting that you found yourself at a "virtual cube" article - although I have written articles about virtual cubes, the link was to an index.).

The reason I was sending the link to an index was that, as I stated, an article that touched upon your questions is SOON TO APPEAR there, in hopes that it would assist you. I'm glad you found an answer on your own, but you still might want to see the article I referenced in my original response - when it is, indeed, published.

I am currently writing a "manifesto" that covers something very similar, in lurid detail, to what you have described in your last, but it will not appear before next month. I'm afraid that if we try to evolve your project here, there will be a score of back-and-forth messages to impart the details, which are a bit tricky. I hope you'll find time to check back for it next month (it will be in the Analysis Services series). I'm confident that the "concentrated format" will benefit a large audience efficiently.

Best of luck with your current challenges.

Bill