How can i do this in BI?

Question

How can i do this in BI?

DD-390158

Grasshopper

Points: 16
More actions
January 14, 2008 at 12:06 pm

#182061

Hi All,
I am new to BI but I am good in SSIS packages.
My customer requirement is to see the reports in BIRDS daily/ weekly/ and Monthly.
My Present process is I am pulling data from SQL server (SQl server 2005)with the SSIS packages and storing in My staging table (SQl server 2005) as monthly, After that I am developing dimensions based on Monthly Data and then producing cubes Monthly ..This process is working fine ... (This is developed by earlier developer)
Now my customer wants the same Data in the Cube Daily/Weekly/Monthly/..And data is incremental as its Support service's Data.
So he wants see Numbers of service call daily, then Weekly and then Monthly.
Please guide me I am totally new to BI if any example is available on net that will be gr8
Thanks in advance
DD

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

maya potiyevskaya SSCrazy Points: 2091 More actions · Answer 1

Hey,

i am not a "cube person", but BIDS is just tool to show data. in essense, when your tables are done processing, your reports will be refreshed automatically, unless they are cached on reporting server. so, you want to make sure they are not being cached. to do so, you would go into report properties of the reports you just deployed and make sure it's set to 'always run this report with the most current data' .

is it what you wanted?

Loner SSC-Insane Points: 21279 More actions · Answer 2

Do you have a 'Date' dimension table? In that table you should have day, month, year as the drill down sequence, when you retrieve the data, you should retrieve at daily basis then aggregate into monthly and yearly in the fact table. So you can query the data in daily, monthly and yearly from the fact table.

DD-390158 Grasshopper Points: 16 More actions · Answer 3

hi Loner ,

Thanks for your reply,

Yes i have Date dimension table in which i Have records for monthly and in fact table i Pull data Monthly basis ..and then process the Cube for That month.

As per your suggestion i need to modify my "Date" Dimension Table and pull the data in Fact table based on daily and weekly and Monthly data ..and then process the cube is that correct ?

but my problem is from the source i am getting on incremental data not the full length of data so how i can achieve this in the cubes ? to see all daily/weekly/Monthly Data

Please guide me

Loner SSC-Insane Points: 21279 More actions · Answer 4

How do you get the data right now? When you say you get the data incrementally, do you mean the fact table data? Then you have to reprocess the fact table every time you receive the data, it is not ideal but I cannot think of other way.

Niels Naglé Ten Centuries Points: 1058 More actions · Answer 5

This question can't be answered with the information provided. To answer the question correctly i need some more information about the data that is loaded to the DWH.

How is the data matched to a specifc date or month and is the data aggregated to the month or not?

What do you want to have daily/weekly? the reports or the data or the cubes?

Niels Naglé

DD-533246 Valued Member Points: 58 More actions · Answer 6

Hi here is my situation,

Data I am getting is in a flat file from there I am pulling with the help of SSIS package to a staging table.

The data I am getting is only new Data means yesterday's (Service calls) data.

Only few records and records which are modified. Shortly we can say incremental data and modified data.

so adding to the Staging table I am deleting the records which are already in the database and are modified y'day those recording i am adding and also adding new Records. After that I am pulling this data in to fact table with a Key for each day.

Now my questions is in The Cube how can I display this data like daily,weekly,Monthly calls made .

I have a Date Dimension which has a unique key for each day.

My Main concern is to display data in BIRDS with the help of CUBE daily weekly Monthly (New calls, Closed calls, Active calls)

Hope this help you to understand.

Thanks

Niels Naglé Ten Centuries Points: 1058 More actions · Answer 7

Good morning,

Thanks this explains the situation your in pretty clear. (except i must say if you delete records why not just update the changed calls??) but that part works fine so up to the next problem.

While building your cube have you made a special calendar dimension and set the colums to the corresponding properties in SSAS of your dimension with a cal_key (of the day). Because when you have done this you can make a hierarchie within your calendar. From year, quarter,week day or anything you want. Then when processing the cube SSAS knows the hierarchie of the data so you can drop your hierarchie in a report and look at the data for each month week and day.

This should work if you need more information just let me know

Niels Naglé