January 17, 2012 at 7:56 am
Hello,
I wanted to post here to get some advice on where I need to go from here.
We recently adopted SQL Server to use as a data warehouse for all of our transaction data. In the past, I have used Crystal Reports for reporting against our live database, but we moved to SQL with the impression that it would give us far more flexibility and the ability to simply deliver more powerful features to our users. Additionally, we recently migrated to a new ERP Solution (Infor SX.e which runs in a Progress Environment).
So, here is my situation: I need to deliver a lot of reports. I feel like I could mitigate a lot of my workload by providing some AD-HOC tools for some of our high-level users, so that they can create some simple reports on their own. I just don't really know where to start, and was hoping someone would be willing to offer some suggestions.
Our data warehouse is refreshed once per day. The data is copied as-is from our live Progress DB. As such, it is far from palatable for public consumption. I would like to be able to tie all of this highly-normalized data together and apply calculations and logic to the data so that it makes more sense for the end-users. Then, the dream would be that they can report against this data using SSRS or Excel.
Does anyone have any recommendations for this scenario? It seems to me that SQL Server 2008 offers multiple ways to do this very thing, I just don't know what is going to be best for my particular situation.
Thanks in advance for all of your help!
January 17, 2012 at 8:09 am
Sounds like an ideal situation for a Report Model. There are numerous tutorials available if you google it.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 17, 2012 at 8:31 am
In my previous company we used SQL Server Reporting Services (SSRS) on a daily basis. Depending on the size of your data SQL Server Analysis Services (SSAS) may be a choice in addition to SSRS because it will allow you to create and use a cube. You can then reference the cube in SSRS.
SSRS will allow you to run queries on you data and give you a way to display them to users, embed the reports in a web interface, and set up emailed reports in subscriptions. It is great because it has its own built-in web interface on the local network that can be accessed by anyone in your network. If you don't use SSAS (it can be very hard to use), I would just build stored procedures in SQL Server and call those procedures in SSRS to get your dataset and report off of it.
Another option, if your users like raw data and excel, is to create them a connection in excel directly to SQL Server so that they can pull data on their own and manipulate is as they need to. We did a mix of SSRS and Excel connections for our users, depending on who they were and what they needed.
Jared
CE - Microsoft
January 17, 2012 at 2:33 pm
It's a little difficult to offer a solution when we don't know much about the reports that you have to deliver.
I can talk to my experience - we have really locked down ad-hoc usage because of the issues that arise when non-tech users provide results that may or may not be "correct". We have a standard set of reports that satisfy our main business needs so that everyone always gets the same answer. At this point we also have internal power users that use Excel against our SSAS cube and we also provide several flattened datasets in SQL that they can query for non-summarized data. It's taken us several iterations to get here. We had to define our business and report requirements and then build out the data warehouse (dimensions and facts) and how to load it from our ODS. Then the SSAS cube was built to support the busines requirements. We have some report and Excel templates set up for power users.
What type of business requirements do you have? Who do you have on your staff? What technology do you have access to - ie SharePoint? What skill set do you have available to you? That's really going to determine what you provide. Lots of options are out there - report models, Power Pivot, SSAS, etc. From my experience, creating stored procedures to cover every conceivable request is unsupportable and inefficient, unless you have a very narrow set of data and business rules. While a data warehouse/SSAS cube solution takes a lot more work to build out, you get a lot of efficiencies and a lot less errors. If you go that route, I'd recommend SSRS cube browsers options (Angry Koala comes to mind) and use those as a start for an interface for your users.
Good luck!
MWise
January 18, 2012 at 4:33 am
It may seem a slight tangent to what you're after, however, I watched about half of this yesterday:
http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DBI334
At about 20-25mins in he speaks about Report Builder and the types of users who can use it - right down to people who have no idea what tables and fields are etc. He then goes on to discuss 'Report Parts' and the 'Report Gallery'
It seems to me that this could partially help you out perhaps? (I found out about them yesterday around 5pm, so beyond what's in that video that's all I know about them!)
January 18, 2012 at 4:36 am
SQLKnowItAll (1/17/2012)
In my previous company we used SQL Server Reporting Services (SSRS) on a daily basis. Depending on the size of your data SQL Server Analysis Services (SSAS) may be a choice in addition to SSRS because it will allow you to create and use a cube. You can then reference the cube in SSRS.SSRS will allow you to run queries on you data and give you a way to display them to users, embed the reports in a web interface, and set up emailed reports in subscriptions. It is great because it has its own built-in web interface on the local network that can be accessed by anyone in your network. If you don't use SSAS (it can be very hard to use), I would just build stored procedures in SQL Server and call those procedures in SSRS to get your dataset and report off of it.
Another option, if your users like raw data and excel, is to create them a connection in excel directly to SQL Server so that they can pull data on their own and manipulate is as they need to. We did a mix of SSRS and Excel connections for our users, depending on who they were and what they needed.
We used to do this Excel wise, if it's as it used to be then I believe the connection files are unencrypted, so just drop into notepad and you can see the login and password for the server - so create a new account with restricted access at least. (and probably Read Only).
January 18, 2012 at 6:49 am
Thanks for all of these suggestions, you've given me quite a bit to chew on. I think I'm going to investigate using SSAS, because I think it is going to fit the bill best for what we need.
As I said before, the data on our SQL Server is a 1 to 1 replication of the data on our transaction database. It's very complicated and confusing, as you would expect. Do I need to investigate doing anything with SSIS, to clean up the data a bit, as a precursor to using SSAS?
January 18, 2012 at 7:35 am
mbagley (1/18/2012)
Thanks for all of these suggestions, you've given me quite a bit to chew on. I think I'm going to investigate using SSAS, because I think it is going to fit the bill best for what we need.As I said before, the data on our SQL Server is a 1 to 1 replication of the data on our transaction database. It's very complicated and confusing, as you would expect. Do I need to investigate doing anything with SSIS, to clean up the data a bit, as a precursor to using SSAS?
It really just depends on your data. SSAS can be used without any involvement from SSIS. Also, before deciding on SSAS, make sure it is really what you need. I find SSAS to work best with transformation of the original data into a data warehouse and on large aggregates. This operation in and of itself can be taxing on servers and not be as useful to you. I find SSRS without SSAS to be more useful for data that is smaller sets and needs to be cut into smaller reports. For example, looking at a month's worth of contracts or a sales rep's daily or monthly numbers would not be best suited for SSAS. A report of individual transactions would be a waste in SSAS. So, if you can give us some of your most important reports and how they will be used we may be better suited to guide you here.
SSAS is a completely different skillset that will have to be combines with SSRS. Although it is really powerful, it is often a poor choice for companies interested in granular reports. Think of it this way... It would be really cool to have your own plane to get you from point A to point B, but if it is only a 3 hour drive it is much cheaper, requires less gas, and you do not need to learn how to fly a plane. Probably overall better suited for the trip as well.
Jared
CE - Microsoft
January 18, 2012 at 9:01 am
Again, hard to say without knowing your data or report requirements...but yes, typically you transform the data from an operational database into a data warehouse schema. You build out dimensions (like time, product, employee, organization tables) and facts (sales, manufacturing, payroll, whatever your domain is) and the relationships between them. In SSAS you'll do some of the same type of work, and then additionally you build out measures and calculations. SSAS is great for aggregations, we do a lot of this - rollups of data at every level in an organization or across our product lines. We also do a lot of time aggregations - monthly, quarterly and yearly results. This is so much easier to do with SSAS then say, building stored procedures (or one massive sp) that can rollup data at every level in your org for any time period, filtered by whatever the user might select. Building our a data warehouse or een a data mart and associated cube is not an inconsquential task. SSAS and MDX is a whole lot different then SQL, but close enough to drive a SQL developer nuts. And honestly the SSRS to SSAS integration is not a strength in the BI stack. There is a graphical interface which might get you 80% of the way where you need to go, but then for the other 20% you have to write MDX. I've spent hours trying to write an MDX query that I could do in 20 minutes in T-SQL. The pay off is that I can write it once in MDX as opposed to having to write it multiple times in T-SQL to support all sorting/grouping/filtering options or having to use dynamic SQL and deal with poor performance.
In a nutshell, if you aren't aggregating your data, then don't bother with SSAS.
MWise
January 18, 2012 at 9:40 am
Aggregating data is definitely where we want to go, there's no question about that. So with that in mind, I don't have any doubt that SSAS is where I need to be heading. However, I'm thinking that filtering our data through SSIS and into a proper Data Warehouse is going to be the logical first step in this process.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply