Showing posts with label dimension. Show all posts
Showing posts with label dimension. Show all posts

Thursday, March 8, 2012

BI Dev Studio - export the grid in Dimension Usage tab?

Is anyone aware of a simple way to export the grid (or similar contents) from the Dimension Usage tab in the cube designer?

I imagine it can be done by parsing the XML definition of the cube, but I'm not much of developer with that sort of thing, and I wondered if there's a simple way or a not-so-simple-but-already-written way to get this. I'm not picky about formatting (spreadsheet, text, whatever -- anything but a bitmap screenshot!).

Does anyone else think this would be a handy self documenting feature?

Thanks,

CJB

Keep an eye on http://www.codeplex.com/bidshelper as that feature should be posted within the next week or two. Basically it's going to spit out a print-friendly version of that dimension usage tab.|||I have an early release of this working on my machine at the moment. It prints out using nested groups rather than the matrix that you see in the report usage tab, but most of the info is in there. If you are interested in having a look at an early version contact me via my blog. Otherwise as Greg says, we should have a release out in the next week or two.|||

Thanks, I will keep an eye on that. I poked around on the codeplex projects that you and Mr. Gosbell are working on. I cringed at how much cooler it all is than anything I've put together.

I'm still climbing a .NET learning curve, which wasn't really necessary for me until recently -- as the use of SQL 2005 has begun to mature a bit, and the ease of integrating assemblies into SQL and SSAS has become so slick now, it's too valuable to ignore.

Saturday, February 25, 2012

Better way to filter dimension members?

I have a dimension hierarchy that has several thousand members (it's a geographical one). We often times want to answer questions for a particular city or zip code in the hierarchy - has anyone found a better filtration mechanism in either BI studio's OLAP browser or Excel 2007? Basically, I'd love to just type "Seattle" or "Seattle, WA" and have it choose the Seattle geographical member (instead of navigating the full hierarchy to select it).

Better design practice in case of having hierarchy containing many members is pretty simple, but is not often simple to implement. Unfortunately there arent that many alternatives.

Anyhow. You need to try and create additional levels in your hierarchy so your users can navigate down to the subset of members. For example Customer dimension should have Customer hierarchy where you get to particular customer navigating through Customer Country, Customer State, Customer City levels.

If you are unable to come up with such nice hierarchy, you should try and build another attribute in the dimension based on exactly the same column but set the DiscretizationMethod property for it, such it becomes a grouping level. You add this additional attribute on top of your original one to create artificial way of navigating. It is bit clugy but without a way of navigating to subset of members you are stuck with displaying nundreds or thousand of members for user and that is not a very good idea.

HTH

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

Friday, February 10, 2012

Best way to calculate Customer Age

Code Snippet

Code Snippet

What is the best way to calculate a customer's age?

I have a customer table which acts as both a dimension and a fact table and a corresponding Activity Fact that shows what kind of activity type a customer associated with.

How do I calculate the following?

1. Customer Current Age based on DOB?

This one has to be calculated on the fly based on today's date.

I am thinking about adding a link from DOB to the time dimension

so DOB is a dimension of the dimension dimCustomer.

Or it can be a dimension of the Fact dimCustomer.

2. Customer Age at the time of Enrollment ?

For this one, I created a new calculated column in the DimCustomer table to show AgeAsOfEnrollmentDate by using

Case ISNULL(DateOfBirth,'1900-01-01')
WHEN '1900-01-01' THEN NULL
ELSE DATEDIFF(Month,DateOfBirth,Enrolldate)/12
End AS AgeAsOfEnrollmentDate

3. Customer Membership Age?

How do I calculate How long has the customer been a member based on the EnrollDate?

So for this, I need something calculated on the fly like

DATEDIFF(Month,Enrolldate, Current Day)/12


Code Snippet

DimCustomer

CustomerID int

AccountNo int

DateOfBirth datetime (example--> 19700531)

EnrollDate datetime (example --> 20060731)

Fact_Activity

ActivityID int

ActivityNo int

ActivityDate datetime (example --> 20070201)

CustomerID int

ActivityTypeID

DimActivityType

ActivityTypeID

ActivityType

ActivityTypeDescription

DimTime (is a role-playing dimension for EnrollDate and ActivityDate)

I am thinking about adding also for DateOfBirth.

This has a hierarchy CALENDAR

Calendar Year

Calendar Quarter

Month Of Year

FullDateKey


Hello Binh,

One way you could calculate the customer's age on the fly based on DOB is to create a new named calculation in the data source view of your cube, inside the customer table (similarly to what you did for point 2). In the expression field, you would need to use a T-SQL expression that returns the age of the customer with the desired accuracy. For instance, something like "datediff(year, [DateOfBirth], getdate())" or “datediff(month, [DateOfBirth], getdate())/12.

Hope this helps,

Artur

|||

Hi Artur,

Yes, I know I can do this for both the Customer Current Age and the Enrollment Age,

but the problem is that these values need to be dynamic and if I put it in the data source view like you suggested,

I would have to reprocess the cube everyday for the age to reflect right?

If I somehow can calculate it in the calculation script, I don't have to reprocess the cube to see the new number right?