Tag Info

Hot answers tagged

18

Business Intelligence is often a completely separate sect from Database Administration and Database Development. Business Intelligence, at the highest level, includes three main facets: Reporting Integration Analysis Reporting Reporting is the creation, deployment, and management of reports as well as the added ability for users to customize reporting ...


12

The value depends very much on the individual organisation and its requirements. Depending on the level of sophistication required, a B.I. role might fall into a few different categories: Spreadsheet jock - working from data sets extracted directly from operational systems, this role will produce reports and analysis using desktop tools such as Excel or ...


11

Is B.I. a business or technical project? There are too many variables to answer this categorically; I'm tempted to VTC the question as it doesn't really have a single correct answer. However, on second thoughts I can say a few reasonably meaningful words on the subject. Customer Intimacy Business Intelligence (or more prosaically reporting) is very ...


11

lots of Discussions about ETL vs ELT out there. The main difference between ETL vs ELT is where the Processing happens ETL processing of data happens in the ETL tool (usually record-at-a-time and in memory) ELT processing of data happens in the database engine Data is same and end results of data can be achieved in both methods. it very much depends on ...


9

I have been working with Pentaho for about a year now. Pentaho is a full Open Source suite for Business Intelligence. It's strenght is that it relies on independently managed project : Pentaho Data Integration (Kettle) ->ETL Pentaho Report Designer (PRD) -> Report designer Mondrian -> R-OLAP cube and much more.. You can use them as a whole (Pentaho ...


9

Think about a process or event you might want to analyze. Let's say you were building Lougle Analytics, and want to analyze visits† to your site. A fact table represents the process or event you want to analyze, in this case it is a list of site visits. What things might be useful to you in analyzing site visits? Information about the web browser ...


7

what would a BI person produce that is of value to my organization. I'm going to take a stab at this part of the question as I think others have done a good job of explaining what BI is. I work for a company with many clients and I know a great deal of information about the functions we provide for those clients. Our applications are very ...


7

It's almost a matter of semantics. A lot of hot air gets released in discussions about this but I'm not really convinced that there is any real philosophical depth to a distinction between the two. At some level you can view ETL as transforming data in a client-side tool before finally loading it, with ELT implying that the data is transferred to some sort ...


6

Well...my solution is as follows: I used a dynamic pivot table in a stored procedure. The stored procedure called a View that I created which denormalizes the data. I think used MS Query to hook the stored procedure up to Excel 2010. This loads it up into a nicely formatted table that allows the end user to sort and filter to their heart's content. With ...


6

2 * 10k RAID1 for OS, 6 * 15k RAID10 for everything else. Honestly, with this many disks 1 array is the safest and usually fastest bet. If you've got time to test and have a real world, repeatable, measurable workload then by all means do a test run with your tempdb on the OS drive (caveat: limit tempdb file growth to ensure you don't splat the OS). Flip ...


5

While they are not great examples of best practice, the SQL Server sample databases would be good place to start. They include an OLTP, data warehouse and analysis services databases for a fictional organisation. Studying the differences between them should help you make sense of how OLTP (transaction) and OLAP (analytical/BI) databases differ and why. ...


5

I think you're not understanding what is meant by datawarehouse. It's not a tool. Or an application. Or a database. It doesn't mean "big database". You said, we worked with MS Business Intelligence and MSSQL as Data Warehouse storage. MSSQL wasn't just the storage for the DWH, it is the DWH. A datawarehouse is a database which is specifically ...


5

EAV is a nightmare for BI tools. I've found a few places that build automated processes that generate a "pivoted" view of the EAV table, as an ETL process daily which drops & recreates the table, with columns for each key. However, depending on how your BI tool works, you will still have to manually add the new attributes that are created by the ...


5

Perfectly reasonable proposal. No technical issues from your description of what you're aiming for. I'm wary of commenting on licensing issues and would encourage you to verify you are correctly covered with either Microsoft or your reseller. In principle, the same licensing would apply for this arrangement as would apply if you were also deploying the ...


4

I'd say less as an initial starting point but more as an indispensable resource, there are some great blogs out there: SSIS Junkie/Jamie Thomson SSIS Team Blog


4

BI Development is a big subject to learn and will take a lot of time understand all the concepts, I would suggest this book as place to start your journey of discovery: The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset by Joy Mundy Permalink: http://www.amazon.co.uk/dp/0470640383


4

Kimball Group books are great source of DW knowledge and ETL information in particular. http://www.kimballgroup.com/html/booksMDWTtools.html Follow this link, click Tools and Utilities, scroll to Chapter 7 — Design and Develop the ETL System. Download Sample SSIS packages contains full solutions for Adventure Works.


4

I would recommend reading The Data Warehouse Toolkit by Ralph Kimball. Goes through the theory, with some good examples, and will leave you with a solid understanding of the theory. Then you can look at the tools themselves. If you're looking for something to build your ETL process, Pentaho have something called the "Star Modeller" which lets you model ...


3

A surrogate key is a system assigned unique value to identify an entity occurrence. A natural key is what the business uses to identify an entity occurrence. The source systems, as well as your BI/Data Integration database, can use either type to identify the entity occurrence - such as Jim Brown in your example. In the source system we call what the ...


3

Databases designed with the assumption that they will be entirely resident in main memory can use structures such as T-tree indexes. But the real advantage is, IMDBs are just simpler. They do less (as they don't have to worry about managing a cache, or serializing writes for consistency, or anything to do with ACID-compliant I/O at all) so they execute fewer ...


3

Column store indexes will make an appearance in SQL Server 2012 (aka 'Denali'). Here is a link to a Power Point presentation by Conor Cunningham, Principal Software Architect in the SQL Server Query Processor team covering this new feature.


3

Not my field of expertise but as I understand it the difference in the majority of so-called in-memory OLAP databases (not a term I'm fond of, it's used as marketing pitch more than as a fair comparison of technologies) is column store indexes. Column-Stores vs Row-Stores (How Different Are They Really) is a good intro to the technology if you're familiar ...


3

What is the difference in terms of analysis. Storing the values that apply to the snapshot period would be the natural fit for most kinds of reporting, including "requests on trending the coin-in data for example by month per machine." The risk is that if a snapshot goes missing the total on the machine may not match the totals in the cube. Storing ...


3

sys.dm_exec_requests is only able to display requests that are actually in flight at the precise moment sys.dm_exec_requests is executed. To capture all all requests that go by you need to hook into events, either XEvents or profiler events. Eg. capture RPC:Completed and SQL:BatchCompleted will capture all the queries executed (among other things).


3

In the image below which is an example of a basic Star Schema. The Dimensions are the Dim_Tables. These are generally the values that you want to analyse the data by. So you want to look at a particular product sales, in a particular country, over a particular date range. In the fact_sales table you have just the one Measure which is Units_Sold. Measures ...


2

Thanks to @JackPDouglas for the guidance, I think I may have an answer to my own question. It's a hack but I think it will work: The user is interested only in some types of payments and some types of products. I will attach subquery only for those payment types in the detail view: select case when exists ( select top 1 1 from ...


2

You need to start off with a cross join of detail and payment, because you say "products in any transactions that includes a certain type of payment are considered to be paid with that payment type", so you are effectively asking for parts of a transaction to be counted multiple times if there are multiple payment types used.


2

Absolute numbers for months and quarters allow them to sort correctly when used in a query tool. A 'month of year' value is only useful for sorting within a year, but an absolute month value that is in order will sort a list of months correctly across years. This is very helpful to support (for example) a report over a rolling 12 month period. Keys for ...


2

Epoch is also a synonym for a date or time. Occasionally used in database models as an alternative to the ubiquitous 'timestamp' as a column name, the two are essentially interchangeable. Edit: Looking at your list with fresh eyes (and without getting the book from the cupboard it's hiding in) I expect an epoch in this instance would be a period such as a ...


2

The differences between Cognos and Modeler are very large. Cognos is mainly a summary and reporting tool. It does not do any statistical analysis to speak of (except when you use the part of SPSS Statistics that is optionally integrated into one of the Studios). Modeler is a full data mining tool with many modeling capabilities. Modeler can read and ...



Only top voted, non community-wiki answers of a minimum length are eligible