Click here to Skip to main content
Click here to Skip to main content

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

, 21 Apr 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
How to build Reporting ASP.NET Web application
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”

[caption id="attachment_42" align="aligncenter" width="450" caption="Creating Analysis Services Project"]

Creating Analysis Services Project

[/caption] 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-6

rs-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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ramy Mahrous
Software Developer LINK Development
Egypt Egypt
Business Intelligence Developer at LINK Development which is a wholly subsidiary of LINKdotNET, graduated from Faculty of Computers and Information | Helwan University, Cairo, Egypt | 2003 - 2007, Computer Science as a Major, he has a passion for community, latest knowledge delivering and Q&A forums.

Comments and Discussions

 
GeneralMy vote of 1 Pinmemberdharanivendan18-Mar-10 20:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141030.1 | Last Updated 21 Apr 2009
Article Copyright 2009 by Ramy Mahrous
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid