Building ASP.NET Reporting OLAP-Based Application Part-1


Here I’ll talk about how to integrate your asp.net application with Reporting Service through ReportViewer web control which provides rich functionality to deliver Reporting Service Report through asp.net applications.

In real life applications; Analysis Service and Reporting Service on server machine, Web Server another server machine and clients usually on another domain access the website to do something.

Let’s take a look on this diagram to visualize what happened in intra applications which one of its functionality shows reports this reports actually based on OLAP Data (SSAS used here)

Infrastructure Architecture

Here we build OLAP Cube as Report data source.

So, let’s begin to build our report based on Cube which built from AdventureWorksDW

1- Open Microsoft SQL Server Business Intelligence Studio (BIDS) 2005\2008

2- Select Business Intelligence Projects from Project types then create Analysis Services project, with name “AdventureWorksInternetSaleCube”

Creating Analysis Services Project

Creating Analysis Services Project

3- Press OK 

4- Right click on Data Sources -> New Data Source-> Next; to add connection to AdventureWorksDW. Then from Data Source Wizard press on New which gets Connection Manager dialog to setup your connection to AdventureWorksDW

rs-p1-3

   a. Provider: set the default in our case we need to connect to SQL Server instance

   b. Server name: The name of the server which has AdventureWordDW in our guide I’m using mine “RamyMahrous-Lap”

  c. Log on to the server: You can use Windows\SQL Authentication in our guide I’m using the default setting which is Windows Authentication

  d. Connect or database: Select or enter database name: “AdventureWorksDW”

  e. Press on Test Connection to validate it. It must say: Test connection succeeded.

  f.  Press OK

rs-p1-41

  g. You’ll find a new connection has been added to Data connections.

  h. Press next

  i. You come to Impersonation Information dialog: which is what’s user credential you need to connect by to the Analysis Services (which we will use later to build our Cube on) let’s understand the four choices

          i. Use a specific windows user name and password: if Analysis service on another machine i.e not on RamyMahrous-lap in our example I should connect to it using a user has access to its machine. Because Analysis service DOESN’T SUPPORT ELSE WINDOWS AUTHENTICATION.

          ii. Use the service account: to use the user credentials which the Analysis service starts with (may be you, may be the administrator)

          iii. Use the credentials of the current user: YOU

          iv. Inherit: to use the default user. Read more about these options on http://technet.microsoft.com/en-us/library/ms187597.aspx

rs-p1-5

  j. Use the one fits your development environment, for me I’ll use Use the service account

  k. Next

  l. Set the Data source name to “Adventure Works Data Source”

  m. Finish

  n. You’ll find Adventure Works Data Source.ds added to Data Sources. Adventure Works Data Source.ds in xml file so you can edit it manually but take care if the file being corrupted it’ll affect all the solution

5- Right click on Data Source Views->New Data Source View->Next; to add our relational tables which is Cube data source few things should you know about Data Source View

  a. Every Data Source View associated to single connection as every Data Source View gets data from one location, if you have multiple server which holds data you need to do multi Data Source Views

  b. Use mainly to view data without being have any permission on the database directly; Microsoft helps to calm DBAs down.

6- Use our connection “Adventure Works Data Source”, it’s selected by default; Next; you’ll find all available objects (Tables and Views); include those objects FactInternetSales, DimPromotion, DimProduct; then OK; You’ll find Adventure Works DW.dsv created and a new window opens has your three tables and connected together. You can right click on the table to Explore the data on it.

rs-p1-6rs-p1-7

7- Right click on Cubes->New Cube-> Next->Use Existing tables->Next; Data Source View: Adventure Works DW (selected by default); Measure group tables-> “FactInternetSales” or you can press suggest to let it select the measure.

8- Next; Next; Select Dimensions->Select all

9- Next; Finish.

10- Congratulation we’ve built the Cube which be the data source for our report.

I’ve passed on creating Cube elements definitions quickly like what’s Fact, dimension, Measure, KPI, Partitions, etc…. because it needs a lot of posts to talk about each of which in details. But I assume you have enough knowledge about SSAS, SSRS and SQL Server relational database concept.

Conclusion:

We’ve showed building Cube using BIDS to be used later as Report data source

Stay tuned the next part building the Report.

About these ads

15 Responses to Building ASP.NET Reporting OLAP-Based Application Part-1

  1. […] ASP.NET Reporting OLAP-Based Application Part-2 In the previous post we learned how to build simple cube using BIDS, and if you’re follower I’ve forgot something […]

  2. […] ASP.NET Reporting OLAP-Based Application Part-3 In the previous two posts building the Cube and building the Report, we discovered how BIDS helps in developing BI applications in robust, […]

  3. […] Part 1 Here I’ll talk about how to integrate your asp.net application with Reporting Service through […]

  4. […] filled the CheckBoxList controls with data directly 2- To apply our infrastructure architecture First Part you need to have a user have permission to access Reporting Server Server and modify the above code […]

  5. Have you ever considered adding more videos to your blog posts to keep the readers more entertained? I mean I just read through the entire article of yours and it was quite good but since I’m more of a visual learner,I found that to be more helpful well let me know how it turns out. This is good…thanks for sharing

  6. Ramy Mahrous says:

    Thank you, Damian.
    Due to time, I’ll plan for it later, kindly tell me in what you need me to blog what’s your interested topics in BI or SQL Server in general.

  7. […] Building ASP.NET Reporting OLAP-Based Application Part-1 April 2009 6 comments 3 […]

  8. “Building ASP.NET Reporting OLAP-Based Application Part-1
    [Business Intelligence].[ALL]” honestly got me personally hooked with ur page!
    I personallywill wind up being back even more normally.
    Many thanks -Sidney

  9. Hi mates, its impressive post concerning tutoringand fully defined,
    keep it up all the time.

  10. Thank you, I’ve just been looking for information about this topic for a while and yours is the greatest I have came upon so far. However, what in regards to the bottom line? Are you certain concerning the source?

  11. Polly says:

    Hello! I know this is sort of off-topic however I had to ask.

    Does running a well-established blog like yours take a massive amount work?

    I’m completely new to running a blog however I do write in my diary every day. I’d
    like to start a blog so I can share my own experience and
    views online. Please let me know if you have any recommendations or tips
    for new aspiring blog owners. Appreciate it!

    My favorite blogging site concerning technology:
    Polly

  12. Salvatore says:

    Good post. I certainly love this website. Thanks!

  13. Howdy I am so happy I found your blog page, I really found you by mistake, while I
    was looking on Google for something else, Anyways I am
    here now and would just like to say many thanks
    for a tremendous post and a all round entertaining
    blog (I also love the theme/design), I don’t have time to
    read it all at the minute but I have bookmarked it and also added in your RSS feeds, so when I have
    time I will be back to read more, Please do keep up the fantastic
    jo.

  14. Spot on with this write-up, I absolutely believe that this web site needs far
    more attention. I’ll probably be back again to read more, thanks for the info!

  15. Your style is really unique compared to other people I’ve read stuff from. I appreciate you for posting when you’ve got the opportunity, Guess I’ll just book mark this blog.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: