Back to Basics — What is DDL, DML, DCL & TCL?

I was talking with a database administrator about different categories that SQL Commands fall into — and I thought it would be great to document here. So here you go:

ACRONYM	DESCRIPTION	SQL COMMANDS
DML	Data Manipulation Language: SQL Statements that affect records in a table.	SELECT, INSERT, UPDATE, DELETE
DDL	Data Definition Language: SQL Statements that create/alter a table structure	CREATE, ALTER, DROP
DCL	Data Control Language: SQL Statements that control the level of access that users have on database objects	GRANT, REVOKE
TCL	Transaction Control Language: SQL Statements that help you maintain the integrity of data by allowing control over transactions	COMMIT, ROLLBACK

BONUS (Advance) QUESTION:

Is Truncate SQL command a DDL or DML? Please use comment section!

Author: Paras Doshi

SSAS Multidimensional cube: How to solve duplicate attribute key error when attribute is non key?

Problem:

In SSAS multidimensional cube, while processing a cube, you get an error/warning for a non-key “price” column that says:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘Table Name’, Column: ‘Price’, Value: ’29’. The attribute is ‘Price’.

You see, if it would complain about a key column having duplicate attribute that would have been fine but it’s complaining about duplicate attribute key when an attribute is non key. How do you solve this?

Solution:

mostly this is because that the view/table that you are using in your data source views have blanks/nulls.

so in your source, use ISNULL/COALESCE TSQL function before bringing data in the cube OR if it makes sense for you then you can filter out blank data too.

I hope that helps.

PowerPivot DAX: Moving Average

A quick blog post about sharing PowerPivot DAX formula to calculate moving average. Please use this as building block for your scenario:

Here’s the Data:

DATE	AMOUNT
6/1/2013	50
6/2/2013	40
6/3/2013	30
6/4/2013	20
6/5/2013	10
6/6/2013	10
6/7/2013	20
6/8/2013	30
6/9/2013	40
6/10/2013	50

(usually, the date would in a date table. For the purpose of sharing building block of this formula, I kept it this way to keep it simple)

Formula (Calculated Measure):

[code language=”text”]
Moving Average Last 3 days:=CALCULATE(AVERAGE(TableName[Amt]),DATESINPERIOD(TableName[date],PREVIOUSDAY(TableName[date]),-3,day))
[/code]

Results via a Pivot Table:

Note that the formula is a building block and you’ll have to make changes as per your requirement and data model. Feel free to leave a comment if you need some assistance from my side. And also consider using the PowerPivot forum to reach out to community: MSDN Forum – PowerPivot for Excel

How to create a MDS entity via Excel add-in?

In this Post, we’ll see how I (as an Administrator) created a Master Data Services Entity via MDS add-in for Excel:

1. Created a Model via MDS Web Application. I named it “customer Information”

2. Switch to Excel

3. Open the File that has the Data that you want to load to MDS

4. Switch to Master Data tab in Excel
[Resource: Steps I followed to Install SQL Server 2012 Master Data Services (MDS) on my Demo Machine: And then Installed the Excel Add-in]

5. Connect to MDS server (via Excel add-in)

6. Select Model as CustomerInformation

7. Under Build Model, select Create Entity

8. Configure the values in the “Create Entity” > click OK

9. Switch to MDS web application to see the new entity:

I hope this helps! Your comments are very welcome!

Master Data Services: Analogy to remember what are Models, Entities and Attriutes (parasdoshi.com)
Matching activity in Data Quality Services in action! (parasdoshi.com)
Guest Blog: How we use Fuzzy Lookup add-in in our company to solve data inconsistency problems: (parasdoshi.com)
SQLAuthority News – Download Whitepaper – Cleanse and Match Master Data by Using EIM (sqlauthority.com)
How to clean similar textual data in Excel via Fuzzy lookup add-in? (parasdoshi.com)
Official Resource: http://msdn.microsoft.com/en-us/library/hh479642.aspx (MSDN)
Master Data Services Add-in for Microsoft Excel (MSDN)

Where’s the Formula Bar in Excel 2013?

I was playing with Excel 2013 and wanted to see the Formula Bar for something. Turned out, by default, It was hidden and so I learned how to unhide it. Here are the steps:

1. Go to View Tab

2. Check the “Formula Bar” check-box:

3. And it’s unhidden now!

Conclusion:

In this blog-post, we saw how to unhide the formula bar in Excel 2013.

Intro to Power View for Excel 2013 (blogs.office.com)
Calculated Member and Measures in Excel 2013 (blogs.office.com)

PowerPivot: Quick Tip regarding Copy-Pasting Tables in PowerPivot 2010

I was working on couple of PowerPivot models recently and both models needed a Date Table. So here’s what I did:

1. Imported the filtered version of the Date Table (DateStream) from Azure Data Market. Added Calculated Columns to the table. Let me call this Table1

2. I copied entire Table1 and pasted it to an another PowerPivot model – let’s call that Table2

After this:

And I went to happily create data models! Later, I while testing the model (that had Table2), I realized that it had lost the “formulas” – in other words when I copy-pasted from Table1 to Table2 in PowerPivot – it did not copy-paste the formulas but it just pasted the values.

So, 1. Copy Pasting a Table in a PowerPivot does not copy-paste’s the Formula’s in the columns.

And also it does not keep the “Data connection”. In this case, Table1 was connected to Azure Data source and I could “refresh” data if I chose to. But Table2 was “Static” – In other words, It lost the data connection with the data sources. So I could not “refresh/update” data from the data source if need be.

S0, 2. If you copy Paste a Table in a PowerPivot, the newly copied table does not keep the “Data connection” to data source.

I have also posted this here: http://beyondrelational.com/modules/1/justlearned/tips/17976/power-pivot-copy-paste-a-table-in-a-powerpivot-does-not-copy-pastes-the-formulas.aspx

Crunch more than 1 million rows in Excel 2010 with free addin called Power Pivot! (parasdoshi.com)
New Journal Article Published – Title: “Building an Ideal Tabular Model for Power View reports” (parasdoshi.com)

Examples to help clarify what’s unstructured data and what’s structured?

I have been reading and researching about BigData and BigData on cloud. One of the concept that’s repeated is that “Big Data is about analyzing unstructured data…” and in this blog post, I just want to show few examples that would help you differentiate between Structured data & Unstructured data.

Before we begin, here’s the definition of Unstructured data:

Unstructured Data (or unstructured information) refers to information that either does not have a pre-defined data model and/or does not fit well into relational tables – Wikipedia

Also I just wanted to point that it’s not unstructured because you cannot fit the data into a schema/model but even after fitting it into the model – it would not help. Example. Consider email body as an example of unstructured data. You can create a column “EMAIL BODY”. Now think of questions that are likely to be asked. Do they get answered? if not – then fitting it into model and calling it structured does not make sense, does it? With that, Here are the examples:

1 Word Doc & PDF’s & Text files

Unstructured data

Examples: Books, Articles

2. Audio files

Unstructured data

Example: Call center conversations.

3. email body

Unstructured data

Example: you don’t need an example here!

4. Videos

Unstructured data

Example: Video footage of criminal interrogation

5. A Data Mart / Data Warehouse

Structured Data

6. XML

Semi Structured Data

Couple of Applications for your brain cells:

1. Map disease patterns by analyzing medical records (Text)

2. Tuning customer support by analyzing calls (Audio)

Few Quotes about Unstructured data that I liked:

80 percent of business-relevant information originates in unstructured form – Justin Langseth. URL (Wikipedia Article says that even Merrill Lynch cited this)

BUT some-one else had a nice perspective about this 80%:

but managing it (this 80%) really isn’t a significant problem……………the innovation isn’t in structuring text, it’s in applying models to discover and exploit their inherent structure. Source

My Experience with Unstructured Data (in context of BigData) and Cloud:

I have been playing with MapReduce on Windows Azure (Project Daytona), Elastic Map reduce (Amazon Web Services) and Google’s BigQuery platform. To give you one example. I’ll use the example of Microsoft’s project daytona. Here I uploaded data in unstructured format in form of TEXT. And the goal was to run the “Word Count”. It helps you answer questions like: which word has the highest frequency? or which is the least popular word? and you could tweak the algorithm to consider words with length greater than four (among other constraints) – Now this is what happens when you run the algo: amazing MapReduce framework (App deployed on Windows Azure in this case) does some analysis on unstructured data (TEXT in this case) and it helps you answer the question that you were looking for. So I hope you know how it works.

That’s about it for this post. Do you have an example or application of unstructured data? Please do post it in the comments!

Insight Extractor – Blog

Paras Doshi's Blog on Analytics, Data Science & Business Intelligence.

Data model

Back to Basics — What is DDL, DML, DCL & TCL?

Like this:

SSAS Multidimensional cube: How to solve duplicate attribute key error when attribute is non key?

Like this: