How to fix food craving

Do you struggle with a certain type of food which after the first bite you couldn’t stop eating?

I do.

Mine is nuts.

I have been told Nuts are good for us. The problem is once I pop a couple of those crunchy little kernels of savory goodness into my mouth, the bag empties. As a result, within minutes, I consume more than a day’s worth of fat.

So how do you identify trigger foods?

  1. Jot down food items in a list if:
    • You reach for it at least 3 times within an hour regularly
    • You crave it when watching TV
    • You have a tendency to wipe out a whole bag of it in one sitting
  2. For each item, write down:
    1. In what situation or environment you tend to eat it
    2. How you feel when eating it
    3. What it is about the food that keeps you coming back for it (texture, flavor, feeling, etc).

For example, moving back to my bad romance with nuts,  I would typically purchase a bag of them whenever I pass by the snack section of the mall. I would also grab some subconsciously if there happen to be a bag of them in my pantry and when I get bored. I like the crunchy texture and savory flavor.

It then becomes clear on how I should handle them.

  1. Avoid purchasing: I would avoid passing them when I do my shopping
  2. Share with others: I would bring them to the office or a party and share with my co-workers or friends
  3. Find a replacement
    • If boredom is what triggers me, then I would try to take a walk outside, meditate, or read something interesting.
    • If its the texture I like, then I should diversify my options with other snacks. I could choose to eat baby carrots with hummus which provides me the same texture and savory flavor I crave.

 

mixed-nuts-kernels

Simple MDX Queries

— Single axis

select [Measures].[Sales Amount] on Columns

from [InternetSales];

— Double axis

Select [Measures].[Sales Amount] on Columns,

[Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS on rows

from [InternetSales];

–two aggregation wrong no supported

Select [Measures].[Sales Amount] on 0,

[Dim Employee].[Reports To].[Level 02].ALLMEMBERS on 1,

[Ship Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 2

from [InternetSales];

–two aggregation right

Select [Measures].[Sales Amount] on 0,

[Dim Sales Territory].[Sales Territory Country].[Sales Territory Country]*[Ship Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 1

from [InternetSales];

–Filter out emplty

Select [Measures].[Sales Amount] on 0,

NON EMPTY ([Dim Sales Territory].[Sales Territory Country].[Sales Territory Country].ALLMEMBERS) on 1

from [InternetSales];

–“ALLMEMBER”

Select [Measures].[Sales Amount] on 0,

NON EMPTY ([Dim Sales Territory].[Sales Territory Country].[Sales Territory Country].ALLMEMBERS) on 1

from [InternetSales];

Select [Measures].[Sales Amount] on 0,

NON EMPTY ([Dim Sales Territory].[Sales Territory Country].[Sales Territory Country].&[Canada]) on 1

from [InternetSales];

–using tuples

Select NON EMPTY [Measures].[Sales Amount] on Columns,

NON EMPTY [Order Date].[Calendar Year].[Calendar Year] on rows

from [InternetSales];

Select NON EMPTY ( [Measures].[Sales Amount],[Order Date].[Calendar Year].[Calendar Year]) on columns

FROM [InternetSales];

–creating a tuple set

Select NON EMPTY {([Measures].[Sales Amount],[Order Date].[Calendar Year].[Calendar Year].&[2006]),

([Measures].[Sales Amount],[Order Date].[Calendar Year].[Calendar Year].&[2007]),

([Measures].[Sales Amount],[Order Date].[Calendar Year].[Calendar Year].&[2008])

} on columns

FROM [InternetSales];

–Multiple Tuple Sets

Select [Measures].[Sales Amount] on Columns,

NONEMPTY(

([Dim Product].[Model Name].Allmembers, {[Order Date].[Calendar Year].[Calendar Year].&[2007]

,[Order Date].[Calendar Year].[Calendar Year].&[2008]}),[Measures].[Sales Amount]

) on rows

FROM [InternetSales];

–Functions

–TOP Percent

Select [Measures].[Sales Amount] on COLUMNS,

TopPercent([Dim Product].[Model Name].[Model Name],50,[Measures].[Sales Amount]) on ROWS

from [InternetSales];

–Top Sum (running total of category aggregattion exceed a certain amount)

Select [Measures].[Sales Amount] on COLUMNS,

TopSum([Dim Product].[Model Name].[Model Name],150000,[Measures].[Sales Amount]) on ROWS

from [InternetSales];

–use set functions

–Members and Allmembers (to see or not to see calculated members)

Select [Measures].[Sales Amount] on COLUMNS,

[Dim Product].[Model Name].[Model Name].Members on ROWS

from [InternetSales];

–non emplty

Select [Measures].[Sales Amount] on COLUMNS,

Non empty [Dim Product].[Model Name].[Model Name].Members on ROWS

from [InternetSales];

–nonemplty function against tuple

Select [Measures].[Sales Amount] on COLUMNS,

Nonempty( [Dim Product].[Model Name].[Model Name].Members, [Measures].[Sales Amount]

) on ROWS

from [InternetSales];

–Top/Bottom Count

Select [Measures].[Sales Amount] on COLUMNS,

TopCount( [Dim Product].[Model Name].[Model Name].Members, 5,[Measures].[Sales Amount]

) on ROWS

from [InternetSales];

Select [Measures].[Sales Amount] on COLUMNS,

BottomCount([Dim Product].[Model Name].[Model Name].Members, 31,[Measures].[Sales Amount]

) on ROWS

from [InternetSales];

— Specifying Axis

Select [Measures].[Sales Amount] on Columns,

[Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS on rows

from [InternetSales];

–Not supported in SSMS

Select [Measures].[Sales Amount] on 0,

[Measures].[Discount Amount] on 1,

[Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 2

from [InternetSales];

–use tuples instead has to start at 0

Select non empty {[Measures].[Sales Amount],[Measures].[Tax Amt]} on 0,

non empty [Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 1

from [InternetSales];

–use Having clause

Select non empty {[Measures].[Sales Amount],[Measures].[Tax Amt]} on 0,

non empty [Dim Date].[Calendar Year].[Calendar Year].ALLMEMBERS

Having [Measures].[Sales Amount] >=8000000 on 1

from [InternetSales];

–use slicers

Select [Measures].[Sales Amount – Fact Reseller Sales] on 0,

non empty [Order Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 1

from [InternetSales]

where [Dim Sales Territory].[Sales Territory Country].&[United States];

–AND/OR not supported, use Tuples

Select [Measures].[Sales Amount – Fact Reseller Sales] on 0,

non empty [Order Date].[Calendar Year].[Calendar Year].ALLMEMBERS on 1

from [InternetSales]

where [Dim Sales Territory].[Sales Territory Country].&[United States]

AND [Order Date].[Calendar Year].&[2007];

–instead, from different hiarchy

Select [Measures].[Sales Amount] on 0,

non empty [Due Date].[Calendar Year].Members on 1

from [InternetSales]

where ([Dim Sales Territory].[Sales Territory Country].&[United States]

,[Order Date].[Calendar Year].&[2007]);

–instead, from same hiarchy

Select [Measures].[Sales Amount] on 0,

non empty [Due Date].[Calendar Year].Members on 1

from [InternetSales]

where ({[Dim Sales Territory].[Sales Territory Country].&[United States]

,[Dim Sales Territory].[Sales Territory Country].&[Canada]})

–Scope MDS Statement (create sub-cube)

Step 1: reconnect to server use “options”->”Additional Connection Parameters”

and set statement as Cube=”InternetSales”

Step 2: Run Scope Function one step a time

scope ([Measures].[Sales Amount],[Dim Sales Territory].[Sales Territory Country].&[Canada]);

this = [Measures].[Sales Amount]*1.1;

end scope;

–case statements

With Member [Measures].[Sales Targets] as

Case

when [Measures].[Sales Amount]>3000000 then “Achived”

when [Measures].[Sales Amount]<2000000 then “Below Expected”

ELSE “On Track”

End

Select {[Measures].[Sales Amount],[Measures].[Sales Targets]} on Columns,

[Dim Sales Territory].[Sales Territory Country].[Sales Territory Country] on Rows

from [InternetSales]

 

 

 

5 useful SQL Server 2016 features

1. IF EXISTS

When we create or delete or alter any objects in SQL server versions before 2016, we have do use syntax like this

IF OBJECT_ID(‘[dbo].[V_ABC’) IS NOT NULL

BEGIN

DROP VIEW [dbo].[V_ABC];

END;

GO

With SQL server 2016, the syntax is much easier. And this applies to table, view, function, store procedure, etc.

DROP VIEW IF EXISTS [dbo].[V_ABC];

2. Split

I found split function especially useful when you have a column with values with you need to split into different categories. The new function called STRING_SPLIT comes very handy especially work with Cross Apply

Let’s say you have a service requests table, which has a column called REPORTING_SEGMENT and the value storied in each row of the report segment column contains multiple values. The requirement is to get the request counts for each REPORTING_SEGMENT.

SELECT value AS reporting_seg,

COUNT([ID]) AS countofrequests

FROM [Ad_Hoc].[dbo].[Service_Requests]

CROSS APPLY string_split([REPORTING_SEGMENT], ‘,’)

GROUP BY value;

3. Temporal tables

I found temporal tables are super valuable for auditing, SCD, history tracking purposes. Essentially, the SQL server is doing the job in the back-end which usually you will need to configure by yourself using SSIS package or Store Procedures. Also the history table of those temporal table are automatically configured with column store index and is compressed which provide fast read and saved storage space.

4. In-memory tables

With memory price becoming inexpensive compare years ago, now with SQL server 2016 in-memory table capability, we can use in-memory table which much faster for read and write.

5. Column-store indexes

Very glad to see that Microsoft implemented what the technology used in tabular Analysis service to SQL server. Which make query which depend highly on analytics (like aggregation) much faster than if use row-store indexes.

 

 

How to get Microsoft Professional Certifications

MCITP-logoWhy take certification exams?

  • Get overall systematic knowledge of the target profession
  • Get more confident on job as an expert
  • Gain latest updates information
  • Know my job and its relation to my co-workers job
  • Gain job security and flexibility
  • Feel fun and accomplished

Where to Find the information

How to take actions

  • Sign up now! ( you can cancel or change for free until 7 days before the exam)
  • Take 1 – 2 hours blocked time each day to focus on studying
  • Be mentally prepared (it will be hard but no matter what is the result I am learning something)

Resource to use

  • Buy the exam training kit book(if available)
  • Google “exam number + study guide” find the URL for www.mssqltips.com and follow the study guide
  • Cooperate learning site has tons of paid videos ( Anthem: My Learning)

Tips

  • Sign up through retake option (allow to retake the exam once if failed)
  • Use Video Courses online and listen to it when driving or walking
  • Join study groups (optional)

SQL to DAX – FILTER, Customized Measures

Filtering and Customized Measures

SQL Statements

–1. Filter result on female customer shopping behavior
SELECT CalendarYear,
MonthNumberOfYear,
SUM(F.SalesAmount) AS TotalSales
FROM dbo.DimDate AS D
LEFT JOIN dbo.FactInternetSales AS F ON D.DateKey = F.OrderDateKey
LEFT JOIN dbo.DimCustomer AS C ON F.CustomerKey = C.CustomerKey
WHERE C.Gender = ‘F’
GROUP BY CalendarYear,
MonthNumberOfYear
HAVING SUM(F.SalesAmount) IS NOT NULL;

–2 Define calculate – female sales percentage
SELECT CalendarYear,
MonthNumberOfYear,
SUM(F.SalesAmount) AS TotalInternetSales,
SUM(CASE
WHEN c.gender = ‘F’
THEN F.SalesAmount
ELSE 0
END) AS TotalFemaleInternetSales,
CAST(CAST((SUM(CASE
WHEN c.gender = ‘F’
THEN F.SalesAmount
ELSE 0
END)/SUM(F.SalesAmount))*100 AS DECIMAL(18, 2)) AS VARCHAR(5))+’ %’ AS FemaleInternetSalesPerc
FROM dbo.DimDate AS D
LEFT JOIN dbo.FactInternetSales AS F ON D.DateKey = F.OrderDateKey
LEFT JOIN dbo.DimCustomer AS C ON F.CustomerKey = C.CustomerKey
GROUP BY CalendarYear,
MonthNumberOfYear
HAVING SUM(CASE
WHEN c.gender = ‘F’
THEN F.SalesAmount
ELSE 0
END)/SUM(F.SalesAmount) IS NOT NULL;

Corresponding DAX Statements

–1. Filter result based on female customer shopping behavior
–code one
EVALUATE
CALCULATETABLE (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Dimdate, DimDate[CalendarYear], DimDate[MonthNumberOfYear] ),
“TotalSales”, CALCULATE ( SUM ( FactInternetSales[SalesAmount] ) )
),
NOT ( ISBLANK ( [TotalSales] ) )
),
DimCustomer[Gender] = “F”
)
— code one is the same as this one
EVALUATE
CALCULATETABLE (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Dimdate, DimDate[CalendarYear], DimDate[MonthNumberOfYear] ),
“TotalSales”, CALCULATE ( SUM ( FactInternetSales[SalesAmount] ) )
),
NOT ( ISBLANK ( [TotalSales] ) )
),
filter(all(DimCustomer[Gender]),DimCustomer[Gender] = “F”)
)
— differnt behavior than code one
EVALUATE
CALCULATETABLE (
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Dimdate, DimDate[CalendarYear], DimDate[MonthNumberOfYear] ),
“TotalSales”, CALCULATE ( SUM ( FactInternetSales[SalesAmount] ) )
),
NOT ( ISBLANK ( [TotalSales] ) )
),
filter(va(DimCustomer[Gender]),DimCustomer[Gender] = “F”)
)

–2, Define Measure and reuse, female sales percentage
DEFINE
MEASURE FactInternetSales[TotalInternetSales] =
SUM ( FactInternetSales[SalesAmount] )
MEASURE FactInternetSales[TotalFemaleInternetSales] =
CALCULATE ( SUM ( FactInternetSales[SalesAmount] ), DimCustomer[Gender] = “F” )
MEASURE FactInternetSales[FemaleSalesPerc] =
DIVIDE ( [TotalFemaleInternetSales], [TotalInternetSales], 0 )
EVALUATE
FILTER (
ADDCOLUMNS (
SUMMARIZE ( Dimdate, DimDate[CalendarYear], DimDate[MonthNumberOfYear] ),
“TotalInternetSales”, [TotalInternetSales],
“TotalFemaleInternetSales”, [TotalFemaleInternetSales],
“FemaleSalesPercent”, FORMAT ( [FemaleSalesPerc], “Percent” )
),
[FemaleSalesPerc] <> 0
)

The one investment which provides the highest ROI

20150625044625-shutterstock-112158569.jpegYou can invest your money and time on many things like house, stock options, retirement account, etc. But the only one investment which will provide you the overall highest ROI and least amount of risk is yourself.

ROI stands for Returns of Investment. The definition of returns shouldn’t be just money but should be a combination of money, happiness, health, personal satisfaction. Based on this definition, the only one investment which will provide the returns is yourself.

Personally, I have spend quite a lot money and time on attending conferences, getting courses which helps me to grow professionally, gym membership, Yoga studio, Healthy suppliments, home robots, books in different format, facial products which helps my skin looks better (La Mer is my favorite brand), pretty close which helps me feel more confident, and lots of Not Free but very good apps which help my life more efficient… Turns out that all of those investment I spend on myself bring me way more satisfaction on income, health, and myself, which make me more happier in general.

Basically, try invest your money and time on the following items and I guarantee that you won’t regret.

  1. Makes you more valuable professionally
  2. Improves your health
  3. Makes you more confident
  4. Save you time on the unproductive works or things you don’t like, and instead use those time to focus on things you are good at, passions about, etc.

FROM SQL to DAX – SELECT, Grouping, Table Joins

SELECT GROUPING JOINS

  • SIX SQL Queries

–1.Select all columns from one table (not best practice)

SELECT *

FROM dbo.DimDate AS D;

–2.Select top 10 from one table

SELECT TOP 10 *

FROM dbo.DimDate AS D

ORDER BY D.DateKey;

–3.Select two columns in one table

SELECT DISTINCT

CalendarYear,

MonthNumberOfYear

FROM dbo.DimDate AS D;

–4.Select two columns and doing aggreation in the same table

SELECT CalendarYear,

MonthNumberOfYear,

COUNT(WeekNumberOfYear) AS countofWeeks

FROM dbo.DimDate AS D

GROUP BY CalendarYear,

MonthNumberOfYear;

–5.Select columns and aggregation in two tables

SELECT CalendarYear,

MonthNumberOfYear,

SUM(F.SalesAmount) AS TotalSalesAmount

FROM dbo.DimDate AS D

LEFT JOIN dbo.FactInternetSales AS F ON D.DateKey = F.OrderDateKey

GROUP BY CalendarYear,

MonthNumberOfYear;

–6.Select columns and aggregation in two tables and filter out blank value

SELECT CalendarYear,

MonthNumberOfYear,

SUM(F.SalesAmount) AS TotalSalesAmount

FROM dbo.DimDate AS D

LEFT JOIN dbo.FactInternetSales AS F ON D.DateKey = F.OrderDateKey

GROUP BY CalendarYear,

MonthNumberOfYear

HAVING SUM(F.SalesAmount) IS NOT NULL;

  • Corresponding Version Queries

–1.Select all columns from one table

Evaluate

DimDate

–2.Select top 10 all columns from one table

Evaluate

TOPN(10, DimDate,DimDate[DateKey])

–3.Select two(or few) columns from one table

EVALUATE

SUMMARIZE ( Dimdate, DimDate[CalendarYear], DimDate[MonthNumberOfYear] )

–4.Select two columns and doing aggreation in the same table 3ms — you can use rollup syntax inside Summarize — summarize useful only select columns in single table

EVALUATE

SUMMARIZE (

Dimdate,

DimDate[CalendarYear],

DimDate[MonthNumberOfYear],

“countofweeks”, COUNT ( Dimdate[WeekNumberOfYear] )

)

–or which performance better 2ms — also in Excel 2013 SSAS tabular 2016 PowerBI there is a better version SELECTCOLUMNS

EVALUATE

ADDCOLUMNS (

SUMMARIZE (

Dimdate,

DimDate[CalendarYear],

DimDate[MonthNumberOfYear] ),

“countofweeks”, CALCULATE(COUNT ( Dimdate[WeekNumberOfYear] ))

)

–5.Select columns and aggregation in two tables

EVALUATE

ADDCOLUMNS (

SUMMARIZE (

Dimdate,

DimDate[CalendarYear],

DimDate[MonthNumberOfYear] ),

“TotalSales”, CALCULATE(sum(FactInternetSales[SalesAmount] ))

)

–6.Select columns and aggregation in two tables and filter out blank value

EVALUATE

FILTER (

ADDCOLUMNS (

SUMMARIZE (

Dimdate,

DimDate[CalendarYear],

DimDate[MonthNumberOfYear] ),

“TotalSales”, CALCULATE ( SUM ( FactInternetSales[SalesAmount] ) )

),

NOT ( ISBLANK ( [TotalSales] ) )

)

 

When is the best time to start fitness challenge 

I often heard people saying that I want to start to lose weight, I want to start that fitness challenge, I want to have that beach ready body … BUT, let me start later because this is my busies the season in the year! I am not ready yet! I need a perfect plan! I don’t want to take that aggressive route! ….

I get that and I have been there too. But my friend, that is the reason why we never get things done! And that is procrastination!

When is the perfect time to start a finesse chanllenge, a fitness goal, or any goal?

The answer is NOW!

Start it small, do something, or anything!

Let’s say you wanna to run a marathon. Start to run 1 miles today, or do some research about it, or join a running group…

Let’s say you want to control your food consumption. Then start on stoping eating what you are eating right now, Forgot what you have been eating, and from now on eat more clean! Or Ask a friend to help! Or doing google search on this topic. Or download a fitness app! Or join a fitness group!

Instead of thinking to much or finding excuses, how about we start to do something first and even if it is not too perfect but we can always revise it later.

What and when to eat for your workout

shutterstock95837026

Today I made couple of mistakes around nutrition for my workout which made my workout performance level hit the lowest ground and it wasn’t pleasant. So that I want to share some tips around Nutrition and food for workout and fitness. Listed below is a summary of what i have been read, learned and my experience about workout food types and timing.

  1. Pre-workout, if you are doing intense workout like cycling, marathon running. Eat finish your  easy-to digest-meal 2-3 hours before your workout.
  2. In addition to that, 1 hour before you workout consume some small snacks like banana, nuts, pre-workout energy drink.
  3. During workout, prepare plenty of water. If your workout is intense workout or with duration, prepare some snacks like energy gel, energy drink, or chocolate beans. If your workout is not intense or short, you don’t need to consume any calorie in and water is enough
  4. Post-workout, after 30 minutes, consume some nutrition-densed food like protein powder, shakeology, or your own meals with high protein, fiber and vitamins

 

Your friend Annie

 

Blog at WordPress.com.

Up ↑