New at BI and data warehousing and trying to make sense of it all.

[theskaz]

Good evening gents! I am new on this site obviously and wanted to say hi before i start with my issue.

now on to the issue. I am starting a data warehousing project from complete scratch for a small company that is getting off the ground. This is my fist endeavor outside of a relational DB. Anyway, we have SQL Server 2008 and im trying to develop an ODS using an ETL to take the data from our production DB to the ODS and then use SSAS to make a cube, then use SharePoint 2010 for the presentation. We have a DB with a "Client" table, an "Activity" Table, and a "Event" table.

Client - All of our cloient information
Activity - Events that need to be acted upon (Tickets)
Event - These are alerts that come in from the clients (We are an IT business)

From what I understand, If I want to make an dimensional DB the dimensions would be Client, Activity, and Time. The Event would be a Fact?

So going off of that i set up a DB in the lab. with these tables
DimTime
FactEvent
DimActivity
DimClient

Ok cool.... Now i got to get the data in there.... I open BIDS and create the world's simplest ETL.... didnt work.
after some googling and revisions (conditioning the data and getting rid of nulls) I got it to work. Problem is that my Event table has 11 million rows in it..... OOM messages everywhere.

Ok with that set, I tried to create a cube with those demensions and it is failing with all types of errors. Ill get to those later but does it seem that I am understanding this correctly?

[BoxesAndLines]

Looks good on the surface. The issue may be your cube size. Loading 11m rows into a cube maybe to much data. What you may want to consider is aggregating the cube data and the drilling to detail to get grain level information. That's one of the points of dw'ing, presenting the data in a meaningful manner, i.e. actionable information. Looking at 11M tickets isn't very meaningful.

[theskaz]

Thanks for the response. I have been reading around some more and noticed that I might be going at some of this the wrong way.

The DimTable, from what I have seen, has all dates in it from a specified start time. What I was doing was adding the timestand from the event and using some tsql to explode that into the other columns in that row. Opinions?

the fact table is really just to count the amount of events in a specified "dimension" whether its customer, time, geographical area, or whatever they want. Is there a better solution than to just add all the rows?

[Sponsored content]