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


10

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


10

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


9

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


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


6

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


6

The book "The Data Warehouse Toolkit" by Kimball is a must for any fledging BI developer, IMO.


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

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

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'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

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


4

I recommend the Data Warehousing Toolkit by Ralph Kimball. This is a solid book on dimensional modeling and provides many "soup to nuts" case studies.


4

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


4

Even if we generate a precomputed table with all of the columns in it (example: 80 columns, 300K rows), performing analysis on this is slow [...] Since we do not know ahead of time which fields would be queried on the most, we cannot put indexes on specific columns. On the other hand, (correct me) I don't believe it's best to place 80 indexes on a table. ...


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

The Microsoft Channel 9 site is a great place to start.


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

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


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



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