BI Example for SQL Server 2005

Hi all,

I'm trying to find an example on BI using SQL Server 2005. Ideally something that would allow me to analyze Customer and Product information like Sales per period, per product line, etc. Something really really standard that the user could query directly from Excel 2003.

I'm sure such simple example exists, I'm looking for the DB schema, cubes, dimensions already setup in SQL 2005. My question is if the are scripts available so my only task would be to import our ERP data into SQL and make sure it gets processed.

I'm not interested in really fancy stuff SQL can deliver, just something that is easy to start with and build on.

I'd appreciate it if you can point me to any direction...

Thanks,

ST

The AdventureWorks DW sample shipping with 2005 is meant for this very purpose.

BI Example for SQL Server 2005

Hi all,

I'm trying to find an example on BI using SQL Server 2005. Ideally something that would allow me to analyze Customer and Product information like Sales per period, per product line, etc. Something really really standard that the user could query directly from Excel 2003.

I'm sure such simple example exists, I'm looking for the DB schema, cubes, dimensions already setup in SQL 2005. My question is if the are scripts available so my only task would be to import our ERP data into SQL and make sure it gets processed.

I'm not interested in really fancy stuff SQL can deliver, just something that is easy to start with and build on.

I'd appreciate it if you can point me to any direction...

Thanks,

ST

The AdventureWorks DW sample shipping with 2005 is meant for this very purpose.

Best way to extract part of a db and deploy it

Hi,
I'm using MSSQL2k and I have some customers in a customer table. I want to write a webapp and offer them the possibility to teach them using the app while having telephone contact with them. They can use the app on my server for some time and add data to it.
After a while when someone wants to buy the app I would like to distribute the app as an Intranet app to their own server and let them have the data they already have wrote into the db on my server.
Because there will be some customers with much data I'd like some guidance in how to plan the database to easily extract their own data and create a new db polutated with their data.
KennethThe easiest way that comes to mind would be to add some sort ofcustomer identifer to the primary database, and when you're ready toexport, run a script that copies the rows with that customer identiferfrom the primary database to a blank model database specific for thatcustomer.
All you'd have to do is write the script one, and them plug in the customer identifer when you're ready to export.

Best way to do this query

I have a Customer table and an Events table. Whenever a customer attends an
event, a row is added to the Events table, which has CustomerID, EventName
and EventDate fields.
I want a query that returns all the customers but only the most recently
attended event. In other words, I want the query to ignore events other than
the most recently attended by each customer.
The (simplified) tables are:
Customer:
ID
Name
Events:
ID
CustomerID
EventName
EventDate
Any suggestions for the best way to do this?
TimTim,
You could use something like this:
(Untested)
Select Customer.ID, Customer.Name, Events.ID, Events.EventName,
Events.EventDate
>From Customer
Join Events on Customer.ID = Events.CustomerID
Where Events.EventDate =
(Select Max(Events2.EventDate) From Events Events2 Where
Events2.CustomerID = Customer.CustomerID)
HTH
Barry|||"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1129053565.927048.54860@.o13g2000cwo.googlegroups.com...

> Select Customer.ID, Customer.Name, Events.ID, Events.EventName,
> Events.EventDate
> Join Events on Customer.ID = Events.CustomerID
> Where Events.EventDate =
> (Select Max(Events2.EventDate) From Events Events2 Where
> Events2.CustomerID = Customer.CustomerID)
Good suggestion. Thanks.
Tim

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?