Michael Franz
asked on
BI Software and Implementation
Hello,
Let me first qualify, I am not an IT guy.
We are looking to implement a reporting tool that will help us better run our business. I have had some say use SSRS, SSAS, QlikView, Domo, Informer, etc...... Some say build a warehouse, or at least stratify the data into another server / additional tables to have easier data to report on.
Our goals is to have a better understanding of the data. The current system has NO report writer. Everything is adhoc and there is no trending which is extremely important in our industry which is insurance.
The database is a Sql2012.
I need a tool or system where I can create, run, schedule, email (.xslx / pdf) results. Create data report, drill reports, BI dashboards. Data must be mobile.
Thoughts??
Let me first qualify, I am not an IT guy.
We are looking to implement a reporting tool that will help us better run our business. I have had some say use SSRS, SSAS, QlikView, Domo, Informer, etc...... Some say build a warehouse, or at least stratify the data into another server / additional tables to have easier data to report on.
Our goals is to have a better understanding of the data. The current system has NO report writer. Everything is adhoc and there is no trending which is extremely important in our industry which is insurance.
The database is a Sql2012.
I need a tool or system where I can create, run, schedule, email (.xslx / pdf) results. Create data report, drill reports, BI dashboards. Data must be mobile.
Thoughts??
Several years ago I got employed in a telecom company as a team head of the reporting team. Initially I thought that they have at least several tools for reporting, but the fact was that no one from the team members never heard about BI, DWH, ETL and all related details. As you said, everything was adhoc by issuing SQL scripts to DB and deliver them in spreadsheet format.
In order to deal with ascending number of requests to deliver information (not data), my first mission was to make a quick share-knowledge to other members about the overall architecture of DWH and BI.
Only condition imposed by the company was "at this stage we don't want to spend a penny" on any tools.
So the challenge begin.
Building a Data-warehouse is not a one day job, not even a month, it is a continues process. We started from small data-marts based on Kimball's methodology. We used as ETL tool Pentaho Data Integration (Kettle, community edition), a very reliable tool. Further on DB level we build up the data stage and then star-schema structure (dimensions, facts, bridge and so on).
The hardest was choosing the BI tool, as the range was pretty large. But at that moment only Microstrategy Reporting Suite delivered a full production software with 100 named-users. It has a BI Server where you can publish your reports, you can schedule them, deliver via email in excel, pdf, html formats. Also they have a BI Mobile Server too (this option we never used).
Nowadays Microstrategy reduced the amount of available named-users in this version to 10, but these are all power users, not just report consumers. The beauty is that they allow anonymous log in on BI Server which suites perfectly us.
So these are my thoughts and path we have chosen. Just wanted to say that I don't promote any tools and software.
Regards, Alex.
In order to deal with ascending number of requests to deliver information (not data), my first mission was to make a quick share-knowledge to other members about the overall architecture of DWH and BI.
Only condition imposed by the company was "at this stage we don't want to spend a penny" on any tools.
So the challenge begin.
Building a Data-warehouse is not a one day job, not even a month, it is a continues process. We started from small data-marts based on Kimball's methodology. We used as ETL tool Pentaho Data Integration (Kettle, community edition), a very reliable tool. Further on DB level we build up the data stage and then star-schema structure (dimensions, facts, bridge and so on).
The hardest was choosing the BI tool, as the range was pretty large. But at that moment only Microstrategy Reporting Suite delivered a full production software with 100 named-users. It has a BI Server where you can publish your reports, you can schedule them, deliver via email in excel, pdf, html formats. Also they have a BI Mobile Server too (this option we never used).
Nowadays Microstrategy reduced the amount of available named-users in this version to 10, but these are all power users, not just report consumers. The beauty is that they allow anonymous log in on BI Server which suites perfectly us.
So these are my thoughts and path we have chosen. Just wanted to say that I don't promote any tools and software.
Regards, Alex.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I was working for R-Tag (www.r-tag.com). Their software supports multiple report types: SSRS, Crystal Reports, Ad-hoc queries and Dynamic dashboards. I believe the software will be a good fit for your needs because it is already used by few big insurance companies ( New Yourk Life, NIAC etc.) and part of the features were developed to satisfy their needs. For example:
- combine multiple reports , even from different types in one pdf or excel file. This allows you to combine your existing reports , or reports written 10 years ago with your reporting tool at that time with current reports written with the current reporting tool. Or in other words if you have a report written in Crystal 8 and you want to combine it with a SSRS report and an ad-hoc report you can do this
- report journal. The system keeps track of the report usage so you can check what is the performance of the reports and optimize them. You can also rerun the reports if necessary. For example if you find a bug in a report you can check who used this report for the period when the bug was active, what were the parameters etc., rerun all wrong reports and redistribute them.
- add reports to document management (DM). The system has internal DM and you can save a copy each time a report was started. This, combined with the report journal will allow you to review the reports. Document management has automatic tag options which means that you can request to see all reports related to a specific parameter value, period, user etc. For example is easy to get all reports for 2013 for customer X
- scheduler The software provides internal scheduler with very easy interface (similar to MS Outlook calendar). You can schedule the slow reports for the night and distribute them by email , save on the disk or in DM
- central management You can set report permissions by roles. This means that when you have a new person in the finance department you will add him/her to the right roles and give immediate access to all necessary reports. Another scenario when a person is promoted or moved to another department - with few clicks you may remove few hundred reports and add another set of reports.
-data driven reports With insurance there are many reports which are run on the fly and you don't know what will be the parameters until you need to run them. For example you may schedule a nightly job to send e-mails to customers with claims, which require action. You can get a list of the customers satisfying your scenario, run one or more reports for each customer and send the exported result by e-mail to the customer default email. Similar feature is available in SQLServer Enterprise.
There are probably more features. The list above is for the features available 2 years ago when I was working there.
- combine multiple reports , even from different types in one pdf or excel file. This allows you to combine your existing reports , or reports written 10 years ago with your reporting tool at that time with current reports written with the current reporting tool. Or in other words if you have a report written in Crystal 8 and you want to combine it with a SSRS report and an ad-hoc report you can do this
- report journal. The system keeps track of the report usage so you can check what is the performance of the reports and optimize them. You can also rerun the reports if necessary. For example if you find a bug in a report you can check who used this report for the period when the bug was active, what were the parameters etc., rerun all wrong reports and redistribute them.
- add reports to document management (DM). The system has internal DM and you can save a copy each time a report was started. This, combined with the report journal will allow you to review the reports. Document management has automatic tag options which means that you can request to see all reports related to a specific parameter value, period, user etc. For example is easy to get all reports for 2013 for customer X
- scheduler The software provides internal scheduler with very easy interface (similar to MS Outlook calendar). You can schedule the slow reports for the night and distribute them by email , save on the disk or in DM
- central management You can set report permissions by roles. This means that when you have a new person in the finance department you will add him/her to the right roles and give immediate access to all necessary reports. Another scenario when a person is promoted or moved to another department - with few clicks you may remove few hundred reports and add another set of reports.
-data driven reports With insurance there are many reports which are run on the fly and you don't know what will be the parameters until you need to run them. For example you may schedule a nightly job to send e-mails to customers with claims, which require action. You can get a list of the customers satisfying your scenario, run one or more reports for each customer and send the exported result by e-mail to the customer default email. Similar feature is available in SQLServer Enterprise.
There are probably more features. The list above is for the features available 2 years ago when I was working there.
I recommend finding a competent Business Intelligence consulting company / super dude in your area, and throw some dollars at them so they can frame up your business requirements, and then determine the best tool.
Until then, it's premature to talk specific software.
I can state that I'm fluent in the Microsoft BI tools, and there's a high probabability that I'll be right, but a common behavior of most of us developers here is that we all have hammers, and everything looks like our kind of nail, if you catch my drift.
Until then, it's premature to talk specific software.
I can state that I'm fluent in the Microsoft BI tools, and there's a high probabability that I'll be right, but a common behavior of most of us developers here is that we all have hammers, and everything looks like our kind of nail, if you catch my drift.
ASKER
thank you
Analysis Services, SSAS, is a whole different ballgame used for aggregating large amounts of data into manageable chunks, making it easy to say "show me revenue from all clients in California broken out by county and age group." It takes data from SQL Server (and many, many other sources, if you need), and collects them into a single "cube" structure. You are unlikely to get a working, usable cube without someone with a solid understanding on your IT team.